Selecting distinct records with INNER JOIN

[1mroberts]

I built a student attendance tracking dB in version 2007 and am having trouble figuring out the perfect attendance portion. I want to return each student that does not have an unexcused or excused absence or has been remanded to alternative school. With the following code, I am getting more records than students so it seems there is an issue with the DISTINCT command as well as the WHERE clause. I can either select a quarter (nine weeks) from a drop down box, which automatically assigns dates to the from and to date fields, or I can manually put in dates.

SELECT DISTINCT Student.LastName, Student.FirstName, Student.MiddleName, Student.ClassPeriod, Attendance.AttendanceStatus FROM Student INNER JOIN Attendance ON Student.StudentID=Attendance.StudentID WHERE (((Attendance.AttendanceStatus)<>"ABSENT - EXCUSED" And (Attendance.AttendanceStatus)<>"ABSENT - UNEXCUSED" And (Attendance.AttendanceStatus)<>"REMANDED TO ALT. SCHOOL") And ((Attendance.AttendanceDate)>=forms!PerfectAttendance!FromDate.Value And (Attendance.AttendanceDate)<=forms!PerfectAttendance!ToDate.Value)) ORDER BY Student.ClassPeriod;

[Luuk]

In your current selection, if a student has more than 1 'AttendanceStatus', he/she will show up more than once.

"I am getting more records than students so"
Are you getting duplicate students?

To check this you can expand the order by to:

ORDER BY Student.ClassPerdid, Student.LastName, Student.FirtName, Student.Middlename

or, if you have a studentnumber

ORDER BY Student.ClassPerdid, Student.StudentNumber

[1mroberts]

Thanks Luuk. I analyzed the data and found what was in common for the 4 duplicate students showing up. I have 68 students and 72 records being returned on report. They each had a tardy in their attendance records. Can I assume that since the tardies aren't in the WHERE clause, the DISTINCT is not being applied to records with tardies? I can actually live with duplicate records being returned if they have a tardy because they should be on perfect attendance report. Only absences or alternative school excludes a student.

I also deleted attendance records so that I only had nine students that had any absences and should not show up in the perfect attendance report. All 9 students are showing up in the perfect attendance report and I have verified that each has at least one absence. The date portion of the WHERE clause is working because if I select a quarter outside of any records, no records are returned. This is what I don't understand unless it has something to do with how the WHERE criteria is being grouped.

I tried something else as well. I took out the DISTINCT and it returned every attendance record except those that had an attendance status of an absence (no records have Alt. School) so it seems like the WHERE clause is working correctly. I'm thoroughly confused after doing this.

I'd truly appreciate any help. If you can think of an easier way to do this, I'm all for it. Even a count of absences would work so that I could quickly see anything above zero did not have perfect attendance. Problem is, I have no idea how to do that.

[jimatqsi]

Distinct means distinct. If it's not distinct it's a separate record. Look at the results returned by your query. Compare each row returned to every other row. You'll see what makes similar rows different from each other and then you'll understand why you have gotten multiple, distinct rows when you were expecting just one.

The solution may well be to return fewer columns or maybe it has to do with how you are grouping/totaling/sorting.

Jim

[1mroberts]

Thanks Jim. I remembered that I had a copy of the dB when I first built it and it only has limited attendance records in it. It is the same code I posted earlier that is not working. I even went in and changed a PRESENT status to ABSENT - EXCUSED or ABSENT - UNEXCUSED and it omits that student from the report like it should.
To keep testing, I went and added some attendance records to students in other periods. Worked the first time but when I went in and changed a present to ABSENT - UNEXCUSED or ABSENT - EXCUSED, it showed that student as having perfect attendance. I even closed out ACCESS and went back in but no luck. This makes me think it is ACCESS rather than the code if it works some of the time. Any ideas??

Thanks

[1mroberts]

Sorry Jim. I had a completely wrong understanding of the DISTINCT command. As soon as I added an attendance record with a status different from other records it shows that record. That makes it no longer distinct. I get it now. I'll see if I can fix with that in mind.