Access compare and sum records in two tables

[piszczel]

Dear All, I’ve the following problem. I need to compare two tables, sum records by description and calculate the difference.



190511 and 200511 are identical in structure but different in data. Both are connected with column RefId in format (100-110 etc)

Table00
RefId|Description|Qty
100-101|Elbow90|1
100-102|Elbow90|1
100-103|Tee1x1|1
100-104|Tee2x2|1
100-105|Tee3x3|1
100-110|Flange|1

Table01
RefId|Description|Qty
100-101|Elbow90|1
100-102|Elbow90|1
100-104|Tee2x2|1
100-106|Flange|1
100-107|Flange|1

I use Access. I can’t use FULL JOIN so I wrote:

SELECT Table00.RefId, Table00.Description, Table00.Qty, Table01.RefId, Table01.Description, Table01.Qty
FROM Table00 LEFT JOIN Table01 ON Table00.RefId = Table01.RefId


UNION SELECT Table00.RefId, Table00.Description, Table00.Qty, Table01.RefId, Table01.Description, Table01.Qty
FROM Table00 RIGHT JOIN Table01 ON Table01.RefId = Table00.RefId;

Now, how to sum elements in Table00 and Table01 by description and put the difference? Is it possible in SQL in this move or I need to sum then records in program.

Thank you in advance.

Mike

[June7]

Not fully understanding what you want. Example: Each table has 2 Elbow90 so the difference is 0. Is that what you are looking for? If so, the RefID is irrelevant and join on that field not needed. Following might be solution:
1. Union on the Description field so that all possible descriptions are available.
2. A Totals query for each table, group on the Description and Sum the Quantity.
3. Join the three queries
Here is example of all as nested query:
SELECT DescriptionQuery.Description, Table00query.SumOfQuantity, Table01query.SumOfQuantity
FROM (SELECT Table01.Description, Sum(Table01.Quantity) AS SumOfQuantity FROM Table01
GROUP BY Table01.Description) As Table01query RIGHT JOIN ((SELECT Table00.Description, Sum(Table00.Quantity) AS SumOfQuantity FROM Table00
GROUP BY Table00.Description) As Table00query RIGHT JOIN DescriptionQuery ON Table00query.Description = DescriptionQuery.Description) ON Table01query.Description = DescriptionQuery.Description;

[piszczel]

Dear June7, thank you for your replay.

Not fully understanding what you want. Example: Each table has 2 Elbow90 so the difference is 0. Is that what you are looking for?

Yes , this is exactly what I need. I have to show the differences in quantity between both tables, even if there is 0.

SELECT DescriptionQuery.Description, Table00query.SumOfQuantity, Table01query.SumOfQuantity
FROM (SELECT Table01.Description, Sum(Table01.Quantity) AS SumOfQuantity FROM Table01
GROUP BY Table01.Description) As Table01query RIGHT JOIN ((SELECT Table00.Description, Sum(Table00.Quantity) AS SumOfQuantity FROM Table00
GROUP BY Table00.Description) As Table00query RIGHT JOIN DescriptionQuery ON Table00query.Description = DescriptionQuery.Description) ON Table01query.Description = DescriptionQuery.Description;

The query is fine, but I don’t get what is DescriptionQuery.Description
Is this another query saved in Access? If yes, could you give me an example please?
Mike

[June7]

Sorry, I meant to nest the DescriptionQuery. Yes, it is item 1 in the to-do list. It is a UNION query of the two tables pulling in only the Description field to produce a source for all possible descriptions. I deduced from your example that each table could have descriptions not in the other. If you have a single master table of these descriptions that would work.

[piszczel]

It's working! Thank you.
But ...

I posted only an example. In the reality the tables are much more complicated. What if each element is identified by columns like description (already done), material and bore? So description can be the same but material or bore can be different.

I appreciate what you already did, but if you know the solution please let me know.

Mike

[June7]

Do you want the summing to be down to the bore level? Can the same material and bore values be with different descriptions? Include Material and Bore in the queries and in the Group By clauses. You will also have to include them in the UNION to create unique identifier. Then the final join will be on all three fields. If you don't want sum down to these fields then they are irrelevant.

Is there anything else should be aware of to analyse this issue?

[piszczel]

Do you want the summing to be down to the bore level?

Yes

Can the same material and bore values be with different descriptions?

Yes

Include Material and Bore in the queries and in the Group By clauses. You will also have to include them in the UNION to create unique identifier.

I did

Then the final join will be on all three fields.

And I stopped here. How to write the correct query?

Is there anything else should be aware of to analyse this issue?

Basically this is it. The only one thing is, there will be 3 Bore columns. Bore1, Bore2, Bore3. Some elements like tees can have 3 bores (Bore1 for main for inlet Bore2 for main outlet and Bore3 for branch outlet).

If you want I can send you the original table for review. If you agree please send me your email address: betrieb@o2.pl

Thank you!

Mike

[June7]

Posting emails in forums is discouraged. Think has something to do with spammers trolling. You might want to edit post and remove it. You can provide file by either attaching to post or uploading to a fileshare site and posting link.

Did you try the edits I suggested on the working queries? It should not be difficult to add the additional fields.

[piszczel]

Did you try the edits I suggested on the working queries? It should not be difficult to add the additional fields.

Sorry, I did it now! The key word was AND :

SELECT DescriptionQuery.Description, DescriptionQuery.Material, DescriptionQuery.DN, Table00query.SumOfQuantity, Table01query.SumOfQuantity
FROM (SELECT Table01.Description, Table01.Material, Table01.DN, Sum(Table01.Qty) AS SumOfQuantity FROM Table01 GROUP BY Table01.Description, Table01.Material, Table01.DN) AS Table01query RIGHT JOIN ((SELECT Table00.Description, Table00.Material, Table00.DN, Sum(Table00.Qty) AS SumOfQuantity FROM Table00 GROUP BY Table00.Description, Table00.Material, Table00.DN) AS Table00query
RIGHT JOIN DescriptionQuery ON (Table00query.DN=DescriptionQuery.DN) AND (Table00query.Material=DescriptionQuery.Material) AND (Table00query.Description=DescriptionQuery.Descrip tion)) ON (Table01query.DN=DescriptionQuery.DN) AND (Table01query.Material=DescriptionQuery.Material) AND (Table01query.Description=DescriptionQuery.Descrip tion);

Thank you for your help.

Mike