Sorting and Grouping Order

[summer01]

Having a problem with my sort order, which is probably something very simple that I am not seeing. The query works great but the report is a bit of a problem. In the case of Duplicate names that have a Jr. and Sr. following them in the last name field, it is grouping everything under one of the names rather than separately. If I add "active contract #" as a primary sort/group header, the names are spearated correctly but the alpha sort by last name is not occuring. If I make it the 2nd group header and file name first, it agains combines the duplicate names. How can I make this work so it sorts by Last name first and keeps the duplicate names separate? Below is the SQL of my original query that makes up my report.

SELECT Trim([MemberLast]) & ", " & Trim([Member]) AS FileName, qry_ComplianceCurrStatDte.CatName, AutocamMembers.ActiveContractNum, qry_ComplianceCurrStatDte.CatID
FROM AutocamMembers INNER JOIN qry_ComplianceCurrStatDte ON (AutocamMembers.CONTRACT_NUM = qry_ComplianceCurrStatDte.ContractNum) AND (AutocamMembers.MemRelCde = qry_ComplianceCurrStatDte.RelCode)
WHERE (((qry_ComplianceCurrStatDte.LastOfCompliant)="no" Or (qry_ComplianceCurrStatDte.LastOfCompliant)="unknown"))
GROUP BY Trim([MemberLast]) & ", " & Trim([Member]), qry_ComplianceCurrStatDte.CatName, AutocamMembers.ActiveContractNum, qry_ComplianceCurrStatDte.CatID;

 

[dhookom]

You can set your sorting and group expression to:
=[FileName] & [ActiveContractNum]


Duane
Hook'D on Access
MS Access MVP