Join in query not working properly?

[upsman via AccessMonster.com]

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

 

[Allen Browne]

Without being able to see your query, I am guessing that you have some
criteria under the fields from the Attendance table. If so, the records
where there is no match will not meet those criteria, because the related
table fields will be Null.

If that's what's happening, try explicitly accepting nulls in your criteria
as well, e.g.:
Is Null Or ...

If that does not solve the problem, switch your query to SQL View (View
menu, in query design), copy the SQL statement, and paste it here.

 

[upsman via AccessMonster.com]

Here is the SQL for my query

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. I have 164
students in the Students table but only 121 of them have records in
Attendance. I'm only getting 121 records returned in my query.

Without being able to see your query, I am guessing that you have some
criteria under the fields from the Attendance table. If so, the records
where there is no match will not meet those criteria, because the related
table fields will be Null.

If that's what's happening, try explicitly accepting nulls in your criteria
as well, e.g.:
Is Null Or ...

If that does not solve the problem, switch your query to SQL View (View
menu, in query design), copy the SQL statement, and paste it here.

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

[quoted text clipped - 15 lines]

in
Attendance?

 

[Allen Browne]

Try changing the 2nd last line to:
WHERE (NewAttendance.Quarter = 1) OR (NewAttendance.Quarter Is Null)

Outer joins and null criteria are the 2 topics discussed in this article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the SQL for my query

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. I have 164
students in the Students table but only 121 of them have records in
Attendance. I'm only getting 121 records returned in my query.

Without being able to see your query, I am guessing that you have some
criteria under the fields from the Attendance table. If so, the records
where there is no match will not meet those criteria, because the related
table fields will be Null.

If that's what's happening, try explicitly accepting nulls in your
criteria
as well, e.g.:
Is Null Or ...

If that does not solve the problem, switch your query to SQL View (View
menu, in query design), copy the SQL statement, and paste it here.

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

[quoted text clipped - 15 lines]

in Attendance?