Unanswered: Query using multiple copies of same table
I have a query I am setting up and I have two tables (SHIP and ORDER) that both contain the numeric fields (OCODE and DCODE - origin code and destination code, respectively). Both of these tables also have unique key fields ( SHIP = AS_NID and ORDER = AO_NID). There is a field in the ORDER table called AO_AS_NID, which links the SHIP and ORDER tables together.
I have a LOCATION table that contains the detailed info (name, city, state, zip, etc.) for these numeric code fields (OCODE and DCODE). This table has a unique key field called AL_NID and then a field called AL_AS_NID, which should link the SHIP table to it. It also has a field called LOCCODE, which is the field that will relate to the OCODE and DCODE fields in the other tables.
My question is this: I have tried having one copy of the location table and linking on SHIP.AS_NID -> LOCATION.AL_AS_NID. My query is going to have the SHIP.OCODE, name, city, state, zip, SHIP.DCODE, name, city, state, zip, ORDER.OCODE, name, city, state, zip, and ORDER.DCODE, name, city, state, zip. The query will run here, but it only picks up the first OCODE's details and displays them for the rest of the record. I tried making 4 copies of the LOCATION table and linking like this SHIP.OCODE -> LOC1.LOCCODE, SHIP.DCODE -> LOC2.LOCCODE, ORDER.OCODE -> LOC3.LOCCODE, and ORDER.DCODE -> LOC4.LOCCODE. When I try and run that query, it bombs out.
Can anyone help me figure this out, please? I have not done a whole lot of Access DB work lately and am quite rusty. Thanks!!
I would mind seeing you DB structure for these three tables, because it is a little hard following your explaination.
However, I beleive that you problem is the link between the Location table and the Ship table using AL_AS_NID. And why is AL_NID the primary key of the Location table and when it should be LOCCODE which you have link to OCode and DCODE (which should be the only relationhsip you need between Location table and the Ship table)
You are create to many relationships that are not needed. and it is making it hard to Access to determine what you really want.