Adding a new table to a query

[robbeh]

For argument sake these are the 3 tables.

Table1 - holds every person in the database
Unique ID - PK
FName
LName

Table2 - holds people that have special permissions
Relationship ID - PK
Permissions1
Permissions2

Table3 - attributes only for people in table2
Relationship ID - PK
Colour1
Colour2

I have inherited this database where right now Table1 and Table2 get connected via a query. How do I connect Table3? Since Table1/2 have data added, I try and connect table 3 the query comes up blank since no entries have been made in Table3.

I could add Colour1 and Colour2 to Table2 as a field but the issue is Table3 information gets used in multiple different queries.

Basically, I want to add the Colour Attributes to anyone in Table2 but not store it in a separate table.

Thanks,


Robb

[June7]

Don't use INNER JOIN. Use RIGHT or LEFT.

Advise not to use exact same field name in different tables. Also, no spaces or special characters/punctuation (underscore is exception).

What are the FK fields? Did you mean RelationshipID is FK?

[robbeh]

Thanks June,

I try and follow the no space rule but kept Relationship ID as I thought they might have to be the same.



Here is how the first working relationship is setup and works. The additional one is the same set up. I have tried the other two options and one throws an error saying I should do two queries and the other works but doesn't include the new table3.

The Relationship IDs are all PK and the Unique ID is PK in the main Table1.

I really need to work on my Access skills as I feel this should be something so easy.

[robbeh]

Oh man, I take it all back. Please disregard.

The issue was I needed to use the 3rd option in the relationship (All Doc Data Table...Table1...realized I didn't block this out..ah well ) but I totally forgot to then add the tblLaserCert (Table3) to the query itself...hence why no data was showing.

Thanks June for your help!