two foreign keys in one tbl link to same tbl

[faust9]

Wow, I've found some good stuff here already... Thanks.

PROBLEM:
I have a table(we'll call it shipping) that has two columns FK'ed to another table (locations). My problem is I have no idea how to pull the information from the locations table into the shipping table.

DESCRIPTION:
My shipping table has a source and a destination column. Now, I have a fixed location list that applies to both columns and I'd like to avoid having a source tbl and a destination tbl... I'm an Access Neophyte to say the least (for now at least) but I have managed to put together a querie that pulls out most of the information that I need (name, phone number, yadda, yadda, yadda) but I can't seem to correctly link the FK's for the sources to the locations table as well as the FK's for the destinations to the locations table. When I run my querie I get an error or my Source(destination) column will display the correct information but the Destination(source) column will display ALL of the locations in the location table. Here's what I mean:

Source destination
detroit toledo
detroit cleveland
detroit San Diego
detroit London
detroit Paris
detroit detroit
Paris toledo
Paris cleveland
Paris San Diego
Paris London
Paris Paris
Paris detroit

.
.
.

The first and second source was detroit and Paris but the Destinations list all of the locations I have in the table.

How can I link two FK's in one table to another table in a querie?

Thanks.

 

[neileg]

I'm not clear what you are having trouble with. I would expect you to hold the ID of the location as an FK in either source or destination fields. So what is the issue?

 

[faust9]

Here's my tables:

tblShipping
    shippingID (primary key, autonumber)
    sourceLocationID
    destinationLocationID
    contactID
    requestedDate
    orderedDate
    transporterID
    .
    .
    .

tblLocations
    locationID (primary key, autonumber)
    locations

What I'm trying to do is display an entire order on one line of a querie. I have the relationships established but two of the columns link to a single table(the source and destination columns both link to the locations table). I can pull all of the required info (all the contact info, the transporter info, ...) except the source and destination info. For source and destination I've played around enough to either get an error outright or one of the columns will list all of the locations while the other will list the correct locations with each iteration of the full listing. So, if I wanted to ship from Detroit to Paris I'd get the listing in my first post where Detroit(the source) was correctly queried but the destination simply lists all of the items in the location table.

Sorry for any confusion and thanks for the help.

 

[neileg]

Ah...

I presume you have a query where you are linking the two tables. I suspect you only have one copy of the location table in query design view. You need two. Link one to the source and one to the destination.

 

[Rabbie]

You could populate source and destination in your query by using DLOOKUP("locations","tblLocations","tblShipping.sourcelocationID=tblLocations.locationid") and similar for destination.

 

[faust9]

Ah...

I presume you have a query where you are linking the two tables. I suspect you only have one copy of the location table in query design view. You need two. Link one to the source and one to the destination.

Yes, that's what I needed. Thanks a lot. I had a single location table related to both source and destination tables. Thank you so very much for the help.