Query Looking For Same Value In 3 Consecutive Days

[alsoto]

Hello,



I have an Attendance table with the following fields:

SSN
Date
Status (for ex, A = Absent, P = Present, E = Excused, etc)

I want a query that will give me a student who has been Absent 3 (or more) consecutive days (Status "A" greater than 2 days in a row). Keep in mind that by days, I means days the class meets, so it could be that the student was absent 10/19/11, 10/21/11, and 10/24/11 if the class meets on Mons, Weds, and Fridays. It's also important to note that if the class is meeting 40 times in a semester over a 90 day period, only those 40 actual meeting dates are in the table, not all 90 days.

Can anybody suggest an easy way to do this?

Thanks,
Al

[June7]

Probably not easy.
Do you have a table that has all the dates classes will be held on?
Sounds like will require VBA with recordsets.

[orange]

What are your table structures?
What have you tried? What errors did you get?

[alsoto]

Yes, I append the 40 days (or whatever number) that the class meets to my student attendance table, along with class name, SSN, and attendance status.

I haven't gotten any errors yet because I am not sure how to approach it. Looking for a game plan.

How about if I add an autonumber field. Then in my query, I can add a criteria like:

Status = "A" AND
autonumber + 1 where status = "A" AND
autonumber + 2 where status = "A"

Does that seem like a logical direction to move in? Only problem I see is if the student was absent more than 3 times, I would get screwy results.

[orange]

If you have attendance status data for each student and each day, you might be able to use subqueries,

You would need today, previous day and next day. You would be looking for "A" (absent) in those 3 positions.

See allen Browne subquery for PriorValue at
http://allenbrowne.com/subquery-01.html#AnotherRecord

You will have to adjust the code to get the NextValue also.

Good luck.