Report - Can you sort on a formula

[Lisa Perry]

Hello,

I have a report that is grouped by Exam Firms and then totals the costs associated with that firm in a specific time frame so you would have something like below on the report which is exactly what I need but I need the firm with the highest amount of total costs to show up first (so in the case below it would be BBB Firm) so the sort needs to be on the "Sum of Expenses" and I can't get that to work.... any ideas?

AAA Firm
Sum of Exam Costs: $15,000
Counts number of Exams: 6

BBB Firm
Sum of Exam Costs: $22,0000
Counts number of Exams: 10

Thank you so much for any help.

Lisa

[June7]

You tried to set this sort in the report Grouping & Sorting dialog?

Want to provide project for analysis? Follow instructions at bottom of my post.

[Lisa Perry]

Thank you for the help but I don't see where you put any directions below your post?

Yes, I used the grouping function to group by Firm and then it is calculating the expenses for each firm and it works perfect, I just need it to sort on the sum of expenses (meaning which firm had the most costs) but I can't do that. I tried to move the sort function above the grouping but it does not allow me to select the sum of expenses - I can choose expenses and it works but it lists them individually and not the sum - I need the sum - so then I tried to sort using an expression but not sure how to write it so it works or even if this is possible...

Lisa

[R_Badger]

Junes directions are in their signature, literally underneath their post

To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!
Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

[Lisa Perry]

Have uploaded DB with report - need to sort on high cost client

Hello,

I have a report that is grouped by Exam Firms and then totals the costs associated with that firm in a specific time frame so you would have something like below on the report which is exactly what I need but I need the firm with the highest amount of total costs to show up first (so in the case below it would be BBB Firm) so the sort needs to be on the "Sum of Expenses" and I can't get that to work.... any ideas?

AAA Firm
Sum of Exam Costs: $15,000
Counts number of Exams: 6

BBB Firm
Sum of Exam Costs: $22,0000
Counts number of Exams: 10

Thank you so much for any help.

Lisa

[alansidman]

Lisa;
I changed your query to the following aggregate query so that it totals the company first before bringing the information into your report

SELECT TblFirmExpenses.ClientName, Sum(TblFirmExpenses.AuditCost) AS SumOfAuditCost
FROM TblFirmExpenses
WHERE (((TblFirmExpenses.[Date of Exam]) Between [Enter Start Date] And [Enter End Date]))
GROUP BY TblFirmExpenses.ClientName;

I then changed your sum formulae in your report to reflect the names of the new fields.

I also then changed that you sort first before you group and it produced a report with the highest volumes first and descending order. In order to get it to do this, I had to delete the detailed Audit Costs from your report. I am not sure how to get both, but hopefully others will have a solution for you, but in the mean time this is a start that will give you a summary report.

Alan

edit: Having some thoughts on how to get the details. You may have to create a sub-report based upon another query which would list the details.

[Lisa Perry]

Hi Alan,

Thank you. I will look at this later when I am home, I can't download files to my computer at work...

I truly thank you for helping me...

Lisa

[June7]

Try this SQL for QryFirmExpenses:

SELECT Val(DSum("AuditCost","TblFirmExpenses","[Date of Exam] Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "# AND ClientName='" & [ClientName] & "'")) AS ClientSum, ClientName, AuditID, [Date of Exam], AuditCost, Comments
FROM TblFirmExpenses
WHERE ((([Date of Exam]) Between [Enter Start Date] And [Enter End Date]));

This will give you the sum by client for the date range as a field in the query that can be used for SORT BY or GROUP BY criteria in the report design. Allows detail and summary info. Alternative could be a separate query that does the summation by client but this query would also need the date range parameters. Then join that query to the other query to get the same output as the DSum calc. Using two queries with input parameters could mean twice the popups. I recommend using a form for input of criteria then the queries would refer to the controls on form as inputs.

I think the two queries could be nested which should then not have parameter popups repeat: http://allenbrowne.com/subquery-01.html
Building nested query SQL can be tricky. I usually do it by building the two queries then in the SQL View window paste the SQL of first query into the second.

[Lisa Perry]

Thank you for the code and please forgive me but I have not done too many aggregate queries, where do I put all the code in the query?

Would you show me in my database and then load back up so I can see it or is that too much trouble and again, I appreciate your help and I know once I see it - I will have a better understanding of how to do this....

Lisa

[June7]

Copy the sql from my post and paste it into the SQL View of query designer. Then look at Design View and you will see where everything is. I did not offer an aggregate query syntax. I show DSum, which is a domain aggregate function.