relationships?

[Guest]

I collect information on parts being used by several companies for several
types of vehicles. The various companies submit the list of parts by using
their own part number, I have to match up their part number to a universal
part number. So I made tbl-Parts - (pk PartsID) (PartNumber)
(UniversalNumber) and
tbl-Universal (pk UniversalID) (UniversalNumber) (UniversalDescription)
I want to make a parent form based on tbl-parts and a subform from
tbl-Universal, so when parts are entered on the form the universal number
will populate along with a description. I've made a couple of variations of
this but I keep getting the wrong information populating or my relationships
are not going the right direction, can someone give me an idea how they would
set up these tables and their relationships?
Thank you for your time and assistance.

 

[Guest]

I would use a third table as a junction table and two different forms. The
reason for this is it seems to me that you could have two UniversalNumber for
the same PartNumber. Also true would be two PartNumber for a UniversalNumber.

The junction table would be one-to-many from PartNumber to junction table
and one-to-many from UniversalNumber to junction table.

tbl-Parts --
PartsID - PK
PartNumber
PartDescription

tbl-Universal --
UniversalID - PK
UniversalNumber
UniversalDescription

tbl-PartsUniversal --
PartsID - FK
UniversalID - FK

One form would be Parts with a subform for Universal. The other would be
Universal with subform for Parts.

 

[Guest]

Thank you. That's what I needed.

I would use a third table as a junction table and two different forms. The
reason for this is it seems to me that you could have two UniversalNumber for
the same PartNumber. Also true would be two PartNumber for a UniversalNumber.

The junction table would be one-to-many from PartNumber to junction table
and one-to-many from UniversalNumber to junction table.

tbl-Parts --
PartsID - PK
PartNumber
PartDescription

tbl-Universal --
UniversalID - PK
UniversalNumber
UniversalDescription

tbl-PartsUniversal --
PartsID - FK
UniversalID - FK

One form would be Parts with a subform for Universal. The other would be
Universal with subform for Parts.