none
Sorting Issue RRS feed

  • 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?

    Thursday, February 1, 2018 1:58 PM

Answers

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

    -Tom. Microsoft Access MVP

    • Marked as answer by tkosel Thursday, February 1, 2018 2:26 PM
    Thursday, February 1, 2018 2:17 PM
  • 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

    • Marked as answer by tkosel Thursday, February 1, 2018 2:26 PM
    Thursday, February 1, 2018 2:18 PM

All replies

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

    -Tom. Microsoft Access MVP

    • Marked as answer by tkosel Thursday, February 1, 2018 2:26 PM
    Thursday, February 1, 2018 2:17 PM
  • 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

    • Marked as answer by tkosel Thursday, February 1, 2018 2:26 PM
    Thursday, February 1, 2018 2:18 PM
  • 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.

    Thursday, February 1, 2018 2:26 PM