LastOf in query

  • Thread starter Thread starter maggie
  • Start date Start date
M

maggie

I have the following query and am having trouble with the
LastOf section. If a student is first marked as Absent,
then we change them to tardy, the LastOf info doesn't
change. It will still say Absent. I am getting the Max
date that I want, but not the LastOf that I want. In
other words, I want the latest date and whether they are
absent or tardy on that date. How can I change this to
reflect what I want? Thanks!

SELECT DISTINCTROW [Bus Information].[Bus Number], Max
([Absent/Tardy Table].[Date Absent/Tardy]) AS [MaxOfDate
Absent/Tardy], Last([Absent/Tardy Table].[Absent or
Tardy?]) AS [LastOfAbsent or Tardy?], students.[Student
Last Name], students.[Student First Name],
students.Teacher, students.[Student Number], [Bus
Information].Monday, [Bus Information].Tuesday, [Bus
Information].Wednesday, [Bus Information].Thursday, [Bus
Information].Friday, [Bus Information].Date, [Bus
Information].Daily
FROM (students INNER JOIN [Bus Information] ON students.
[Student Number] = [Bus Information].[Student Number])
LEFT JOIN [Absent/Tardy Table] ON students.[Student
Number] = [Absent/Tardy Table].[Student Number]
GROUP BY [Bus Information].[Bus Number], students.
[Student Last Name], students.[Student First Name],
students.Teacher, students.[Student Number], [Bus
Information].Monday, [Bus Information].Tuesday, [Bus
Information].Wednesday, [Bus Information].Thursday, [Bus
Information].Friday, [Bus Information].Date, [Bus
Information].Daily, students.Status
HAVING ((([Bus Information].[Bus Number])>"0") AND
((students.Status)="Current")) OR ((([Bus Information].
[Bus Number])>"0") AND ((Max([Absent/Tardy Table].[Date
Absent/Tardy])) Is Null) AND ((students.Status)
="Current"))
ORDER BY students.[Student Last Name], students.[Student
First Name];
 
I think you can try splitting this large query into two. In the first
one, select the last date and not the tardy/absent status. In the
second, inner join the student, bus and date and select the status. This
should do the trick regardless of how you add or change records.
As it is, it is hard to tell whether the LAST record is the one you
updated, or is your database truly transactional and you never update,
only add, information. In the latter case your query should work. If on
the other hand you do update the existing information, then "last"
record is not necesserily the one with the greatest date.

Pavel
 
Back
Top