Using a table twice in the same query

[Beringer]

I once came across something about being able to use the same table twice in
one query and now I can't remember where I read it so I am asking here.

I have a query that contains 2 different columns of Users that store a
foreign key to the Users table. I would like to do a query that returns the
names of both users in one, using joins, but don't know how.

Can someone help me?

Thank you in advance,
Eric

 

[Allen Browne]

Drag a second copy of the table into the query design window.
If the table is called A, Access will alias it as A_1.
You can use the properties box to assign another alias if you like.

Now you can accept one join line to table A, and another to A_1.

 

[Van T. Dinh]

SELECT MT.Field1, MT.Field2, ...
U1.UserName, User2.UserName
FROM ([tblMain] As MT INNER JOIN
[tblUser] AS U1 ON MT.frg_UserID_1 = U1.UserID) INNER JOIN
[tblUser] AS U2 ON MT.frg_UserID_2 = U2.UserID
WHERE ...

Adjust the type of joins if required.

 

[Beringer]

Thanks guys!