Question question about creating lookup forms

[ahseenam]

I'm creating a database to track our participants in Access 2010.

I have a few fields that I want to assign lookup values to.

School (MLK High, Central High, Northeast High...)

Interests (Visual Arts, Drama, Sports, Literature....)

Color (Red, Blue, Green...)

I could create a table School, another table Interests, another table Color, and then have the source for each one to be SELECT Color.ColorName FROM Color and then SELECT School.SchoolName FROM School and then SELECT Interests.InterestPicker from Interest.... this is how I usually do things.


But then I wondered if instead of three separate tables, I could have one table called ParticipantData with fields called School, Interests, Color... and then have my lookup queries be SELECT ParticipantData.School from ParticipantData, and SELECT ParticipantData.Interests FROM Participant, and SELECT ParticipantData.Color FROM ParticipantData


Are there advantages/disadvantages to one way or the other?

 

[plog]

It sounds like you want to do the wrong thing (A) the wrong way(B). Let's start with B first.

According to your method this lookup table is going to have 3 fields--School, Interests and Color. Visualize this in table form and you will see the problem with this method--what happens when you have more Schools than Colors and Interests? Some colors and interests will be blank. And why is MLK High in the same record as Visual Arts and Red? It makes it seem like those 3 are somehow related when in actuality they are just the first 3 values you entered for each field.

The smarter way to do this wrong thing is to have 1 table with 2 fields: ValueType (School, Interest, Color) and Value (MLK High, Central High, Visual Arts, Blue, Green, etc.). That way there are no blanks and the records are independent of themselves.

On to A. The cop-out, text-book reason both those ways is wrong is because the proper way to normalize 3 sets of independent data is with 3 tables. The real reason I would use 3 tables is because you might want to store more data with each record than you do now. For example, in a month you want the addresses of the schools or how large the faculty is. If you do it with 1 table you are going to have to have those fields for Color and Interests as well and you'd just be wasting space.

 

[wiklendt]

Also, in (a) you should be filling the autnumber primary key, not the text field from that table.

i.e., SELECT tblColor.ColorID, tblColor.ColorName FROM tblColor. your foreign key field should be number, not text.

[edit] but you should be using three separate tables, as plog urges.[/edit]