Totaling Query

[F1stman]

Hey all,
Let me try to explain this. I am creating a query to take the sum of
transactions for a certain ID# in one table and subtract them from the
original fund amount. The problem is, some funds have no transactions giving
them a null sum. So when I run my query, I only get results for the funds
that have transactions. How would I set up a query to give me a result for
every fund. Sorry it is remedial. Thanks, Adam

 

[Ken Sheridan]

Adam:

You need to use an outer join so that all rows from the Funds table are
returned regardless of a match in Transactions, and use the Nz function to
return a zero for the Sum of the transaction amounts where there is no
matching row in the Transactions table, e.g.

SELECT Funds.FundID,
FundAmount - NZ(SUM(TransactiaonAmount)) AS Balance
FROM Funds LEFT JOIN Transactions
ON Funds.FundID = Transactions.FundID
GROUP BY Funds.FundID, FundAmount;

Ken Sheridan
Stafford, England

 

[F1stman]

Thanks so much for the help. It looks good. But where do I put this? Thank
you, Adam

 

[F1stman]

Nevermind. Duh! I figured it out. Thanks, Adam

Adam:

You need to use an outer join so that all rows from the Funds table are
returned regardless of a match in Transactions, and use the Nz function to
return a zero for the Sum of the transaction amounts where there is no
matching row in the Transactions table, e.g.

SELECT Funds.FundID,
FundAmount - NZ(SUM(TransactiaonAmount)) AS Balance
FROM Funds LEFT JOIN Transactions
ON Funds.FundID = Transactions.FundID
GROUP BY Funds.FundID, FundAmount;

Ken Sheridan
Stafford, England