Databases
StackOverflow2013
Postsdbo
POCreating Report, repetition in report output, Ms Access SQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreating Report, repetition in report output, Ms Access SQL
    text
    copied!<pre><code>SELECT NMC.*, Exam.Final_Exam_Level FROM (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) AS NMC 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)&lt;=#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; </code></pre> <p><strong>This query shows each technician, Last Exam, Last New_Model, Last course.</strong> </p> <pre><code>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&gt;=#1/1/2010# And Jobs.Date_Occured&lt;=#12/31/2010# ORDER BY Fix; </code></pre> <p><strong>This query shows the jobs each technician has done.</strong></p> <p>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.</p> <p>Any Help?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload