Join in query not working properly?

[upsman]

I have a query where I'm selecting fields from two tables - Students and Attendance. I have selected option 2 in my Join Properties, i.e "Include ALL records from Students and only those from Attendance where the joined fields are equal". The tables are joined by StudentID. Either I'm misinterpreting what the join is supposed to be doing or it's not working.
I need to see every record in Students regardless of whether or not they have a record in Attendance. I need to calculate absences and tardies. If there's a matching record in Attendance, I subtract the date absent from the total days. If there's no matching record in Attendance, I still need to calculate total days and show it. As it is now, if there's no matching record in Attendance, it doesn't do anything. How can I get it to do what the join says, "Show ALL records in Students as well as those that match in Attendance?

Thanks

 

[Rickster57]

By "doesn't do anything" I take it you mean there is no calculation results?
If so, try an expression in your query like this:
expr: IIf(IsNull([daysmissed]),0,[daysmissed]).
This will give you a value to work with.

If the student isn't showing up even though there is a record for him/her then there is something wrong in the relationship.

Hope this helps.

 

[upsman]

Here is my SQL if it helps:

SELECT DISTINCTROW Students.StudentID, Students.AdminDate, NewAttendance.Year, CalcQtr1Days([AdminDate]) AS DaysEnrolled1, [DaysEnrolled1]-(NZ([DaysAbsent1])) AS DaysPresent1, Sum(NZ([NewAttendance].[Absent]))*-1 AS DaysAbsent1, Sum(Abs([NewAttendance].[Excused])) AS NumExcused1, Sum([NewAttendance].[Absent]-[NewAttendance].[Excused])*-1 AS NumUnexcused1, Sum(Abs([NewAttendance].[Tardy])) AS DaysTardy1
FROM Students LEFT JOIN NewAttendance ON Students.StudentID = NewAttendance.StudentID
WHERE (((NewAttendance.Quarter)=1))
GROUP BY Students.StudentID, Students.AdminDate, NewAttendance.Year;

If there are no records in Attendance, I still need to do CalcQtr1Days to get DaysEnrolled1 and I still need to calculate DaysPresent1. There are 164 students in my Students table by only 121 of them have records in Attendance. My query is only returning 121 records but I need 164 returned.