Sort a report by group totals

[ejm8]

I have a report that shows sales rate by employee, and I want to be able to sort the report by sales rate (desc).

The query I'm pulling from has multiple records per day per employee, so to get a summary in the report I group by employee, and don't display any detail.

I can't create a summary query because the users need to be able to filter the report by date (filter criteria is passed from a form, via [tt]DoCmd.OpenReport reportName, acPreview, , criteria[/tt]).

The report looks something like this:
[tt]
Employee Customers Sales Sales Rate
Bob 100 10 10.0%
Joe 99 13 13.1%
[/tt]
...and the query looks something like this:
[tt]
date job_num employee customers sales
5/1/04 1111 Bob 52 6
5/1/04 1112 Bob 12 1
5/1/04 1113 Bob 36 3
5/1/04 1113 Joe 99 13
[/tt]

Any idea how I can sorting the report by sales Rate? I've done several searches here over the last few days and I can't find anything that solves the problem.

 

[dhookom]

Why can't you set the criteria in the query to:
Between Forms!frmYourForm!txtStart and Forms!frmYourForm!txtEnd
You can't sort a report based on aggregate values.


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]

 

[ejm8]

Duane -- Thanks for your reply!

In my original post I only mentioned the need to filter by date... There's actually more criteria on the form, including a couple of list boxes I need to extract the selected items from (if a checkbox next to the listbox is selected).

I gave your suggestion a try, and it does work for the date range. However, it doesn't work for some of the other fields when I use [tt]IIf[/tt] and there is nothing to return. For example:

IIf([Forms]![frmYourForm]![someCheckBox]=True, [Forms]![frmYourForm]![someTextBox], "")

...makes the query give no results if [tt]someCheckBox[/tt] is false/not checked. I also tried [tt]Like *[/tt] instead of just [tt]""[/tt], but still no results. Any suggestions on this? ..or any other solutions to solve my original problem?

Thanks!
Ed

 

[dhookom]

You could add column to your query of [YourField] & "". Then set the criteria to:
IIf([Forms]![frmYourForm]![someCheckBox]=True, [Forms]![frmYourForm]![someTextBox], [YourField] & "")


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]

 

[ejm8]

Thanks Duane. This worked for some things, but I still wasn't able to find a way to set criteria for a field in the query that needs to check to see whether it should look at a multiselect box or one or two text fields for a range (based on whether or not a checkbox is checked), for example, I tried using this on a date field:

IIf([Forms]![myForm]![useDateSelectBox]=True, "In(" & [Forms]![myForm]![stringOfSelectedDates] &")",IIf([Forms]![myForm]![dateRangeTo]="",[Forms]![myForm]![dateRangeFrom],"Between " & [Forms]![myForm]![dateRangeFrom] & " And " & [Forms]![myForm]![dateRangeTo]))

So I ended up just using [tt]QueryDef(...).sql[/tt] to redefine the query with the right criteria everytime before the report is ran. Not an ideal solution, but it works. Is there a better way?

Thanks for your help with this!

 

[dhookom]

Redefining the querydef is cool. If you want to use a multi-select list box and the field your comparing to is not in the report's record source, you may have to use this method.

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]