Using SUM in summry Query

  • Thread starter Thread starter Bardia
  • Start date Start date
B

Bardia

I am trying to create a simple Summery Query with only two currency fields
each from two different tables. I am using Sum Function to calculate the
total of each field. When I add one field from first table I get correct
total but, when I add the second field from another table I get crazy totals.
Please help
 
How are these tables related? Is there a line joining the 2 of them in the
upper pane of your query design window.

If there is no line, you are matching every record to every record of the
other table. So if Table1 has 1000 records, and Table2 has 50 records, this
give you 50,000 records. When you sum them, the total will be severely
inflated.

If there is a one-to-many relation between Table1 and Table2, and a record
from Table1 has 5 records in Table2, that row from Table1 will appear 5
times in the query output. So, if you sum the field from Table1, that one
value will be included 5 times -- again giving an inflated total.

Similarly, any rows in Table1 that have no match in Table2 don't appear at
all in the resultant query when you use the default join. More on that here:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html
 
Back
Top