How do I connect 2 fields in one table to a single field in another table?

I think I'm having a brain cramp on this.  I have a single query that I am trying to design for some transactions that I am trying to report on. Initially, the query included a field from my main table that showed a code for the "From Location" and another field for the "To Location".  I just added another table that has all of the location codes in a single column, followed by the Names of the locations in another field.

I want to design my query to tie in the From Location Name, and the To Location Name.  In design query, I have 2 fields from my main table tied to a single field in the second table, but I don't think that will work and am not sure what to do.
Dominator1025Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
add another second table to your query grid
connect From to the first secondtable
connect To to the second secondtable
0
 
Dominator1025Author Commented:
To be more clear, the From and To Location codes in the main table are both in the same record, which then needs to be tied to multiple records in the second table that relates the codes to the names.
0
 
Rey Obrero (Capricorn1)Commented:
upload a copy of your db
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Nick67Connect With a Mentor Commented:
It is not intuitive, but you can add a table to a query MORE THAN ONCE.
Why?
For exactly this situation.
So add the table twice
First, it'll be tblLocation, and when you add it again it'll be tblLocation_1
Get rid of any pre-existing joins the Query Editor creates
Then create the join from tblLocation to the field that is the To location ID
Then create the join from tblLocation_1 to the field that is the from Location ID

You're then in business!
0
 
Dominator1025Author Commented:
Thanks guys. I wasn't sure exactly what Rey meant, but then Nick's answer made it click and drove it home for me.  I appreciate it!
0
 
PatHartmanCommented:
You will also want to alias the column names you select from tblLocation.  Assuming the name of the column you want to select is LocationName then for each of the two uses:

FromLocationName:tblLocation.LocationName

ToLocationName:tblLocation_1.LocationName

If you don't do this, Access will give them names you won't like because EVERY column in the query MUST have a UNIQUE name.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.