By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

join question

P: n/a
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.

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.