Form Link

[Annabelle]

I have the following tables:

tblModels
--fields--
ModelID
BandID
ModelName
MotorID
etc.

tblMotors
--fields--
MotorID
MotorBrandID
MotorName
etc.

I need to link the 2 tables via MotorID. The data type for the MotorID field in both tables is "number". Please note that not all motors are available on all models. I tried to do the link via a query and was not successful. My idea was to include the ModelID and the MotorID fields (both "number" data types) and set the criteria to be for example:
ModelID criteria = 1
MotorID criteria = 1,2,3,4,16,17,20,21
meaning those MotorIDs are available on ModelID 1.

All suggestions will be appreciated. Thanks.

Annabelle

 

[dynamictiger]

I don't know if this is the best solution but it should work:

tblModels
--fields--
ModelID
ModelName
etc.

tblMotors
--fields--
MotorID
MotorBrandID
MotorName
etc.

tblBrand
BrandID
BrandName
etc

trelModelMotorBrand
ModelID
MotorID
BrandID

In this model many models can have many motors which can have many brands etc