Sorting Issue

Question

I have two access 2013 Queries.  The first select query is below.

SELECT ImportedDataFirstPass.LastName, ImportedDataFirstPass.FirstName, ImportedDataFirstPass.UserPrincipalName, ImportedDataFirstPass.DisplayName, ImportedChild.License, ImportedChild.DomainName
FROM ImportedDataFirstPass INNER JOIN ImportedChild ON ImportedDataFirstPass.UserPrincipalName = ImportedChild.UserPrincipalName
WHERE (((ImportedChild.DomainName) Like "rmssurgical.com"))
ORDER BY ImportedDataFirstPass.LastName, ImportedDataFirstPass.FirstName, ImportedChild.License;

It sorts like I expect it to.

The second is a crosstab query which uses the query above as its table.

TRANSFORM Count([5-QueryForRMSSurgical].UserPrincipalName) AS CountOfUserPrincipalName
SELECT [5-QueryForRMSSurgical].DisplayName
FROM [5-QueryForRMSSurgical]
GROUP BY [5-QueryForRMSSurgical].DisplayName
PIVOT [5-QueryForRMSSurgical].License;

For some reason it sorts on the Display name field.  I do not expect that, I expect it to sort on the first queries LastName field.  Any help?

Answer 1

The best strategy is to sort in the final query only.

---

-Tom. Microsoft Access MVP

Answer 2

Your crosstab query groups by DisplayName, and LastName doesn't even participate in the query.  Why do you expect it to sort on LastName?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

I guess because I am stupid.  I thought that if I sorted in the query that supplied the data, it would inherit that sort.  I guess not!

Thanks to Tom and Dirk for setting me straight.  I will need to change the display name since it is now "John Doe"  instead of "Doe, John"  like it should be.