Multiple choice in Option Group

[BSC5905]

In my database I need the user’s to select multiple choices from an Option Group. For example, the Option Group has 30 choices. The operator can chose from 0 to all 30 choices. Once the user has made there selection, I will need to query only the selected data and put it in a report. My approach was to create a table and add the 30 options as text box’s and set the data type as yes/no. Is there a better way of doing this? I am pretty new with MS Access and did not know how to approach this problem. Your help would be greatly appreciated


Jonesy

 

[lespaul]

yes there is a better way. First you need to read the Fundamentals document in the link below. Your existing design is "committing spreadsheet". Can you tell us a little bit more about what these options are and what they pertain too?

Thanks


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[BSC5905]

The Option Group is for chemical use. The operator will select by checking all a box that applies to him/her. The check box would be on the left side of each option.

CHEMICAL USE HISTORY
Alcohol
Heroin
Crack
Cocaine
Marijuana
Hallucinogens
LPCP
Benzodiazepines
Sedatives
Inhalants
Barbiturates
Methamphetamines
Other stimulants
Nicotine/Tobacco
Non-Prescribed Methadone
Caffeine/Coffee/Soda
Other tranquilizers
Sedative/Hypnotics
Other Opiates/Synthetics (Daladid, Morphine, etc)
Other amphetamines (Crystal, Crank, etc

 

[lespaul]

Ok then somewhere you are storing the OperatorID, you are going to want to create another table that will store the operatorID and the specific Chemicals used:

Operator
OperatorID
Name
Address

Chemicals
ChemicalID
ChemicalName

OperatorChemicals
OperatorID
ChemicalID

The OperatorChemicals table is a standard "junction" table. You could eliminate the Chemicals table and just store:


Operator
OperatorID
Name
Address

OperatorChemicals
OperatorID
ChemicalName

However by keeping the chemicals in a table you don't end up with misspellings in the ChemicalName Field and it's easy to add new chemical choices by adding a new record in the Chemicals table.

HTH

Leslie

 

[BSC5905]

How do display all of the chemicals as a group on a form and select as many of the options (chemicals) that are displayed.