MS Access - Help with Result Sorting

Hi all - I'm having some trouble getting a report to sort data the way I need it to in Access.

I have a database:

table PersonRole
PRID
PersonNameID
RoleID
CaseID
FirmID

table PersonName
PersonNameID
PersonName

table FirmName
FirmID
FirmName

table Role
RoleID
RoleType

table Case
CaseID
Case Name
Court

table Court
CourtID
CourtName

I've generated a query to get back the cases of all people who have a certain role.

SELECT tbl_PersonName.PersonName, tbl_Role.RoleType, tbl_CASE.CaseName
FROM tbl_Role INNER JOIN (tbl_PersonName INNER JOIN (tbl_CASE INNER JOIN tbl_PersonRole ON tbl_CASE.CaseID=tbl_PersonRole.CaseID) ON tbl_PersonName.PersonNameID=tbl_PersonRole.PersonNameID) ON tbl_Role.RoleID=tbl_PersonRole.RoleID
WHERE (((tbl_Role.RoleType)="Realtor"));

When I generate a report, I'm getting the list alphabetically, by person name. What I need is the list sorted by people with the most cases at the top. Any thoughts?
anglandpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I would use a sub query to return a field that is case count for each person. You can then sort the report by this field.

You could also create another query that groups on PersonNameID and counts the cases. Add this query to your original query and join on the PersonNameID. Add the field for case count. Now you can sort by the case count
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anglandpAuthor Commented:
Thanks TheHiTechCoach. Could you provide an example, instruction, or some code?
0
anglandpAuthor Commented:
I've created my count for each person query:


SELECT tbl_PersonName.PersonName, tbl_Role.RoleType, Count(tbl_CASE.CaseName) AS CountOfCaseName
FROM tbl_Role INNER JOIN (tbl_PersonName INNER JOIN (tbl_CASE INNER JOIN tbl_PersonRole ON tbl_CASE.CaseID=tbl_PersonRole.CaseID) ON tbl_PersonName.PersonNameID=tbl_PersonRole.PersonNameID) ON tbl_Role.RoleID=tbl_PersonRole.RoleID
GROUP BY tbl_PersonName.PersonName, tbl_Role.RoleType
HAVING (((tbl_Role.RoleType)="Realtor"));
0
anglandpAuthor Commented:
Thank you very much. I ended up doing the following:

SELECT DISTINCT tbl_PersonName.PersonName, tbl_Role.RoleType, tbl_CASE.CaseName, qryRelatorRank.CountOfCaseName
FROM qryRelatorRank INNER JOIN (tbl_Role INNER JOIN (tbl_PersonName INNER JOIN (tbl_CASE INNER JOIN tbl_PersonRole ON tbl_CASE.CaseID=tbl_PersonRole.CaseID) ON tbl_PersonName.PersonNameID=tbl_PersonRole.PersonNameID) ON tbl_Role.RoleID=tbl_PersonRole.RoleID) ON qryRelatorRank.PersonName=tbl_PersonName.PersonName
WHERE (((tbl_Role.RoleType)="Realtor"));
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.