Results 1 to 7 of 7
  1. #1
    anemoskkk is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    12

    Exclamation Joining more than 2 tables

    Hi




    I have to submit a query that details how a particular technician in the[ last yearhas got on with training and information about their [last 'New Model' course.


    In order to differentiate from normal courses and 'new model' courses, the primary key 'CourseID' has an 'MC' for model course and 'QC' for qualification course.



    Any suggestions??
    Last edited by anemoskkk; 04-18-2011 at 03:28 AM.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You should not use special characters (#) in your field names.

    Have you considered putting another field in the Course table? Something like CourseType with values MC or QC.

    A general model for multi table joins

    Select A.*,B.*,C.*
    FROM A,B,C
    Where
    A.linkFld = B.linkFld AND
    B.linkFld = C.linkFld
    + other criteria

    There data models here that may be useful.
    http://www.databaseanswers.org/data_...s_physical.htm

  3. #3
    anemoskkk is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    12

    Exclamation

    Thanks a lot for the help. I added CourseType that definitely helps.

    My problem is now how I can tell Accesss to only show the last 'NEW MODEL' course. I know how to do it when it comes to a range of dates (eg. last year training etc.) but not how to show the latest one.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    So you want the latest new model (MC) regardless of Technician.

    Which table has CourseType and CourseDate?
    Wouldn't the latest date be the "biggest" (Maximum date) ?
    So to select the latest MC CourseType, would involve

    CourseType = "MC" AND CourseDate = Max(CourseDate)

    That's what the logic is. However, you will have to use a subquery.
    Look up subQuery and see if you can get it to work.

    Post back if you have an issue.
    Good luck.

  5. #5
    anemoskkk is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    12
    Thanks a lot for the help. New models course works now.

    But I have another problem.

    Technicians can take an exam even if they havent taken the qualificationcourse. In my current query, a particular technician is shown with exams and courses. But that makes exams duplicated as shown below:

    TechnicianID Name Surname Result ExamLevel ExamDate CourseLevel

    0001 George Vern 96 2 12022011 2
    0001 George Vern 96 2 12022011 3
    0001 George Vern 87 3 15032011 3
    0001 George Vern 87 3 16022011 4


    This is what I want ideally:

    TechnicianID Name Surname Result ExamLevel ExamDate CourseLevel

    0001 George Vern 96 2 12022011 2
    0001 George Vern null null null 3
    0001 George Vern 87 3 15032011 3
    0001 George Vern null null null 4


    SELECT ExamResult.TechnicianID, Name, Surname, Result, ExamLevel, ExamDate, CourseLevel, CourseDate
    FROM Technician, ExamResult, Exam, Course, QualificationCourse
    WHERE Exam.ExamID = ExamResult.ExamID
    AND ExamResult.TechnicianID = Technician.TechnicianID
    AND Technician.TechnicianID = QualificationCourse.TechnicianID
    AND QualificationCourse.CourseID = Course.CourseID
    And ExamResult.TechnicianID='0001'
    AND CourseType = 'Qualification';

    This is my current query which creates duplicates.
    How could it be improved?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What are the relationships for the tables in your database?

    Can you attach a jpg of the relationships?

  7. #7
    anemoskkk is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2011
    Posts
    12
    Hey

    Here is a picture of the relationships. Hope that helps.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 03:43 PM
  2. joining on two userid's
    By svcghost in forum Queries
    Replies: 9
    Last Post: 09-30-2010, 06:48 PM
  3. Joining or Combining Two tables
    By escuro19 in forum Queries
    Replies: 2
    Last Post: 02-16-2010, 03:55 PM
  4. Joining Unioins output to tables? Help Needed
    By techexpressinc in forum Queries
    Replies: 4
    Last Post: 06-24-2009, 05:05 PM
  5. Joining tables two tables and another table that is not
    By DevintheDude in forum Database Design
    Replies: 0
    Last Post: 09-12-2007, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums