multi-level group by clause is not allowed in a subquery how to solve?please help.

[markpastoril]

actual image:



I just want to show the total/ sum of my report.
thanks in advance.

[June7]

Post the SQL statement.

What is the expression for Balance?

Why don't you use aggregate function in textbox?

Use the Balance expression in Sum() function.

[markpastoril]

this is the Expression in Balance[AMOUNTDUE])+([MATERIALS/OTHERS])-([AMOUNTPAID])
sorry did'nt include the BALANCE field in screenshot.

using Sum() was the 1st thing that I use, and then the pop up error:multi-level group by clause is not allowed in a subquery

[June7]

You haven't posted the SQL statement. Why are you using a subquery?

=Sum([AMOUNTDUE]+[MATERIALS/OTHERS]-[AMOUNTPAID])

[markpastoril]

I've used the subquery to automatically get the last reading/record from the last reading. So the user only enters the current reading value,and subtract it with the last reading/record then it calculates the consumption within a month.

[June7]

Well, can't help if you won't provide the SQL statement. Some sample data would be helpful also. If you want to provide db for analysis, follow instructions at bottom of my post.

[markpastoril]

This the sql

SELECT Transactions.Month, Transactions.CustomersID, Transactions.DATEBILLED, (SELECT METERREADING FROM Transactions AS Alias WHERE DATEBILLED = (SELECT Max(DATEBILLED) FROM Transactions AS Alias2 WHERE Alias2.DATEBILLED < Transactions.DATEBILLED AND Alias2.CustomersID = Transactions.CustomersID) AND Alias.CustomersID = Transactions.CustomersID) AS PrevEnd, Transactions.METERREADING, [METERREADING]-[PrevEnd] AS Consumption, Transactions.[MATERIALS/OTHERS], Transactions.[AMOUNT PAID], Transactions.[DATE PAID], Transactions.[OR #]
FROM Transactions;

[June7]

You need meter reading from another record? This is exactly the example used in tutorial by Allen Browne. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[markpastoril]

I've already solve the problem, I've created another query w/c contains only the customersID and the previous reading and then connected the 2 queries to create another one so the subquery from previous reading data becomes a read only and that way it allows me to group and sum.
by the way thanks for the help. JUNE7