Finding Differences between sums of linked tables

[Amir]

Hi!,
I've been working for months about this one and couldn't success..
Hope you can help.

I have 2 tables:

Table1:
Table1ID (-> LookUp to Table1GeneralData table) Text
Table2EquivalentID (-> LookUp toTable2GeneralData table) Text
ProductID
Text
ProductQuantity
Number

Table2:
Table2ID (-> LookUp To Table2GeneralData table) Text
ProductID
Text
ProductQuantity
Number

*Table1GeneralData and Table2General Data tables contain fields like Date.

I want Access to sum up each product in Table1, and each Product in Table 2,
and display in a report the following information about each product that
it's SUMMED quantity in Table1 is difference from it's SUMMED quantity in
the equivalent Table2:
1. The product's summed quantity in Table1.
2. The product's summed quantity in the equivalent Table2.
3. The difference between 1 and 2.
* I want Access to show the result only for products which the differences
between their summed quantity in Table1 and Table2 is > 0 (I want to find
mismatches in documents).
* In addition I want Access to show only records between a range of dates
(It's OK with me to use a condition which is dealing only with one of the
table's date).
* Of course I'm using the NZ function in order to treat missing records as
zeros.

********************************An example for wrong results:
*****************************************
The query would sum up products which are showing once in table1 and twice
in table 2 as if they have double than their real quantity in table 1,
because it produced 2 lines in the result:

SumInTable1 SumInTable2 Difference
100 (TableID=1) 50 (TableID=16 50
100 (TableID=1) 50 (TableID=18) 50
===============================
Summed Difference 100

Where what I meant is:
SumIn Table1 = 100
Sums in Equivalent tables:
Table2 (TableID = 16) 50
Table2 (TableID = 18) 50
Sum of Sums in Equivalent tables: 100
===============================
Difference: 0
((Differences like this should finally not be shown because the final result
is 0)
****************************************************************************
*************************

Thank you very much,
Kind Regards,
Amir.

 

[George Nicholson]

I would think you would need to create a separate Total query for each
table, where the results would allow you to join those queries in a third
one-to-one comparison. I'm a little unclear about which ID#s you are using
for what, so I can't be more specific than that, but I doubt you can do it
in a single query.

The high-level comparison tells you which ID's don't have the same sums. You
could then use subreports (plural, one for each table) to show the low-level
detail for those non matching ID's.

HTH,