Question Sorting a Count in a Report (1 Viewer)

Hello! I hope this hasn't been covered elsewhere, I searched the forums - but ya never know...

Background: I have a database which tracks requests from different departments. The requests are assigned sequential numbers (MOC#) regardless of the department it comes from.

I created a report which groups the requests by department, sorts ascending by MOC# and counts the number of requests per department using =Count(*).

Problem: I would like to sort that count so that the department with the smallest number of requests is at the top of the report and I can't figure this out! :banghead:

I created a second query which groups the departments, shows the count, and sorts it ascending. I joined that query with the original query that contains all of my data. Initially it works fine and shows me exactly what I want, but when I try to use it as my report's record source, I get a "circular reference" error. I'm just starting (attempting) to learn SQL so hopefully it's something simple?

SELECT qryMOCDepts.Dept, qryMOCDepts.DeptCount, [qryOpenMOCs].[MOC Number], [qryOpenMOCs].Status, [Open MOCs].Department, [qryOpenMOCs].[Date Issued], [qryOpenMOCs].Description, [qryOpenMOCs].Owner, [qryOpenMOCs].Comments
FROM (Departments INNER JOIN [qryOpenMOCs] ON Departments.[ID] = [qryOpenMOCs].[Department]) INNER JOIN qryMOCDepts ON Departments.[ID] = qryMOCDepts.[Dept];

Also, if there's a different way to do this, I'm open to suggestions!

Hopefully this makes sense... thank you in advance for any help.

Alecia

I'm not certain on this, but my understanding is that sorting/grouping in a report is controlled within the reporting software. That is, it is not concerned with the order of the incoming data. I think you have to use the various report options to get what you want.

As I said, I'm not positive on this.

Hi jdraw -

Thanks for the response. I'm using Access 2010 and from what I've read you can't sort a count in the report. If you can, I haven't figured that part out yet.

I did see that if you join the two queries that you could then just sort the count field in the report but I can't get my query to work. That's what I'm aiming for, just can't get to it...

has the report been built with the wizard, i know in the wizard you can select the query field that has the count and sort accending. i would stick with the two queries, get your report to appear as you want and then try to get the 2 queries together.

hth

There is a youtube video by Richard Rost for 2010 Report sorting and grouping.
I found it via google
https://www.youtube.com/watch?v=KwX0xovCUwo

Hello - thanks for the advice

shutzy: I did use the wizard, this report has been working well for me for a while, I'm just trying to add a little more value to it. Just gotta get these queries together!

jdraw: To relate my issue to that video... I already have those sorts and groups set up, I would like to now sort those technicians by their "totals", so the technician with the least amount of calls would show up at the top of my report and the technician with the most amount of calls would shop up at the bottom.

Maybe my original explanation was a little confusing.

I figured it out! It was a dumb mistake.

It had to do with the name of the query. I did in fact create a circular reference because I was naming the new query that I had created with one of the queries that I used to create it. Sadly, it made sense to me when I first did it! I renamed it and it works like a charm.

I appreciate you trying to help! THANK YOU!

Glad you have it working.