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.

Sorry if the it was confusing. I am not in my comfort zone with this.

I first create a record with a unique export license # and then set a spares price limit of x dollars. Then once spares have been requested you add a new record in the second table against the export license # with a cost of x dollars per part. I want to show in a column in table 1 the amount of allocated spares left after taking away the spare part cost.

so export license # t100 has $40,000 spares, they order a part for $10,000, they now have $30,000

But this will happen multiple times per export license #

Is that easier to understand?

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. :@