Um. I've written all the stuff below... but I'm not sure if I'm actually being helpful here.
:S Don't feel like I'm explaining properly... but it might not matter if you just want it working vs whether you want to know what's happening?
I'm going to post this anyway, and then you can let me know if it makes any sense, or if I (or rather, someone with a functioning brain) needs to explain it better?
Do you actually have a column called "export license #", or are you just typing that because you're being too lazy to write "num" or "no" ?
Because ideally you should avoid putting non-alphanumeric characters in column names (sooner or later it will cause problems), and (along with using underscore instead of space) it means you do't have to do the annoying [brackets] [around every] [column] syntax, which gets irritating. (Of course, if the names already exist and can't be changed, you're stuck with that.)
Oh, and I'm also assuming you've got a date/time of some description in the second table - if not, you very likely should have. (Anything involving money often means worrying about when it occurred, for accounting/balancing purposes.)
Hmm, re-reading the original message... are you're doing this as an exercise for learning Access, or do you have a real world problem to solve?
There's (at least) two ways to do it, both involving SUM/GROUP BY but the ideal approach depends on exactly what you're after.
Hmm, and assumes my memory of what Access does/doesn't support is not wrong.
:S
Anyway, here's the usual way, where remaining_spares is calculated when needed:
SQL code:
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
And here's another version, where you have a permanent column that gets changed (this would occur immediately after the INSERT - in real databases you can set an on insert trigger, but I don't think Access has them)
SQL code:
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
Oh... both of these are SQL queries... to be able to enter them you need to go into the Query selection, select new query, when it puts you in design view, you can right-click and select "view SQL" or similar, and then you can plonk the SQL in, save it, and then when you view the query - at least for the first one - it should give the appropriate result.
Um. I'm not sure if I'm actually being helpful here.
:S Don't feel like I'm explaining properly... but it might not matter if you just want it working vs whether you want to know what's happening?
I'm going to post this anyway, and then you can let me know if it makes any sense, or if I (or rather, someone with a functioning brain) needs to explain it better?