Can I select multiple options from a drop-down box in Access?

[Guest]

Currently, each option is a separate field in my table, and the users can
select yes or no for each individual option. To save space on my form, I'd
rather have a drop down box with all the options and have the user CTRL+click
to select multiple options. Is this possible?

 

[John Vinson]

Currently, each option is a separate field in my table, and the users can
select yes or no for each individual option. To save space on my form, I'd
rather have a drop down box with all the options and have the user CTRL+click
to select multiple options. Is this possible?

No, not as written.

I suspect that the problem is not in your Form but in your table
structure.

If you have a separate field for each option, you are embedding a many
(object) to many (option) relationship in each record in the table.
This structure is *wrong*!

Instead, consider using *three* tables: your current table (minus all
the option fields); a table of Options; and a table with two fields -
a link to the Primary Key of the main table, and a link to the primary
key of the Options table (this might be the option value itself, I
don't know what an "option" means in your business model).

You would then have one *RECORD* per option, rather than one *FIELD*.

A continuous Subform with a combo box on it can be used to enter this
information - you'ld just have all the options displayed in a column.


John W. Vinson[MVP]

 

[Guest]

I don't think I explaing my set up clearly. Here is an example...I want to
track the preferences of Artsy People, so I have them select their favorite
colors from the choices I give them. I'm using a 0,1 system (0=No, 1=Yes). I
have 3 text boxes - for White, Black, and Red. If Betty likes White and Red -
she would put a 1 in the text box corresponding with White, a 1 in the text
box corresponding to Red, and a zero in the text box corresponding to Black.
[The advantage to this 0,1 method is that I can tell which of the colors
Betty does or does not like versus the colors she didn't indicate a
preference on (which would be done by leaving the text box blank).]
With this method, I have 3 boxes for each color on my form. This takes up
space, so I'd rather make a table called Color_Options and use that table to
populate a drop-down box named "Favorite Colors". Can Betty select White AND
Red from that drop-down box?
If she can, how would that information be stored in the "Favorite Colors"
field? Would I be able to do a query listing all the people that like White
(even if they chose White and Red)?

 

[John Vinson]

I don't think I explaing my set up clearly. Here is an example...I want to
track the preferences of Artsy People, so I have them select their favorite
colors from the choices I give them. I'm using a 0,1 system (0=No, 1=Yes). I
have 3 text boxes - for White, Black, and Red.

Then you are storing data - a color - in a fieldname. This is
*incorrect*.

If Betty likes White and Red -
she would put a 1 in the text box corresponding with White, a 1 in the text
box corresponding to Red, and a zero in the text box corresponding to Black.
[The advantage to this 0,1 method is that I can tell which of the colors
Betty does or does not like versus the colors she didn't indicate a
preference on (which would be done by leaving the text box blank).]

The normalized design would allow this perfectly well.

With this method, I have 3 boxes for each color on my form. This takes up
space, so I'd rather make a table called Color_Options and use that table to
populate a drop-down box named "Favorite Colors". Can Betty select White AND
Red from that drop-down box?

Yes... *IF* the combo box (to use its proper name) is in a Subform
bound to a new table.

If she can, how would that information be stored in the "Favorite Colors"
field? Would I be able to do a query listing all the people that like White
(even if they chose White and Red)?

Yes.

To reiterate: you would have three tables - People, Colors (all the
allowable colors), and FavoriteColors.

The FavoriteColors table would have three fields - PersonID (a link to
the primary key of People), Color, and Preference (an integer, 0 or
1).

Your Form would be based on People; on it you would have a continuous
Subform with a combo box based on Colors, and a textbox for the
Preference.

If Betty (PersonID 312) likes White and Red, and dislikes Black and
Purple, the FavoriteColors table might contain records

312 White 1
312 Black 0
312 Red 1
312 Purple 0

If she has no preference about green, there'd simply be no entry in
the table for green.

This design allows you to easily change or add colors, to very simply
search for all people who like red or dislike purple, etc.

John W. Vinson[MVP]

 

[Guest]

This worked perfectly. Thanks!! I'm actually applying this method to another
database now, but for this one, I need to make sure that same color isn't
selected twice. Is there a way to do that?
I don't want Betty to have the following info the FavoriteColors table:
312 White 1
312 Black 0
312 Red 1
312 White 1
312 Black 1

 

[Tim Ferguson]

I don't want Betty to have the following info the FavoriteColors table:
312 White 1
312 Black 0
312 Red 1
312 White 1
312 Black 1

Put a unique index on the combination (BettyID, Colour).

What do the 1's and 0's mean?

Hope it helps


Tim F

 

[Guest]

I highlighted the PersonID and FavoriteColor and then selected the Primary
Key button. It works. I hope that made the combination of the two the primary
key.

Thanks!!

(The 0's and 1's indicate a favorite color (i.e. 0=No, 1=Yes))

 

[Tim Ferguson]

I highlighted the PersonID and FavoriteColor and then selected the
Primary Key button. It works. I hope that made the combination of the
two the primary key.

Point 1: yes it did.

Point 2: what you have here is an old-fashioned "junction table" for
implementing the M:M relationship between Persons and Colours... I guess
just goes to show that there is nothing special about junction tables.

All the best


Tim F