Joining 3 tables (1 Viewer)

[User Name:]

Hi everyone,

I've got a query that uses a key from one table to pull back records from another.

My question is...

On the table with the key there is another field that I want to use to pull back data from the same table from above.

A bit more info...

Table one holds colleagues details
Table two holds records

Within table one there are 2 different fields that hold different ID's for the same colleague, currently I run two different queries to pull all data - I want to know if I can simply run one query that will return every thing?

I hope this makes sense.

Ant help is appreciated.
Schedule an import or export operation


Thanks,

UN

 

[pbaldy]

If you mean there are two fields with different colleagues, you'd add the colleague table to the query grid twice. Access will alias the second copy. Join one of the fields to the table, the other to the alias.

 

[Pat Hartman]

When you need to join to the same table twice in a single query (or represent this relationship in the relationship window) all you need to do is to add the "lookup" table twice to the QBE. The second instance will be automatically suffixed with _1 so you can identify it. Then draw a join line from field1 to tblLookup and a second line from field2 to tblLookup_1. When you select columns from these tables, be sure to Alias them so you don't get names prefixed by the tablename. To add an Alias, type MyAliasName: in the field grid in front of the bound name.

 

[User Name:]

Hi,

Thanks for your reply but I'm still showing limited results. So I've loaded the colleage tbl twice (one is showing _1), I've linked both colleagues tbls to the record tbl, the from join from one ID for the colleage and then the second with the other ID for the colleague.

These two joins coming from the two colleage tbls are linked to same field in the record tbl (not sure if that makes a difference).

If I simply run my original qry (with the first join from 1 ID to the record) I return 1047 records, then if I run the second query (joined with the other ID) I return 17 records.

I am wanting to run one qry to return the whole amount...HELP?

 

[User Name:]

Can anyone help with this? :banghead:

 

[pbaldy]

You realize we're not in your time zone? Can you post the db here?

 

[nanscombe]

I'm going to guess that the query is only showing data from the record where there are matches in both colleage (sic) tables and you want to see all the entries in table 'record'.

If you look at the attached database I have made an educated guess at what you are trying to do.

Tables:
colleage - People details
record - some type of data with the IDs of two people in it.

Queries:
qryTeamList1 - A result query with default joins between the tables (just shows some entries from the record table)
qryTeamList2 - Shows all entries from the record table and whatever data is available from each of the colleage tables.

To change the type of join between the tables:
Double-click on the connecting line between two tables
Choose the option which starts "Include ALL records from 'record' ..."
Click OK

The line should now show an arrow pointing towards the colleage table.

Repeat this for both connecting lines.

When you run the query now it show all of the entries from the table record.

 

[User Name:]

I'd like to say thank you so much - this is exactly what I was looking for.

 

[nanscombe]

I thought it might be. You're welcome.

 

[User Name:]

Just one more question - What if the field within the record DB is used to link both colleague tbls? When I have treid to apply the above to my DB's it is resulting in everything from one, When I try to apply the same type of join to both colleague tbls, I get an error asking me to perform a join in another qry.

 

[MSAccessRookie]

Just one more question - What if the field within the record DB is used to link both colleague tbls? When I have treid to apply the above to my DB's it is resulting in everything from one, When I try to apply the same type of join to both colleague tbls, I get an error asking me to perform a join in another qry.

If you need to have the results from BOTH sides, then a UNION Query is a possible solution. An overview of a possible solution is below, but you should also look Up UNION Queries for additional details.

• The First part of the UNION Query would be the same as the Query that you have now (All records from Table 1 and only matching Records from Table 2).
• The second part of the UNION Query would be the reverse of the Query that you have now (All records from Table 2 and only matching Records from Table 1).

A standard UNION Query will eliminate any duplicates for you. Give it a try and get back with additional questions.

 

[nanscombe]

In the previous query, qryTeamList2, table record was used as the master table.

In the attached database I have added another query qryTeamList3_LeaderAndMembers to illustrate how you would alter the query if you wanted a team Leader and members type list.

So for each team leader you see who works for them.

Depending which table you want to see all the records from, shown in bold, the joins could flow either:
A -> B -> C
A <- B -> C
A <- B <- C