Sorting a report by the count of certain records

[irethedo]

I have created a report that displays A part number and other fields of data.

I would like to sort the report by the number of occurances of a part number and then sort by the part number so that the report shows that part number in order of the highest occurance of that part number.

I noticed that in the sorting and grouping window for my report I can only select on a field contained within my query but is it possible to sort on count([part number])?

This chokes when i try it with the following error message: "Extra ) in query expression '[count([part Number])]".

How can I set this up to sort by part number showing the highest occurances first?

Thanks!

 

[lupins46]

I think you will have to sort(!) this out in your report's recordsource.
You can create a totals query which gives you the count of each part number, and then join this query on part number to your current source. Used this joined query as your report's recordsource and you can sort the report on the count value in the query.

 

[irethedo]

Thanks for the info...

I tried this by creating a parts totals query and joined this query with my main query and it does give me a count(oparts) and it works to a point but then it only displays the one part number with the most occurances and does not display any others...

What am I doing wrong?

 

[dhookom]

Show us your SQL. I expect there are two queries...


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]