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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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
You haven't posted the SQL statement. Why are you using a subquery?
=Sum([AMOUNTDUE]+[MATERIALS/OTHERS]-[AMOUNTPAID])
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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;
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
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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