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.

 

[John Vinson]

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

What you need to do is add the Users table to the query TWICE -
joining one instance to msg_from and the other instance to msg_to.