Table Design and Fields?

[Chertsey]

I have a table that has School data in it. I need to be able to indicate that
a specific school has numerous options that can be designated to it - or not.
There are 8 options. The selection can be a combination of any or all of the
choices. Some of the options would be for example "WheelChair Access", "Deaf
& Hard of Hearing" and others. Does the fact that I can choose more than one
of the options preclude me from using an Option Group and thereby forcing me
to create a field for each of the choices, that may in most cases be blank.

Regards

 

[Gina Whipp]

Chertsey,

How are the options stored? Do you only have one table? How do you assign
more than one option to a school?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Steve]

Your school table should look like:
TblSchool
SchoolID
<school data fields>

You need to add two more tables:
TblOption
OptionID
Option

You would record "WheelChair Access", "Deaf & Hard of Hearing" and other
options in TblOption.

TblSchoolOption
SchoolOptionID
SchoolID
OptionID

Steve
(e-mail address removed)

 

[JD McLeod]

Hello Steve,
This problem sounds a lot like a problem i am having in a post "Table Design
or Report Design?". This sounds like what i need to fix my problem, but I do
have one question. How would you go about populating third table? I would
assume you would have a separate record for each option. For example, you
could have
School1,Option1
School1,Option2
School1,Option3
School2,Option2
How would you set up the forms, subforms, etc to achieve this. thanks.

 

[Chertsey]

Gina,
Those questions are the exact point of my post and are the questions that
need to be answered!

 

[Gina Whipp]

Chertsey,

Well then you will need to provide me with your tables and their field
names. What it sounds like is you are trying to make a spreadsheet in
Access which is why you running into issues.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Steve]

Hello JD,

Yes, you would have a separate record for each option a school has. Your
example is exactly right. Data entry would be via a form/subform. The main
form would be based on TblSchool and the subform would be based on
TblSchoolOption. LinkMaster and LinkChild would be SchoolID. When you enter
a record in the subform, SchoolID in TblSchoolOption would be automatically
entered by Access. In the subform, you would enter OptionID via a combobox
or listbox with a rowsource of TblOption. Whichever you choose, set the
Bound Column property (Data tab) to 1, Column Count property (Format tab) to
2 and Column Width property (Format tab) to 0;3.

Steve