Sum and Join

[Love Buzz]

Hi All.

Here is what I am trying to accomplish. I am working with a query pulling
from two tables with a Join Property of DepositAccount. My goal is to show
the total number of 'source' and the total $ of 'ItemAmount' from the
'Incoming Return Item Detail - 2009' table and the total number of '# of
Items' and the total $ of the '$ of Deposit' from the 'Deposit Information'
table. Finally to compare the totals from each table (the expressions).
However, the SQL is returning multiple results per account. I want one
result per DepositAccount with the totals for each.

Any idea where I am going astray? Thanks for your help.

SELECT [Incoming Return Item Detail - 2009].DepositAccount, Count([Incoming
Return Item Detail - 2009].Source) AS CountOfSource, Sum([Incoming Return
Item Detail - 2009].ItemAmount) AS SumOfItemAmount, DepositInformation.[# of
items], DepositInformation.[$ of Deposit], [CountOfSource]/[# of items] AS
Expr1, [SumOfItemAmount]/[$ of Deposit] AS Expr2
FROM [Incoming Return Item Detail - 2009] INNER JOIN DepositInformation ON
[Incoming Return Item Detail - 2009].DepositAccount =
DepositInformation.DepositAccount
WHERE ((([Incoming Return Item Detail - 2009].Date) Between [Start Date] And
[End Date]))
GROUP BY [Incoming Return Item Detail - 2009].DepositAccount,
DepositInformation.[# of items], DepositInformation.[$ of Deposit];

 

[Dale Fye]

Buzz,

I think your best bet will be to create two intermediary queries that
generate the Count and Sum values for each of the tables. Then join them (in
a third query) on the DepositAccount fields and do the comparisions.

You could do this in a single query with subqueries, but if you have never
done that before, it would be easier to do it as mentioned above. My guess
is that your DepositInformation table probably has all of your
DepositAccounts, and that the "Incoming" table may or may not have a record
for each DepositAccount, so when you create the third query you should setup
the join to include all values from the "deposit information" query and only
those from the "incoming" query that match.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.