How to calculate the difference between multiple tables

[davey]

Hello there, I am just getting to grips with Access having only previously worked in Excel (VBA). I have 3 tables set up from various sources and I'm trying to create a new table which can calculate the difference between them based on certain fields linking between them. All of the tables have fields for "Factory ID", "Month", "Year" & "Revenue" as well as others, the fields are not identical in each table.


My desired outcome would be to sum the "Revenue" from Tables 1 & 2 and subtract that figure from what is found in Table 3 for that combination of "Factory ID", "Month" & "Year". I'm just at a loss in how to do this really, I know that in Excel I could create a lookup column for Factory ID/Month/Year and use SUMIF, or even use SUMPRODUCT however in Access I don't know where to start (but am willing to learn!).

Thanks in advance for any replies.

[orange]

Tell us more about the 3 tables ---the fields involved etc.
A sample of the data would be helpful.
Also, Access is quite different than Excel (different object models)

[CJ_London]

Sounds like you are trying to apply excel thinking to a database - it doesn't work that way. You haven't provided enough information to suggest a solution, but from your description tables 1 and 2 should be a single table and possibly table 3 as well.

as a guess

1. create 3 group by queries, one for each table, grouping on factoryID, month and year and summing on revenue
2. create a union query based on the 3 tables to combine factoryID, month and year into one set of columns
3. join this unionquery, using left joins to each of the 3 queries on factoryID, month and year and include your sum calculations

[davey]

Thanks for the replies, here is an example of my data -





My desired outcome would be to take the sum of the jam sales from England in January 2018 from table 1 (900+100) and table 2 (50+150) from the figure in table 3 (1000), therefore showing a table entry for England / Jam / 1 / 2018 as -200 (900+100+50+150-1000).

The 3rd table does not show the items which were sold, just the revenue from the factory, month and year.

Hopefully this makes sense, I'm finding it difficult to move away from Excel thinking and methods.

[CJ_London]

your third table does not have a product so how do you decide which product to set the £1000 against?

[davey]

your third table does not have a product so how do you decide which product to set the £1000 against?

For my query result I don't need the Product field, just the difference in the totals. In real life I'm getting data from 3 different systems here - Table1 is from UK sales, Table2 from International sales and then Table3 is our finance system which looks at what was billed out as well as any adjustments. The finance system does not report on what the products are so I just need to get the difference between the sums of the Factory / Mm / Yy from Tables1&2 and Table3. Hopefully that makes sense.

[CJ_London]

so your example is wrong - it should be

therefore showing a table entry for England / Jam / 1 / 2018 as -700 (900+100+500+50+150-1000).

[davey]

so your example is wrong - it should be

therefore showing a table entry for England / Jam / 1 / 2018 as -700 (900+100+500+50+150-1000).

Sorry, I've totally confused my post where I gave examples - you're completely right. My desired outcome would show England / 1 / 2018 / -700 with no mention of the product whatsoever.

I need to work on my posting skills as well as my Access ones!

[CJ_London]

in which case my original post should suffice.

[davey]

in which case my original post should suffice.

This has worked a treat, thanks so much for your help and perseverance.