Results 1 to 10 of 10
  1. #1
    Dadeo is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2017
    Posts
    16

    Sorting the results of a combo box


    I have 2 combo boxes that are sorted by a sort field by number, but after I do my selections the results are not sorted by that field. One field is assignment 1 and the other is assignment 2. Both fields use the data in tblassignments. HEach assignment field has a query for the sort as ascending. How do I get my results sorted by the order column? After I do my selections I get the following results.


    assignment1: JFK, Monday, Tuesday, Med/Surg, Semester 1
    assignment2: Semester 2, Wednesday, Thursday, Palms West, Peds

    It should read

    assignment1: JFK, Med/Surg, Semester 1, Monday, Tuesday
    assignment2: Palms West, Peds, Semester 2, Wednesday, Thursday



    ID Order Assignment
    1 1 JFK
    2 2 Palms West
    3 3 St Marys
    4 5 Med/Surg
    5 6 Peds
    6 7 OB
    7 8 Tele
    8 9 Semester 1
    9 10 Semester 2
    10 11 Semester 3
    11 12 Semester 4
    12 13 Monday
    13 14 Tuesday
    14 15 Wednesday
    15 16 Thursday
    16 17 Friday
    17 18 Saturday
    18 4 Beathesda West

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Post the SQL that is the row source for each of the comboboxes.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the query does the sort. AFTER the fields that display , set sort order that does NOT display.

  4. #4
    Dadeo is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2017
    Posts
    16
    Had problems posting because of the firewall
    Both have the same statement

    SELECT tblassignments.ID, tblassignments.Order, tblassignments.Assignment FROM tblassignments ORDER BY tblassignments.Order;

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Dadeo View Post
    <snip>, but after I do my selections the results are not sorted by that field. <snip>
    I'm struggling to understand.

    The combo boxes data are sorted correctly.
    Do you mean that after you make 5 selections (ie 5 records in the form) that the records are not sorted the same order as the combo boxes?

    Do you have 2 fields in the bound table that are named "assignment1" and "assignment2"?

    Could you post the DB or a picture?

  6. #6
    Dadeo is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2017
    Posts
    16
    Quote Originally Posted by ssanfu View Post
    I'm struggling to understand.

    The combo boxes data are sorted correctly.
    Do you mean that after you make 5 selections (ie 5 records in the form) that the records are not sorted the same order as the combo boxes?

    Do you have 2 fields in the bound table that are named "assignment1" and "assignment2"?

    Could you post the DB or a picture?
    Assignment 1 and 2 are completely separate, The only thing they share is the table that the combo box uses. You are correct the data is sorted in the drop down correctly but not in the field after the section. You can see here what the field looks like after the selection. The way I want it to read if you look at the table on the original post is: using the orders field as the sort: option 1 - 4 should be first in the list then options 5 - 8 second option 9 - 12 third and last option 13 - 18

    assignment1: JFK, Monday, Tuesday, Med/Surg, Semester 1
    assignment2: Semester 2, Wednesday, Thursday, Palms West, Peds

    It should read

    assignment1: JFK, Med/Surg, Semester 1, Monday, Tuesday
    assignment2: Palms West, Peds, Semester 2, Wednesday, Thursday
    Click image for larger version. 

Name:	ASSIGNMENT 1.png 
Views:	13 
Size:	26.7 KB 
ID:	31304
    Attached Thumbnails Attached Thumbnails ASSIGNMENT 1.png  

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This looks like a multivalue field. If so, you will get unpredictable results. I suggest that you avoid them at all costs.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I concur with aytee111.

    Access implements MVFs with an internal junction table, simply hiding the added complexity from the user. One of the big limitations of MVFs is related to integration with, or porting to, other DBMS, as the junction tables need to be rebuilt first. While MS added MVF fields for simplicity for beginners (non-programmers), MS did not fully integrate MVFs to make them usable. MVFs break normalization rules because the fields are not Atomic; ie MVFs break the First Normal Form (1NF) rule.
    This is why most experienced Access programmers will not use them.


    In any case, because you have a MVF, you will not be able to sort the selected values in the two combo boxes.



    See Multivalued datatypes considered harmful (2 pages)

  9. #9
    Dadeo is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2017
    Posts
    16
    Quote Originally Posted by ssanfu View Post
    I concur with aytee111.

    Access implements MVFs with an internal junction table, simply hiding the added complexity from the user. One of the big limitations of MVFs is related to integration with, or porting to, other DBMS, as the junction tables need to be rebuilt first. While MS added MVF fields for simplicity for beginners (non-programmers), MS did not fully integrate MVFs to make them usable. MVFs break normalization rules because the fields are not Atomic; ie MVFs break the First Normal Form (1NF) rule.
    This is why most experienced Access programmers will not use them.


    In any case, because you have a MVF, you will not be able to sort the selected values in the two combo boxes.



    See Multivalued datatypes considered harmful (2 pages)
    Thanks all for your input.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have NEVER used MVF and if I did I would not put different types of data in one field. Each data element should each be in their own field. Although the assignment days in MVF would be logical. However, this will just complicate record searches and filtering.

    Is adjunct really limited to 2 assignments?
    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.

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

Similar Threads

  1. Sorting Combo Box List
    By jrmbach in forum Forms
    Replies: 1
    Last Post: 08-05-2013, 04:22 PM
  2. Sorting Crosstab query results?
    By Dragongem in forum Queries
    Replies: 1
    Last Post: 06-13-2013, 09:21 PM
  3. Sorting combo boxes
    By thefairman in forum Forms
    Replies: 2
    Last Post: 02-29-2012, 12:12 PM
  4. Sorting Data in a Combo Box
    By Mutant_Mike in forum Access
    Replies: 1
    Last Post: 05-03-2011, 03:59 PM
  5. Sorting a Combo box?
    By newtoAccess in forum Access
    Replies: 3
    Last Post: 03-19-2011, 03:57 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