multiple UserIDs referencing multiple users

[twas via AccessMonster.com]

I have a many-to-many relationship set up (with a linking table with two one-
many relationships), where both "many" sides include a UserID that references
a User (with name, phone, etc.). For example, I could have a Left table
(with a LinkLeftID as primary key and a UserID), a Joining table (with a
LinkLeftID, a LinkRightID, and an Instance) , and a Right table (with a
LinkRightID as primary key and a UserID). A second table would have UserID
as a primary key and UserName. How do I setup a query that gives the left
and right user names?
I would like to generate a query that would have columns something like
Instance LeftUserName RightUserName

thanks
Twas

 

[pietlinden]

I have a many-to-many relationship set up (with a linking table with two one-
many relationships), where both "many" sides include a UserID that references
a User (with name, phone, etc.). For example, I could have a Left table
(with a LinkLeftID as primary key and a UserID), a Joining table (with a
LinkLeftID, a LinkRightID, and an Instance) , and a Right table (with a
LinkRightID as primary key and a UserID). A second table would have UserID
as a primary key and UserName. How do I setup a query that gives the left
and right user names?
I would like to generate a query that would have columns something like
Instance LeftUserName RightUserName

thanks
Twas

--

I must be missing something. could you post an abbreviated version of
your tables (relevant fields only or a small subset). Why do you have
UserID in two parent tables?

 

[twas via AccessMonster.com]

This was a very abbreviated version of the tables; the real application is
far more complex. I included only what seemed to be essential fields in
sample tables.

I have two parent tables that each relate back to a single common set of
users, using a UserID key rather than duplicating all the user data. Each
parent field has a number of fields, and each relates to someone in the User
table (system administrators for the Left side, normal users for the Right
side, for example). SA's and normal users are both people with names, phone
numbers, and more. The application appeared to need a UserID in the parent
table on both sides.

In the meantime, I've started to creat two new queries one that returns the
user information for the Left side, and one that returns the user information
for the Right side. The nested queries should provide the correct User data
for each side of the many-many relationship, but I'm hoping there is a
simpler approach.

Twas

I have a many-to-many relationship set up (with a linking table with two one-
many relationships), where both "many" sides include a UserID that references

[quoted text clipped - 11 lines]

--

I must be missing something. could you post an abbreviated version of
your tables (relevant fields only or a small subset). Why do you have
UserID in two parent tables?

 

[twas via AccessMonster.com]

There were two fixes:
for the basic problem, adding copies oi the "one" side table as a
Relationshop places an alias of the query or table with _1, _2 added to the
name, like User_1. Then then when Access references something in User_1, it
pulls the referenced field using the right relationship -- no need to add
extra queries. So the answer to the question as posted is just to add the
tables multiple times, creating separate relationships for each reference,
and then to use the fields from those aliased tables as outputs. Easier to do
than to say.

for a database with many-many relationships, which are implemented as left
(one instance) related to links (many) related to right (one instance), it's
really easy to get ambiguous join errors. The easy fix is to create a query
that just has a one-many relationship, and then use that query as a source in
the next query. This creates nested queries, and Access doesn't have any
ambiguous joins. Easier to say than to do.

twas

This was a very abbreviated version of the tables; the real application is
far more complex. I included only what seemed to be essential fields in
sample tables.

I have two parent tables that each relate back to a single common set of
users, using a UserID key rather than duplicating all the user data. Each
parent field has a number of fields, and each relates to someone in the User
table (system administrators for the Left side, normal users for the Right
side, for example). SA's and normal users are both people with names, phone
numbers, and more. The application appeared to need a UserID in the parent
table on both sides.

In the meantime, I've started to creat two new queries one that returns the
user information for the Left side, and one that returns the user information
for the Right side. The nested queries should provide the correct User data
for each side of the many-many relationship, but I'm hoping there is a
simpler approach.

Twas
[quoted text clipped - 4 lines]

your tables (relevant fields only or a small subset). Why do you have
UserID in two parent tables?