Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Question Unanswered: Calling upon two tables at once

    I have a Table "BuddyList". It contains 3 fields ID (PK), Buddy1, Buddy2. Each buddy field is a is a lookup field in Table "Employees". Buddy 1 & 2 gets the first and last names of the employees. I want to throw that into a report but on the report I need to include each person's position.
    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Uh... use an inner join?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    I don't know SQL really

  4. #4
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unhappy Fusteration

    So, this is not going as though I thought it would. Even though the field "Buddy1" sees both fields "LastName, FirstName", but when I select the name I need it only shows FirstName. Ideally I want
    Code:
    Buddy1                        Buddy2
    *****************************************
    [LastName] , [FirstName]     [LastName] , [FirstName]
            [Positon]                       [Position]
    ---------------------------------------------
    (Next set of buddies)
    
    
    ********************************
    Any help will be great
    Last edited by rguy84; 07-06-04 at 19:33.

  5. #5
    Join Date
    Jun 2004
    Posts
    16
    You need an inner join. Your Buddy table needs to contain the employee id from your employee table and not the text of their names.

    SELECT TBLBuddy.bUDDYONE, TBLBuddy.bUDDYTWO, tblEmployee.empname, tblEmployee_1.empname
    FROM (TBLBuddy INNER JOIN tblEmployee ON TBLBuddy.bUDDYONE = tblEmployee.EmpID) INNER JOIN tblEmployee AS tblEmployee_1 ON TBLBuddy.bUDDYTWO = tblEmployee_1.EmpID;


    the above assumes that you have a field named "empname" in your employee table.

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by David21495
    You need an inner join. Your Buddy table needs to contain the employee id from your employee table and not the text of their names.
    If I make Buddy1, look up EmployeeID, LastName, FirstName, it should work?

    Quote Originally Posted by David21495
    SELECT TBLBuddy.bUDDYONE, TBLBuddy.bUDDYTWO, tblEmployee.empname, tblEmployee_1.empname
    FROM (TBLBuddy INNER JOIN tblEmployee ON TBLBuddy.bUDDYONE = tblEmployee.EmpID) INNER JOIN tblEmployee AS tblEmployee_1 ON TBLBuddy.bUDDYTWO = tblEmployee_1.EmpID;

    the above assumes that you have a field named "empname" in your employee table.
    I just have two seperate fields in the employee table for First and Last.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by rguy84
    If I make Buddy1, look up EmployeeID, LastName, FirstName, it should work?


    I just have two seperate fields in the employee table for First and Last.
    I think you're missing the entire "relational database" concept here.

    Post all the tables you currently have, the fields in the tables, and how you see them related. I am willing to wager you are making things far more difficult on yourself through some odd logic...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    How about just attaching my DB?

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I can't work on other databases from this location... sorry...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    ok, I will list everything then....
    Code:
    Tables:
    Employee
    EmployeesID (PK)
    LastName
    FirstName
    Position
    Team <---Lookup--- Team
                                    Team (PK)
    Information
    InformationID (pk)
    BadgeNum
    BadgeExp
    ParkingExp
    Tier
    CubeNum
    ComputerJack
    PhoneJack
    WorkPhone
    HomePhone
    21Inch
    DualScreen
    EmployeesID
    
    BuddyList
    BuddyID(PK)
    Buddy1 <---look up from Employees 
    Buddy2 <---look up from Employees
    Relationships
    Employees and BuddyList: EmployeesID to Buddy1 One-to-Many
    Employees and Information: EmployeesID to EmployeesID One-to-Many--Inforce Ref Integrity (checked) Cascade Update(checked) Cascade Delete (checked)
    Team and Employees: Team(team) to Team(employees) One-to-Many

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well... I don't see why you need all three of those tables... looks to me like it can be all one table. I don't know what your team table looks like, so I just assume there's a TEamID there somewhere.

    employee:
    ---------
    EmployeesID (PK)
    TeamID (FK)
    LastName
    FirstName
    Position
    BadgeNum
    BadgeExp
    ParkingExp
    Tier
    CubeNum
    etc
    etc
    Buddy1_id
    Buddy2_id
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    So do I have to tell Access Team is a FK or just a lookup field? If it does need to know FK how do they know which is which. I am not sure how the Report for the Buddies will go.. but I will try it

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Get rid of this "lookup field" concept.

    You can use subselects to pull buddy information from the employee table if you'd like. If there is a slight chance of there ever being more then two buddies for a given employee, you should then employ the second table with a format:

    tblBuddies
    ----------
    buddy_id (PK)
    employeesID (FK)
    buddy_employeesID (FK)

    Then you could slap a subform/report with the recordsource:

    SELECT tblBuddies.employeesID, firstName, lastName
    FROM tblBuddies INNER JOIN Employees ON tblBuddies.buddy_employeesID=Employees.employeesID

    The subform would reference the tblBuddies.employeesID and give you a relevant list of buddies for a given employee.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    I am going the safe way, just in case oid odd employees...You call buddy_employeesID a FK, but where is it?

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's another reference to EmployeesID

    You have the field EmployeesID, which is the key to reference for master detail relationships, and buddy_employeesID, which is ANOTHER reference to EmployeesID to be used for retrieving information about all the buddies for a given employee.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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