I am in the process of learning access and need a bit of help getting my head around a total addup
I have two tables
Table 1
Key column is the export license # (text)
allocated spares (currency)
remaing spares (currency)
Table 2
key linked to export license # (text)
spares price (currency)
I want the remaining spares column to be populated with the allocated spares - the spares price for that export license #. There are going to be multiple spares prices for each export license #.
Am I going about it the right way by creating a column for remaining spares? If not then what is a better way of doing it? And HOW do I do it?
The two online access courses have not helped me much so far
Thanks in advance
I'm not entirely sure what you're asking, but for "There are going to be multiple spares prices for each export license"
do something like:
SELECT export_licence, SUM(spares_price) FROM table2 GROUP BY export_license
The results of that can then be joined with the first table and you can subtract or whatever.
SELECT t1.export_license_no , t1.allocated_spares , t1.allocated_spares - SUM(t2.spares_price) AS remaining_spares FROM table1 AS td LEFT JOIN table2 AS t2 ON t1.export_license_no = t2.export_license_no GROUP BY t1.export_license_no , t1.allocated_spares
UPDATE table1 FROM table2 SET remaining_spares = allocated_spares - SUM(t2.spares_price) WHERE table1.export_license_no = table2.export_license_no GROUP BY table2.export_license_no
Thank you Peter, again you have been very helpful, now I have to work out what you have done. I added the # becuase I was lazy (and the original excel sheet was from the US) so I have changed that.
When I re create your query I take it I change the t1 and table1 to the correct table names? Also, what is td?
As for if its learning or real work, its a bit of both really.
I would also like to thank him for helping me with CF and SQL Queries.
From me he will receive 7 lashes with a wet noodle.