Take the 2-minute tour ×
SELECT NMC.*, Exam.Final_Exam_Level
    (SELECT Technicians.Technician_ID AS Technician_ID, 
    Technicians.First_Name AS First_Name, 
    Technicians.Surname AS Surname, 
    MAX(New_Models.Date_Issued) AS Last_Course_Date, 
    MAX(New_Models.Issue) AS Last_Issue, 
    MAX(New_Models.Model_ID) AS Last_Model_ID, 
    Technicians.Course_Level AS No_Training_Courses 
    FROM New_Models, New_Models_Allocation, Technicians 
    WHERE New_Models.Model_ID=New_models_Allocation.Model_ID 
    And Technicians.Technician_ID=New_Models_Allocation.Technician_ID 
    GROUP BY Technicians.Technician_ID, Technicians.Course_Level, First_Name, Surname 
    ORDER BY MAX(New_Models.Model_ID) DESC) 
INNER JOIN (SELECT Technicians.Technician_ID, COUNT(*) AS Final_Exam_Level 
    FROM Technicians, Exams, Exam_Allocation 
    WHERE (Technicians.Technician_ID)=Exam_Allocation.Technician_ID 
    And ((Exams.Exam_ID)=Exam_Allocation.Exam_ID) 
    And (Exams.Date_Taken)<=#12/31/2010# 
    GROUP BY Technicians.Technician_ID, Technicians.Course_Level 
    ORDER BY Technicians.Technician_ID)  
AS Exam ON Exam.Technician_ID=NMC.Technician_ID;

This query shows each technician, Last Exam, Last New_Model, Last course.

SELECT Technicians.Technician_ID, Jobs.Job_ID, Jobs.Date_Occured, Fix
FROM Technicians, Jobs, Tech_Allocation, Recovery
WHERE Technicians.Technician_ID=Tech_Allocation.Technician_ID 
And Jobs.Job_ID=Tech_Allocation.Job_ID 
And Jobs.Job_ID=Recovery.Job_ID 
And Jobs.Date_Occured>=#1/1/2010# 
And Jobs.Date_Occured<=#12/31/2010#

This query shows the jobs each technician has done.

However, when creating a report in Ms Access, the jobs are repeated. Hence, instead of a technician having done 3 jobs, it shows 12 for example. Although when running the second query itself, results aren't repeated.

Any Help?

share|improve this question
(This isn't directly related to your question, but it could still be a problem). I see that to capture all data for 2010, you used '<#12/31/2010#'. However, if these datefields also store the time portion, then you're excluding all events that occurred on 12/31/2010 after midnight. I recommend using <#01/01/2011# instead. Make sense? –  PowerUser May 3 '11 at 12:46
add comment

1 Answer

For obvious reasons, I don't usually read other people's SQL queries, but your example was very well formatted. Is this the problem?

INNER JOIN (SELECT Technicians.Technician_ID, COUNT(*) AS Final_Exam_Level
    GROUP BY Technicians.Technician_ID, Technicians.Course_Level 

These 2 lines are from the 2nd subquery of your first query. You have 1 index field (Technician_ID), but 2 grouping fields (Technician_ID and Course_Level). This would produce results like:

 Technician_ID Final_Exam_Level
 Bob           5
 Bob           4
 Nadine        5

I recommend either removing Course_Level from the Group By or adding it to the Select row.

share|improve this answer
add comment

Your Answer


