No multiple data

[Demetious]

Folks,
I could use a bit of help with my DB.
I have an inventory table set up with the following basic fields.
[Mfr Part #], [Qty], [Location],[Product]=(Combo Box)
All was well on my reports but someone said it wasn't wise to design the table with multiple choice fields and that they should be split up. My question is, how do I do that? I have one part that might be used on several products and when I pull a product report, I only want to see the inventory items for that product. Should I leave it the way I have it because it works or should I redesign? Thanks for the help!

 

[boblarson]

1. Multiple data should not be stored in the same field.

2. If you have multiples you would use another table to store the information. Somewhat like this:

tblMfrParts
MfrPartID - Autonumber (PK)
MfrPartNum - Text
MfrPartDesc - Text

tblMfrPartProducts (junction table)
MfrPartProductsID - Autonumber (PK)
MfrPartID - Long Integer (FK)
ProductID - Long Integer (FK)

tblProducts
ProductID - Autonumber (PK)
ProductNum - Text
ProductName - Text

Something sort of like that. Then you use a form and subform to enter data. For reports a report/subreport would most likely be your usage.

Also, do not use the combo boxes at table level (lookups at table level are not good). See here for why.

 

[Demetious]

Bob,
Here is an example of what I have so far. Are you saying I should create a table for the product combo box? If so, wouldn't that create the redundancy that access is supposed to eliminate? It has been a long time since I have tried to use Access but I hope to learn while I work. Thanks again for any help.

 

[boblarson]

Yes, the product should have its own table with an ID and the Product name as the bare minimums (you can have more info about the product, but those two).

You would remove the table lookup. See here for how. And then you would use a FORM with a combo box on it to store the ID (bound to the table) and show the description.

If you leave the lookups at table level it is just going to cause you problem after problem when trying to query and do reports.