join question

[Pejo]

Table Users
FName(text), LName(text), UserID(number)

Table Correspondence.
Date(datetime), msg_From(number), msg_To(number)

Table Users Data
John, Quincy, 1
Jane, Doe, 2

Table Correspondence Data
2/2/2002, 1, 2
Ideally the query would return a Record that would look like.

2/2/2002 John Quincy Jane Doe

At first I thought I could do this with a simple join but then realized I
could only join on the msg_from or the msg_to and not both
if I right join I can get both but unfortunately I can't figure out how to
get both Users on one row.

I figure maybe I need to use nested queries???

Keep in mind it has be usable in an access DB so T-SQL is not an option.

Thanks.

pejo.

[Larry Linson]

Simple: in the Query Builder, add the Correspondence table, then add the
Users table (twice). Join the From field to the UserId field in one copy.
Join the To field to the UserId field in the other copy of Users (Query
Builder will call it Users_1), then pull down the date field, and the First
and Last names fields from each copy of the Users table. Works exactly as
you want, and here's the SQL, copied unchanged from the Query Builder (note
I did change the name of the "Date" field to "CorrDate", because "Date" is
an Access reserved name and can, in some circumstances, lead to unexpected
results if used as a field name).

SELECT Correspondence.CorrDate, Users.FName, Users.LName, Users_1.FName,
Users_1.LName
FROM (Correspondence INNER JOIN Users ON Correspondence.msg_From =
Users.UserID) INNER JOIN Users AS Users_1 ON Correspondence.msg_To =
Users_1.UserID;

Larry Linson
Microsoft Access MVP

"Pejo" <po**********@hotmail.com> wrote in message
news:7q********************@news20.bellglobal.com. ..

Table Users
FName(text), LName(text), UserID(number)

Table Correspondence.
Date(datetime), msg_From(number), msg_To(number)

Table Users Data
John, Quincy, 1
Jane, Doe, 2

Table Correspondence Data
2/2/2002, 1, 2
Ideally the query would return a Record that would look like.

2/2/2002 John Quincy Jane Doe

At first I thought I could do this with a simple join but then realized I
could only join on the msg_from or the msg_to and not both
if I right join I can get both but unfortunately I can't figure out how to get both Users on one row.

I figure maybe I need to use nested queries???

Keep in mind it has be usable in an access DB so T-SQL is not an option.

Thanks.

pejo.