Table Relationship Question

[Monet 138]

I have two tables, (A) is a detailed Location information table, the other
(B) is containing a list of Area/City/Town names. On table (A) there are two
columns for Area/City/Town, one listed as Primary and the other Secondary. I
want both of these fields to use the information from and be linked to table
(B) but am unsure how to go about this.

Here are my two guesses.
1. Setup another table (B2), where (B2).ID would be linked to (B).Area and
(B2).Area (which would just be an autonumber instead of text) would be linked
to (A).Secondary, while (B).ID is linked to (A).Primary.

2. Link (B).ID to (A).Primary in the Relationships window, and use a Form
which uses combo box queries of (B) for entering info into (A).Primary and
(A).Secondary. This would limit the entries into (A).Secondary to the list
within (B) but I don't see how (A).Secondary would be updated if a record
within (B) needed to be changed.

#1 seems incorrect in that I'm unsure how to make (B2) always contain the
same number of records as (B), such as when records were added or deleted.
#2 seems to have cascading update problems.

Thanks in advance for the help.

 

[Allen Browne]

In the Relationships Window, add a 2nd copy of table B.
Access will alias it as B_1.

You can now create a relationship from A.PrimaryTown to B.Town, and another
relationship from A.SecondaryTown to B_1.Town.