multi level group by clause is not allowed ms access

[junne]

I have a query as follows in MS Access

SELECT ledger.ID, ledger.[Expense:(type)], 
    ledger.[Fund allotment], ledger.Amount, 
    (Select Amount From ledger 
        Where ledger.ID = (Select Min(ID) From Ledger)) - 
        Nz((Select Sum(T.Amount) From ledger As T 
            Where T.ID > (Select Min(ID) From Ledger) And 
            T.ID <= ledger.ID),0) AS Balance 
FROM ledger;

I've put this into a report and now when trying to view the report it displays an alert "Multi-level GROUP BY clause is not allowed in subquery"

What I dont get is I dont even have any GROUP BY clauses in the query so why is it returning this error?

[twinnyfo]

junne,

Have you tried running the query on its own, apart from the Report? Does it produce your expected/desired results? This would be the first step in trouble shooting.

Hope this hepps.

[junne]

ah i see it works thanks a lot :)

[NeoPa]

Using any of the Aggregate functions (Min(), Max(), Avg(), etc.) in SQL automatically causes it to be treated as a GROUP BY query. Essentially you're GROUPing the dataset as a whole.