How to resolve error 3122 1

[R00K]

I am getting this error:

You tried to execute a query that does not include the specified expression "BalanceDate" as part of an aggregate function. (Error 3122)

On this code:

SELECT RunningBalance.BalanceDate, Sum(Nz(Transactions!Deposit,0)-Nz(Transactions!Withdrawal,0)) AS DepWith
FROM RunningBalance LEFT JOIN Transactions ON RunningBalance.BalanceDate = Transactions.TransDate
ORDER BY RunningBalance.BalanceDate;

I looked around and found that adding an "Order By" expression usually rolved the error. I already have it. Removing "Sum" resolves the error, but I have 2 depostis on one day that I want summed so that each date appears only once.

Thank you.

 

[jebry]

Hi!

Instead of, or along with, Order By, use Group By:

SELECT RunningBalance.BalanceDate, Sum(Nz(Transactions!Deposit,0)-Nz(Transactions!Withdrawal,0)) AS DepWith
FROM RunningBalance LEFT JOIN Transactions ON RunningBalance.BalanceDate = Transactions.TransDate
Group By RunningBalance.BalanceDate;
ORDER BY RunningBalance.BalanceDate;


hth


Jeff Bridgham
bridgham@purdue.edu

 

[R00K]

Bingo!

Thanks!