structuring tables that might link to each other?
Posted on 2004-04-25
I have a database that includes two tables called, say, A and B. Each record in table A can have multiple records in table B, but some of these sets of multiple records in table B can apply to more than one record in table A. i.e. table A has 5 records, numbered A1 to A5. Table B has 12 records numbered B1 to B12. B1, B2 and B3, are all linked to A1; B4, B5 and B6 are linked to A2; and B7, B8 and B9 are linked to A3. But B10, B11 and B12 apply equally to both A4 and A5. There is no limit to the number of times a set of table B records can link to a single table A record.
In practical terms a user is required to select a table A record [in a form] and then add any number of table B records to it [in a sub-form]. But sometimes they don't want to enter multiple records twice, they just want to select a table A record, say A5, and rather than enter B13, B14 and B15 which are exactly the same as B10, B11 and B12 they just want to say "OK, I've selected A5, now I just want to tell the system that it should use the same table B records as A4".
What is the best way to structure / manage this in MS Access?