Hello,

I'm exploring this concept.....

I envision having two tables. Table A is the main table, and table B contains auxiliary information for each record in table A.

On the main form, I would like to have a subform at the bottom that shows all the related information related to each record from table A. (i.e. main form shows car number, and in the subform, it shows tire condition, manufactur status, production facility, etc)

Here is the problem with the current recordset......the IDs in table B can have multiple IDs in A. (so it's a one-to-many going from table B to table A) So when you link B to A, id in B will have multiple value to look up from A, which is not desirable.

I could make it so that all the IDS in table B are unique and have only one table A id to look up from, but is there any way around this without having to alter table B id?


Many thanks

My impression is that it isn't LIKE a many-to-many relationship but IS constructed as a many-to-many.

Clearly this won't work since you have, for example, a single tire condition record in B applied to multiple vehicles in A. It needs to be strictly one-to-many (A-to-B).

I think you are confusing the concepts of primary key and foreign key.

The foreign key of the related records in table B should be the primary key of the record in A. If the foreign key in B was unique then you would have a one-to-one relationship.