• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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?
0
anglandp
Asked:
anglandp
  • 3
1 Solution
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now