How can I tie model numbers to manufacturers' names in table(s) ?

[OST Backflow & CCC]

In Access 2003 I'm starting to build a database that will store and print
data from annual testing of water control valves. I have intended to have a
table with fields describing various characteristics of the valves: Make,
Model, Size, Type, Serial Number, Location, Downstream Connection. The Serial
Number, of course, would be the primary key, and none of the fields are
interdependent except Make & Model. Each Make has several Models, and each
Model belongs to only one make. Thus, entering the Model would determine the
make, and entering the Make would limit the choice of models to only those
pertaining to that make. It seems like there should be a simple way to design
this, but it is eluding me (I am a novice). How can I make an entry in one
field determine or limit the entry in another field? I could just enter each
field manually or by choosing from a list, but I would like to design it so
that the Make and Model numbers are always correctly matched.

 

[Golfinray]

Forms and subforms, if set up properly, have a one to many relationship. The
mainform is the one, which would be your serial number, and the many (the
subform) would be the many items you want to know about that serial number.
You may even want to have one mainform with with a couple of subforms
depending on how you want to divide it. Make sure you tables are related (go
to tools/relationships) and join the tables with the primary key. Then create
the form. Use the form wizard as it will ask you what you want. Then add the
subform(s) to the mainform. Use the subform wizard to help you set that up.
The most important part is to have the mainform linked to the subform(s)
properly. The subform wizard should do that for you but if you need to
verify, go to the form properties, go to the data tab, and check master/child
link properties.

 

[Tom van Stiphout]

On Tue, 19 Feb 2008 23:58:01 -0800, OST Backflow & CCC <OST Backflow &
(e-mail address removed)> wrote:

Don't think in terms of spreadsheets, but in terms of relational
database design.
If I gave you a ModelID, would you be able to find the Make? Yes. So
don't store both.
tblMakes
MakeID int auto PK
MakeName

tblModels
ModelID int auto PK
ModelName
MakeID int FK

tblValves
ValveID int auto PK
ModelID
SerialNo UniqueIdx
LocationID
....etc

Valve size and type: where you put these fields depends on if they are
the same per model or not. For example if Valve Model VM1000 always is
a 1 inch valve, put it in Models, if it varies, put it in Valves.

Note how I did not make SerialNo the PK. You could, but I am always
reluctant to display PK values to the users. Too often they will want
to change them. You could solve that with Cascade Update, but I'm not
a big fan of that.

Don't forget to create Relationships, and enforce them.

-Tom.