Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    228

    Unanswered: query across two tables

    I'm having difficulty grasping how to develop a query.

    I have one table for assigments, AssTable. Say there are 5 types of assignments. Each of the five assignments has a persons name stored with it.

    I have another table with employee information, EITable.

    I would like to query for Assignment number, due date and responsible person (for all 5 assignments) AND the responsible person's department from the EITable.

    I think I'm making it more difficult than it should be. Since there are 5 types of assignments for each record, I'm having difficult returning a 5 corresponding departments.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Dude, AssTable?


    sorry... moving on...

    You have to use subselects. Basically you want to join the table five different ways, once for each assignment.

    The other option is to alter your schema to include assignment details linked to assignment types.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Sep 2003
    Posts
    228
    It's really not the name of the table... I just threw it in for giggles.

    I am not familiar with subselects, could you provide some more info and maybe some syntax?
    Last edited by ottomatic; 09-23-04 at 13:32.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you do not need subselects, but a join from AssTable to EIT 5 times

    pretty hard to tell without layouts and some sample rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    AssTable is hilarious.

    It is hard to tell, but, you might need a third table:

    tblEmployee
    tblAssignment
    tblEmployeeAssignment

    The third table will have two foreign keys:
    EmployeeID
    AssignmentID

    With this setup you can query which employees have which assignments. You might add a 'Date Assigned' field as well as a 'Due Date' field to the third table so you know when the employee was given the assignment.

    Whadda u think?
    Last edited by wazz; 09-23-04 at 13:13. Reason: accidentally clicked 'post'

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by ottomatic
    It's really not the name of the table... I just threw it in for giggles.

    I am not familiar with subselects, could you provide some more info and maybe some syntax?
    Basically a subselect is a self contained select query that returns a single record. You put this query in paranthesis and can use it as a field value:

    SELECT (SELECT name FROM emp WHERE emp.emp_id = t1.cat1emp_id), (SELECT name FROM emp WHERE emp.emp_id = t1.cat2emp_id)
    FROM t1
    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
  •