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:

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)

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?

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.

td is me mistyping t1 :'(

For the real table names, you can just change the table1/table2 and leave t1/t2 as is, or you can switch the t1/t2 to use the full table names, or you can come up with your own alias
(I generally use the first one/two characters of each word for aliases - less typing but still understandable within the context).


I tried to find a good explanation of what the "LEFT JOIN" and "SUM/GROUP BY" bits are doing, but couldn't find anything that looked useful.

A left join is basically lining up all the rows in a pair of tables, but unless a regular "inner" join, a left join says "if I've got a row on the left table, but not on the right table, still include it". (any rows in the right table but not on the left are excluded from a left join). In this case it means anything without any spares price is still listed - with a regular join it would not be.

The sum function is an aggregate function, meaning it applies to multiple rows - (it adds the numeric values of a bunch of rows) - which you identify by grouping together rows using the group by command at the end.

Does that make sense?

It's starting to make more sense, thank you for the explanation. Let's see how it works out tomorrow.

I wish to thank Peter for his help over the last couple of week on this access DB, he is a gentleman and will receive all the biscuits and tea he asks for from now on!

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.

Why seven? :@

Good question, and I have no idea. It's just what number my fingers decided on and typed!

I will not accept seven. Six or eight, fine, but not seven. :@