Results 1 to 7 of 7
  1. #1
    MichaelSB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    12

    ORDER BY in query

    So I am at my last hurdle with what I am trying to do I believe. My query looks like the following.

    Code:
    SELECT tbl_StaffList.STAFFID, tbl_StaffList.Last_Name, tbl_StaffList.First_Name, tbl_StaffList.EOD, tbl_StaffList.Department, qry_LastAugment.Last_Augment
    FROM tbl_StaffList LEFT JOIN qry_LastAugment ON tbl_StaffList.STAFFID = qry_LastAugment.STAFFID
    WHERE (((tbl_StaffList.Augment_Status)=True))
    ORDER BY tbl_StaffList.EOD DESC , qry_LastAugment.Last_Augment;
    When it comes to the ORDER BY I am having the issue of it not listing properly or how I would like it rather. I want the query to list the results where the EOD date(Seniority Date) is from newest staff member to oldest staff member so in descending order. I then want it to order next by LastAugment date where the oldest augment date is first and newest is last otherwise Ascending order. With the ORDER BY that I am currently using it does that but if people have the same EOD date it keeps them grouped together which I dont want. Example below.


    Click image for larger version. 

Name:	ORDERBY.png 
Views:	26 
Size:	6.7 KB 
ID:	42877

    So in this image the middle column is the EOD field and the last column is the LastAugment field. That is how it currently displays but how I need it to actually display is the 10/5/2014 EOD that has a LastAugment of 10/11/2020 should now be at the very bottom as everyone else in the list should then get augmented in the order of their EOD date first then by LastAugment after before that staff member gets augmented again. This rotation is just on going then based off of last augment after all staff members have been augmented. Thanks for the help.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    ...
    ORDER BY Nz(qry_LastAugment.Last_Augment, tbl_StaffList.EOD) DESC
    

  3. #3
    MichaelSB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    12
    That didnt work. No errors just not ordered correctly still.

  4. #4
    MichaelSB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    12
    I have found that if I open the query and sort the information by right clicking the field itself then sort from newest to oldest for the EOD then sort oldest to newest from the Last_Augment field it displays things correctly. But I cant get that same display by simply using the SQL view with an ORDER BY.

    I have been able to get the ORDER BY to work this way.

    Code:
    ORDER BY [qry_ActiveAugmentStatus].[Last_Augment], [qry_ActiveAugmentStatus].[EOD] DESC;
    That is not in the query itself though but rather in the form and the report i'm generating based off that query. It works but I was trying to simplify it by just having the query qry_ActiveAugmentStatus already ordered so I could just pull the information to the form combo box and to the report already ordered properly rather than ORDER at the form and report level. Am I overthinking it or is letting the query alone and ordering the information both at the form level and report level separately the right move?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    reports ignore any ordering in the recordsource - you need to use the report sorting and grouping functionality.

  6. #6
    MichaelSB is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    12
    Alright. Is that the same for form combo boxes? I'm assuming so since I had to sort from the combo box rather as well like I did for the report. Thanks for the help by the way.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no, only reports - for forms, combos, listboxes you sort in the record/rowsource - although for forms you can set the orderby property

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

Similar Threads

  1. Replies: 5
    Last Post: 08-27-2019, 08:41 AM
  2. order file and order line Selection -raw data
    By BeneDor in forum Import/Export Data
    Replies: 5
    Last Post: 09-14-2017, 04:46 PM
  3. Query order
    By Dave_D in forum Access
    Replies: 1
    Last Post: 08-16-2017, 02:22 PM
  4. Replies: 2
    Last Post: 03-07-2016, 05:02 PM
  5. Replies: 3
    Last Post: 02-09-2016, 04:36 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