Sorting on Sum Total in Group Footer

[SamanthaW]

I have what should be a simple problem, however I can't work it out.

I have a report that is grouped by client and the details section lists all invoices purchased for that client. I have supressed the details section of the report as all I need to see is the sum of all the invoice totals for each client in the group footer.

I now want to sort by the group footer sum field, I know that you cannot sort on calcluated fields and you need to add it into the actual query, however i do not know how to produce this field. If I enter SumTot: Sum([InvoiceTotal]) in the query it just shows the same amount as it does in each records [InvoiceTotal] field.

Any suggestions on how to do this would be appreciated.

 

[Trendsetter]

are you trying to:

Calculate the total invoices for each client then
sort the highest of those client totals to the top of the list etc...
So that the list grades the clients with the highest turnovers.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[SamanthaW]

Yes that is exactly what I am trying to do.

 

[dhookom]

You might want to share the SQL view of your totals query. I expect you are including too many fields in your group by.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[SamanthaW]

The SQL code is as follows:

SELECT MYOB_Cards.Name, MYOB_Sales.InvoiceNumber, MYOB_Sales.TotalLines, Sum(MYOB_Sales.TotalLines) AS SumTot
FROM MYOB_Cards INNER JOIN MYOB_Sales ON MYOB_Cards.CardRecordID = MYOB_Sales.CardRecordID
GROUP BY MYOB_Cards.Name, MYOB_Sales.InvoiceNumber, MYOB_Sales.TotalLines
ORDER BY Sum(MYOB_Sales.TotalLines);

I tried taking out some of the fields in the Group By
but whatever I tried resulted in an error.

 

[dhookom]

I don't really understand what the various fields are. You should not both Sum() and Group By TotalLines. You should group by the fields in your parent record and Sum() or Count() the fields from the detail/child records.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]