Utilizing a summary query in another query to compare data

[TKTheKid]

Hi all,

In the attached database, I have query queryDifference that compares the quantities of all UPCs in tables tableKWIData and tableWarehouseData and only displays records for those that are not equal. However, if you look at the last five rows in queryDifference, you'll see that the data repeats and shows the difference per each record.

What I really want to have is one line that shows 50 for KWI QUANTITY and 30 for WHSE QUANTITY and 20 for Difference. So I created a new query querySumUPC to summarize the quantities in tableWarehouseData by UPC. I can definitely use this for my comparison, but I'm really confused on how to do it.



In queryDifferenceCOPY I tried to utilize querySumUPC to get what I want, but I'm getting errors. I think I'm way off. Can anyone help out?

Much appreciated!

Tony

[June7]

There are 5 records in tableWarehouseData for article 9326302909. These 5 records must each join to the one record in the Articles union and so then the one 9326302909 record in tableKWIQuantity will also join to each of the 5 records and repeat data. So your idea to use GROUP BY query is appropriate, however, this looses other details of the records. Remove tableWarehouseData from the query and use the querySumUPC in its place. Need to set join link between the Article fields.

[TKTheKid]

Thanks June. That helped!