Table Relationship Question

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.

--
"Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Monet 138" wrote in message
...
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.

--
"Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein

Sweet! Extremely simple solution. Thanks!

--
"Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein


"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Monet 138" wrote in message
...
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.

--
"Imagination is more important than Knowledge. Knowledge is limited,
Imagination encircles the world." ~Albert Einstein