Querying 2 tables (XP)

Topic

New Reply

I have an [Employee Group] table (such as Business Analyst, Project Manager, System Engineer, etc.)

I have an [Employee] table which has fields for Emp_Idx, Name, Emp_Grp_Num, etc.

The two tables have a relationship [Employee Group].[Emp_Grp_Idx] to [Employee].[Emp_Grp_Num]

I also have an [Applications] table with a list of different computer applications we use.

In the Application table the fields are: AppIdx, App_Name, BA_Num, and PM_Num. BA_Num is the index number of the employee who is a BA. The same for PM_Num.

I want to create a query that lists the applications and who the BA and PM are. I can get the BA and PM numbers, but since both these numbers are coming from the same [Employee] table I am having trouble creating the right relationship and getting the query to give me both sets of names.

Any thoughts? What am I doing wrong? Do I need to set up a BA and PM subquery? If I do, how will that affect referential integrity?

As always, Thank you.

Reply | Quote

Replies

You can add the Employee table to the query twice. Access will name the second instance Employee_1, you can specify a different Alias if you like.

Join one of the instances to the Application table on Emp_Idx vs BA_Num, and the other instance on Emp_Idx vs PM_Num.

When you drag fields such as the employee name to the query grid, you will probably want to provide a meaningful column title, e.g.

BA_Name: [Name]

for the field from the instance joined to BA_Num.

Reply | Quote

How clever. I didn’t know you could add a table twice in a query.

Thanks, Hans.

Reply | Quote