Need to Fix a Join

  • Thread starter Thread starter MLD
  • Start date Start date
M

MLD

I have two queries that were working fine using left outer joins.
The first, ALLPASTCLASSES, still works great and in SQL (which I really
don't work in much) looks like this:

SELECT Schedule.ClassCode, Schedule.Date, Count(Attendance.EmpID) AS
CountOfEmpID, Classes.ClassName, Schedule.ID1
FROM Classes INNER JOIN (Schedule LEFT JOIN Attendance ON Schedule.Date =
Attendance.DATE) ON Classes.ClassCode = Schedule.ClassCode
GROUP BY Schedule.ClassCode, Schedule.Date, Classes.ClassName, Schedule.ID1
HAVING (((Schedule.Date)=[Enter a Date]))
ORDER BY Schedule.ClassCode, Schedule.Date;


The other one, which is giving me problems is CLASSESINRANGE. All I did was
add one extra table and now it duplicates records. Even after removing said
table, it still wont work and already has one working inner join in it.

SELECT PersonnelData.FirstName, PersonnelData.LastName, Attendance.EmpID,
Attendance.DATE, Schedule.ClassCode, Classes.ClassName, Attendance.AttCode,
PersonnelData.Phone, PersonnelData.Dept, PersonnelData.Group
FROM PersonnelData RIGHT JOIN (Classes INNER JOIN (Schedule LEFT JOIN
Attendance ON Schedule.Date = Attendance.DATE) ON Classes.ClassCode =
Schedule.ClassCode) ON PersonnelData.EmpID = Attendance.EmpID
WHERE (((Attendance.DATE) Between [Enter start date] And [Enter end date]));


All I want it a list of EXACTLY who attended which class during a given
period and be able to use it for various reports (hence the inclusion of
what some might think extraneous fields). Is there some issue with using too
many tables with joins?

Please advise? Much appreciated,
-Monica
 
I'm responding to my own post in case no one has an answer. After hours of
working on it, I got the results I wanted. Not sure if this is how some of
the MVP's might reccomend, but this is the SQL of the Query grid that works
for me.

SELECT PersonnelData.FirstName, PersonnelData.LastName, PersonnelData.Phone,
PersonnelData.Dept, PersonnelData.Group, CLassInRangeTest.ID1,
CLassInRangeTest.Date, CLassInRangeTest.ClassCode,
CLassInRangeTest.ClassName, CLassInRangeTest.Application,
Attendance.AttCode, Attendance.Late, Attendance.Comments,
Attendance.Confirmed
FROM PersonnelData RIGHT JOIN (CLassInRangeTest RIGHT JOIN Attendance ON
(CLassInRangeTest.ClassCode = Attendance.ClassCode) AND
(CLassInRangeTest.Date = Attendance.DATE)) ON PersonnelData.EmpID =
Attendance.EmpID
ORDER BY CLassInRangeTest.Date;

Hope it helps others.

-Monica
 
Back
Top