Need two relationships to a single table 1

[Smacky22]

I have a situation were I have a main table and several additional tables that have relationships based on an ID using an auto-number. Anywhere I have a word followed by ID is a place where a relatiohship exists between two tables using an auto-number.

I have one particular table (tblAirline) that needs to be referenced twice. The table needs the exact same structure and content, but it has two different meanings in the main table. How can I make this work, or how do I need to redesign this portion of my database?

tblMain:
ID
AcctMngrID
RegionID
PurchaserID
OperatorID
FY01$
FY02$
FY03$
Comments

tblAcctMngr:
AcctMngrID
AcctMngrName

tblRegion:
RegionID
RegionName

tblAirline:
AirlineID
AirlineName

Where AirlineID needs to have a one-to-many relationship with both the PurchaserID and the OperatorID in the table tblMain.

 

[jebry]

Hi!

Just go into your relationship window and add tblAirline twice. Connect one to PurchaserID and the other to OperatorID. That will give a one-to-many relationship between AirlineID and both of the ID's from tblMain.

hth
Jeff Bridgham

 

[Smacky22]

I've tried this before myself. It will allow me to create the relationship, no problem. However, If I try to run a query that includes airline, It will respond with zero records. I need to be able to run queries to 'see' the airline based on the PurchaserID, OperatorID, or both.

 

[jebry]

Hi!

When you create the query you want, go into SQL view and you will see where Access has placed an AND between the connection criteria of the inner join in the From clause. Replace the AND with an OR and you should get results. To get results from both joins, you may need to do two queries and then do a union query with them.

hth
Jeff Bridgham
P.S. After you change that AND to an OR, you will no longer be able to use the QBE grid because Access says it has no way of representing that type of Inner Join.