Multiple Selections in Tables

[joiry]

I hope I can form this question so it makes sense. I’ve been trying to figure out a way to do this, if it can be done at all.

Ok, imagine I’m making a database of widgets. In this database I’m creating a table of widget name, cost, weight, and color. This part is pretty simple.
Now, my widgets can come in many different colors. To accommodate this, I create a separate table for colors, let’s say red, green, blue, yellow and purple. I create a color item in the first widget table, and use the look up wizard to point it to the color table. This way on my forms I can select the color widget I want and have it all standardized.

This is where my problem comes in. My widgets can be multiple colors. How can I create a field in my widget table, to somehow reference that I can have a red and blue widget? So, when I query all my red widgets, I get the red ones and the ones with red in them?

Now, I could make an item in my widget table for each color and make it a yes/no field. But, my real problem is exponentially bigger than this so that isn’t what I want to do.

Thanks,

Joiry

 

[joiry]

One to Many Relationship

Ok, with your help and a lot of trial and error I think I might have this setup. As a double check, I created a widget database which has two "one to many" relationships and a quick form for me to input data. It's not pretty but seems to be working. Is this proper setup?

 

[joiry]

Table

Ok, thanks for all the help, it seems to be working now.

I've performed most of the suggestions. I'm far from a DBA, so I'm not educated as to standards for table and key naming conventions but I can see how that will be of value. Especially if someone else looks at this or I come back to it 6 months from now.

For now at least, I left the look ups. Mostly because it's the only way I know to ensure that my data is uniform and thus can be queried. If I left it as free text, I'd end up with "blue", "Blue, "BLUE", "blew", etc. If I didn't use the look up table and did manual key words, then adding new colors later would be somewhat of a pain.

Anyway, thanks again, I am operational. I do have a question on auto-populating forms, if that is even possible, but I'll ask that question in the proper forum.

Thanks,

Joiry

 

[ScottGem]

For now at least, I left the look ups. Mostly because it's the only way I know to ensure that my data is uniform and thus can be queried. If I left it as free text, I'd end up with "blue", "Blue, "BLUE", "blew", etc. If I didn't use the look up table and did manual key words, then adding new colors later would be somewhat of a pain.

You misunderstood what Pat was saying. Lookups should be done on the FORM level NOT on the table Level. You are very correct that you should use a lookup to ensure standard entries. But you do it with combo or list boxes or option groups on forms, not with lookups on the table level.

The following article may help: http://www.mvps.org/access/lookupfields.htm