Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querying 2 tables (XP)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Querying 2 tables (XP)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying 2 tables (XP)

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

    Thanks, Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •