Link duplicate fields

[Paul B.]

For the purpose of speed building this database, I had copied a field from
one table to the other three.

Now I know that storing duplicate data is redundant and I would like to
learn how to link these tables so I can delete the duplicate fields.

The field is VehicleNumber. It exists in tblVehicles, tblVehicleInfo,
tblDispositions, and tblServiceCentre

The VehicleNumber (tblVehicles) does not change, but who it's assigned to
and where does (tblVehicleInfo).

The tblDispositions holds information relating to the VehicleNumber, such as
servicing and accidents.

The tblServiceCentre lists the location the VehicleNumber gets serviced at
along with other info regarding the service centre.

In each of these tables, the VehicleNumber field is in the same order(it
helped me to do the data entry).


If someone could point me in the right direction for linking the
tblServiceCentre and tblVehicles, I could probably figure the rest out.

thanks in advance.

 

[tina]

generally speaking, foreign key fields are the only fields in your tables
that *should* duplicate data from another table. that's how two tables are
linked.

is VehicleNumber the primary key field of tblVehicles? if so, then the
VehicleNumber field *should* be in the related tables, tblVehicleInfo and
tblDispositions and tblServiceCentre - as a "foreign key". then you link the
tables, in the Relationships window, from the primary key to each foreign
key, and enforce referential integrity. hint: you don't have to use the
same field name in each table; in fact, i don't, because it can be confusing
when writing queries or code. i'd probably name the foreign key field
something like
iVehNofk
dVehNofk
sVehNofk
in each table, respectively.

btw, is each vehicle *always* serviced at the same service centre? if not,
it's possible that the service centre should be tied to tblDispositions
instead of tblVehicles. just food for thought; not knowing the full layout
or purpose of each table, i can't recommend one way or the other.

hth

 

[Paul B.]

Thanks Tina,

I think I understand and will give it a try.

Cheers