Results 1 to 8 of 8
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Ranking values in a Combo Box

    Hi all,

    I am creating a survey and have a combo box field with 5 values in the drop down list: Blue, Green, Red, White, Yellow.

    Is there a way use this field to have the responder rank the colors in order of their favorite? And how would I store the ranking in the underlying table so they are useful in a survey results report.

    Thanks in advance

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Will it always only be 5 different colors? Do they have rank all five or only a few?

  3. #3
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by kd2017 View Post
    Will it always only be 5 different colors? Do they have rank all five or only a few?
    It will actually be 8 values and they have to rank all every time

    these are the actual values in the combo box


    • Orange
    • Blue
    • Green
    • White
    • Clear
    • Fluorescent Yellow

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    IF it will only ever by those 8 colors then I would suggest making a table with 8 fields, either one for each color and the field contains the users rank for that color, or one field for each rank and the field contains a reference to that color.
    *this solution doesn't feel very normalized though. These fields might be added to the responder table or in a seperate one-to-one table.

    You could create a junction table to link the responder to a color along with a ranking of the color with the key being a combo of the responderID and colorID, and also create a unique index on the responder & ranking. This will require vba to validate that the user has created a ranking for every color.

    As for the form there are also several ways to go about it. You could have 8 comboboxes set up in a cascading fashion, after the user picks the first color the rest of the combos remove that color from the list, until they run out of colors. You could just have a listbox and the user clicks up/down buttons to rearrange the colors in their preference, you could do a combination of the two and have one combobox where the user picks a color and it will add it to a listbox and remove the option from the combobox and repeat. There are other ways. All of them involve coding VBA.

    Maybe someone else has a better idea???

    If you decide how you'd like to do and start building your form we can help with the vba, shouldn't be too difficult.

    [EDIT]
    Purely from a usability standpoint what about having 8 command buttons on the form, each button represents a color and you can change background color of that button to the color it represents. The user is instructed to click the buttons in their order of preference, as the user clicks them they will add that color to a listbox in the order they are clicked and they will also disable themselves and be greyed out. If the user changes their mind they can also remove colors from this list and the appropriate button would be re-enabled. User clicks submit and vba processes the list.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm trying to understand how you get 8 selections from a single combo box?? There are probably a dozen ways to do this.

    - 8 coloured rectangles with a textbox beside each for setting a value? You'd have to ensure someone cannot pick the same number 2x.
    - same rectangles with cascading combo beside each. You choose a value, value is dropped from subsequent combo list but user has to choose in order.
    - same rectangles; remove the chosen value from an array, each subsequent combo gets its values from what's left in the array.
    - if not an array, a module level variable that starts out with an 8 value combo value list. Each combo pick uses Replace function to remove the chosen value from the list. Each combo dropdown looks at the remaining list values
    - etc

    Your table would need at least 2 fields; 1 that represents the colour, 1 that is for the rank given. Thus after 7 tests, you'd have 56 records (7x8) and could query to select red and count where rank = 1 (or 2 to see how many times it came in second). Possibly might do a Totals query also to show how many times yellow was #x ( a cross tab would require at least 3 fields, I believe). If you increase the count for any particular ranking, then you are storing calculations, which is usually discouraged.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There are actually only six colours in post #3 (not 8) & that's if we count 'clear' as a colour.
    Is this a multivalued combo box?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    just an off the wall suggestion

    have 6, 8 whatever number of labels each with a backcolour of your choice. Use code on the 3 mouse events to enable the user to click on a label and move to positions that represent 1st, 2nd 3rd etc choices - you can then use the controls left or top position to determine order of choices.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There are actually only six colours in post #3 (not 8)
    I went by this
    It will actually be 8 values and they have to rank all every time
    so not much of this makes a whole lot of sense.

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

Similar Threads

  1. Replies: 5
    Last Post: 11-19-2017, 12:28 PM
  2. Replies: 11
    Last Post: 09-03-2015, 11:12 AM
  3. Replies: 3
    Last Post: 07-03-2013, 10:38 AM
  4. Ranking (Look for previous ranking)
    By leobear in forum Queries
    Replies: 3
    Last Post: 01-10-2012, 05:58 PM
  5. Replies: 1
    Last Post: 03-27-2010, 06:13 AM

Tags for this Thread

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