Results 1 to 10 of 10
  1. #1
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82

    Why doesn't this query sort the way I'm asking it to

    In this query, I want the results sorted alphabetically on the text field LPs.Album. But instead I'm getting the list in a seemingly random order. What am I doing wrong?



    Query:
    Code:
    SELECT LPs.LPID, LPs.Album, Artists.OriginalName, LPs.Performer_id, LPs.[KeyArtistRole]
    FROM Artists INNER JOIN LPs ON Artists.ArtistID = LPs.Performer_id
    ORDER BY LPs.Album;
    Results:
    LPID Album OriginalName Performer_id KeyArtistRole
    1 1,2,3 Red Light 1910 Fruitgum Company 3 1
    2 Greatest Hits Abba 4 1
    3 The Lexicon of Love (c) ABC 5 1
    4 Toys in the Attic Aerosmith 6 1
    5 Eye of the Hurricane Alarm; The 8 1
    6 Local Color Allison; Mose 9 1
    7 America America 10 1
    8 Best of; The Andrew Sisters 11 1
    9 Animalism Animals; The - Eric Burdon and 13 1
    10 Greatest Hits Animals; The - Eric Burdon and 13 1

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if this is a query, it SHOULD have sorted on album,

    but if this is a report, the report sort overrides the query sort.
    you must use sorting/grouping in a report.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is your album field by any chance a lookup field (i.e. storing an AlbumID but displaying the album name)? If so, it will be sorting by AlbumID

  4. #4
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    It's not a report, I cut and pasted from the datasheet view of the query itself.

    Quote Originally Posted by ranman256 View Post
    if this is a query, it SHOULD have sorted on album,

    but if this is a report, the report sort overrides the query sort.
    you must use sorting/grouping in a report.

  5. #5
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    No, the album field is a long text field.

    Quote Originally Posted by Ajax View Post
    is your album field by any chance a lookup field (i.e. storing an AlbumID but displaying the album name)? If so, it will be sorting by AlbumID

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Create a query
    Code:
    SELECT LPs.LPID, LPs.Album, LPs.Performer_id, LPs.[KeyArtistRole] FROM LPs ORDER BY LPs.Album;
    Does is return the Album field sorted correctly?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can't sort on long text (memo) type field. Review https://www.tek-tips.com/faqs.cfm?fid=4188

    Suggest changing field to short text and avoid long text as much as possible.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Agree with June - 255 chars should be more than enough for an album name, change to short text

    note: the link provided says they 'thought' this had been solved in A2k - but it wasn't/hasn't. Simple test to try and sort the column to prove it

  9. #9
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    Can't sort on long text (memo) type field. Review https://www.tek-tips.com/faqs.cfm?fid=4188

    Suggest changing field to short text and avoid long text as much as possible.
    I used to remember that too, but surprised myself awhile back when I tried it again.

    The article says:
    Posted: 18 Sep 03 (Edited 15 Aug 05)

    ...
    Caveat : This is written based on Access 2000.
    I just tried sorting with a query on a long text field and it worked alright

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps fixed in 365, but not in 2010 which is what the OP has (and me!)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-05-2017, 11:10 AM
  2. Replies: 1
    Last Post: 04-07-2014, 02:59 AM
  3. Replies: 18
    Last Post: 02-14-2014, 05:06 PM
  4. sort filter, doesn't sort everything
    By deso in forum Queries
    Replies: 4
    Last Post: 01-25-2013, 05:27 AM
  5. Replies: 7
    Last Post: 10-25-2011, 08:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums