SQL Question

[Gator]

I think I need to group p.PayableAmount for the Totals somehow and then
compute the Field "Ending". There are many records in "p" to 1 record in
"ab". What I have below isn't working. I want to show each Account's ending
balance. Can somebody help?
SELECT
p.PayableAccount AS Account,
ab.AccountBalance AS Begining,
SUM(p.PayableAmount) AS Total,
Begining - Total AS Ending
FROM Payables p
INNER JOIN AccountBalance ab
ON p.PayableAccount = ab.AccountID
GROUP BY p.PayableAccount;
thanks

 

[Michel Walsh]

When you use a GROUP BY query, each expression in the SELECT clause must
either:

- either also appear in the GROUP BY clause
- either be aggregated
- either be a constant
- either be an arithmetic expression from the previous ones


so, try,


SELECT
p.PayableAccount AS Account,
MIN(ab.AccountBalance) AS Begining,
SUM(p.PayableAmount) AS Total,
Begining - Total AS Ending
FROM Payables p
INNER JOIN AccountBalance ab
ON p.PayableAccount = ab.AccountID
GROUP BY p.PayableAccount;



as example, if that makes sense.



Vanderghast, Access MVP