Stock control

[Accessidiot]

I'm trying to write a database to contain the part numbers of components on
vehicles.
Each vehicle has:
Oilfilter
fuelfilter
airfilter
airdryer

Then further to this each oilfilter, fuelfilter etc may be supplied by one
of 5 suppliers each using a different part number for the same item.
I'd like to have all the part numbers in one table and the vehicle details
in another.
However,

Vehicle "A" has oilfilter with part number "A354"
Vehicle "B" also has oilfilter with part number "A354"

I would like to have only one instance of "A354" in the part number table,
but more than one vehicle can be related to it.
I can't seem to get the relationship I need?
Thanks

 

[richard]

Within the Vehicle table you need to create the field for Oilfilter and in
this field store the part number "A534" in the record for "Vehicle A", this
would then relate to your parts table and you would only need one instance of
A534 in your parts table. You then need to create further fields in Vehicle
table for fuelfilter, airfilter and airdryer, each referenced to the parts
table, although if part numbers become the same at some point for different
components from different suppliers then you will have some problems with
duplication.

 

[Barry Andrew Hall]

Hi,

Really you need 3 tables. One for the vehicles recording just about the
vehicles, make sure this includes an ID...
vehicles;
ID, Reg, Color etc
1, AB1, Red

Then have a table for parts... make sure these have an ID, only add each
part once. You could have Type, this would be "oil" "fuel" "air" etc so;
Parts;
ID, Name, Type etc
1, Oil, Filter,
2, Air, Filter,
3, Oil, Engine

Finally a third table. This table is used to bridge the two. So you would
have

VehicleID, PartID
1, 1,
1, 2,
1, 3,
2, 3

So you have one list of vehicles, one list of parts and just a talble to say
that car 1 has this, that and the other

Lookup Normalisation online, its a fascinating subject.