calculate balance query

[eugz]

Hi All.
I have Request, Device and Receive table. All table has DeviceID. I create query to calculate total amount of each type of devices which was requested by link Request and Device tables. And query to calculate total amount of each type of devices which was received by link Receive and Device tables. I would like to create Balance query to calculate:
Total_Receive - Total_Request = Balance
If devices in Request and Receive table is the same result is correct. But if in Receive table add new type of devices I cannot see amount of new device in Balance query. How to fix that problem?
Thanks.

 

[Bilbo_Baggins_Esq]

Hmmm, I think a language barrir is making your inquiry a little unclear, but let me see if I can work this out.

You seem to describe a scenario where ALL DEVICES are included in the DEVICES table (each with its own DeviceID), AND records in the Request or Receive tables may have DeviceIDs not present in the other table.

If you're going to try this in one query, you'll need to add all three tables, each with JOINS to the DeviceID column in the Devices Table, but they each need to be LEFT JOINS such that ALL Records from the Devices table will be included, and only those from each of the other two tables where they match. HOWEVER, if my memory is clear, I think that may result in an AMBIGIOUS SQL Error.

You may need to do this in three queries. One for each total, and one to get your balance.