Query: Everything Works Except the Sum

[Robert T]

My job is sending me on a number of trips this summer so I created a simple
flat file table to track my expenses such as Airfare, Hotel, Train, etc. I'm
going to all of our regional offices and each one has a number such as 01,
02, 03, etc.

I designed a query and every cacluation works except the sum. The Average,
Maximum, Minimum, etc. per group is dead accurate.However, the Sum of Airfare
and Hotel expenditures for each office is about 10 times larger than it
should be. Hopefully someone can explain what I did wrong.Thanks in advance.

Here's the query.

SELECT tblExpenses.Office, tblExpenses.Type_Expense,
Sum(tblExpenses.Amount_Expense) AS SumOfAmount_Expense
FROM tblExpenses, LtblRegionalOffices
GROUP BY tblExpenses.Office, tblExpenses.Type_Expense;

Robert

 

[KARL DEWEY]

Remove LtblRegionalOffices from the query or use it joined to the other table.

 

[Robert T]

Karl:

I included the LtblRegionlOffice in the query so I could display the actual
name of the Regional Office in the query results, as opposed to the IDNo. Per
your advice, I joined the lookup table to tblExpenses. It worked, but can you
explain why creating the join resulted in the accuate sum of each type of
expense?

Thanks,
Robert

 

[KARL DEWEY]

You have a Cartesian join so that for each expense is duplicated for every
combination of regional office. When properly joined you have only the
correct combinations.

 

[Robert T]

Karl:

Thanks for the explanation, however, here's someting I think is interesting.

When I designed the query myself, the sum total of each expense was way off
as mentioned earlier. But when I designed the same exact query using the
Query Wizard, the sum total was right on the money. At first I couldn't
figure out why because both queries were identical in design mode.

I compared both queries in Design Mode and they were identical. However,
when I compared my query with the one created by the Wizard, there was one
key difference. The Query Wizard added the DistinctRow keyword to the
query.But if I didn't examine my query and the query wizard in SQL view, I
would have never known what was different about the two queries. How did the
wizard know we needed the DistinctRow function?

Thanks so much,

Robert