asked on

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.

Dominator1025

ASKER

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.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

upload a copy of your db

SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dominator1025

ASKER

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!

PatHartman

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.