Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query from 2 tables (Access2k)

    I have 2 tables, one with personal info i.e. name, works number etc, & a second one with the company courses they've attended. What I'd like to do is create a list of individuals who have not attended a specific course. The main problem, for me, is that only one field contains the codes for the courses. So in effect I've got, in the second table, Joe having a record for each course he's done, related to his personal info table.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query from 2 tables (Access2k)

    If I understand you right, what you need is an unmatched query.
    If this is the case choose queries and let the Find Unmatched Query Wizard take you thru each step to set it up.
    HTH
    Pat

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from 2 tables (Access2k)

    That sounds like half of what I'm after, the problem is, when looking for records that don't match the course criteria, I get a list showing all the other course results.
    So if Larry,Curly & Mo have each done courses A, B, & C but only Mo has done course D, I'd get Larry & as not having done the course , BUT also listing them having done A, B, & C i.e.

    Larry
    Curly
    Larry A
    Curly A
    Larry B
    Curly B
    Larry C
    Curly C

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query from 2 tables (Access2k)

    Post your query.
    And explain visually exactly what you are after.
    Pat

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from 2 tables (Access2k)

    OK here's a brief example DB. The result I'd like, is just to show who hasn't done course A
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query from 2 tables (Access2k)

    In your sample everyone has dones course A so I have changed my example to course d.
    Here is a query that does the job.
    SELECT Table1.Works_Number, Table1.Name
    FROM Table1
    WHERE (((Table1.Name) Not In
    (SELECT Table2.Name
    FROM Table2
    WHERE (((Table2.Course)="D")))));

    this is a query that uses a subquery.

    To build it in the qbe grid, first build a query that selects everyone who has done course D, change to sql view and copy the sql.
    Start a new query on table1, select the fields you want, then in the criteria line under Name type in :
    Not in (
    then paste in the sql from before, delete the ";" from the end and add and extra closing bracket.
    Regards
    John



  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from 2 tables (Access2k)

    Doh, of course it should have been D. Never mind, thanks John, that's exactly what I need.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query from 2 tables (Access2k)

    Well done John.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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