Opinion

[David]

Hello All,

I wanted to get other opinions on how I should set up my database. I
want the end user to type in a UPC and then a form will display all the
information {label name, label ID, container type, container ID, cap
type, cap ID etc... for that upc. Should I make just one table with all
this info on it or make many tables i.e. caps table which will have a
cap type field and a cap ID field and so on. When a new upc needs to be
entered all data for that upc needs to be entered as well. Also there
may be times where an existing cap ID will change.

TIA,
David

 

[Joseph Meehan]

Hello All,

I wanted to get other opinions on how I should set up my database. I
want the end user to type in a UPC and then a form will display all
the information {label name, label ID, container type, container ID,
cap type, cap ID etc... for that upc. Should I make just one table
with all this info on it or make many tables i.e. caps table which
will have a cap type field and a cap ID field and so on. When a new
upc needs to be entered all data for that upc needs to be entered as
well. Also there may be times where an existing cap ID will change.

TIA,
David

I would start with one table. Then make sub tables for any data that
you find repeated time and time again, especially if it is a long
description.

For example if there are only green and yellow for colors, I would put
them in a separate table. If the label name was different on each UPC, then
it would stay in the primary table as would container type if there were 500
container types out of a total of 700 total records in the primary table.

In the end your primary table may be the only table or it may be a table
of only the UPC codes linked to all the other tables.

It is your data that determines what is the best design.

Good Luck

 

[Douglas J. Steele]

If you're thinking of having a table that has fields named "Container Type",
"Container ID", "Cap Type", "Cap ID", etc., that's definitely not the
correct approach.

Take a look at the Bill Of Materials example at "The Access Web"
http://www.mvps.org/access/modules/mdl0027.htm and see whether you can
modify it for your purposes.

 

[David]

Thanks Doug and Joe for your opinion. I guess my concern is for the
person entering the new data for a new upc. If I had many tables (caps
table, container table, upc table, and a tray table) and that person
entered in all the new info in each table how do I go about
corresponding certain data in each table with a certain upc in the upc
table?

Thank you
David

 

[Douglas J. Steele]

First of all, use Forms for all entry, not directly into the tables.

Assuming you're trying to link each of the elements to a specific UPC, you'd
have a combo box for each element, representing all known values for that
element. By using the NotInList event, you can allow the user to add an
additional value for the element if what they want doesn't exist in the
combo.