Count consecutive records with multiple criteria

[ng1751]

Hello all,

I am trying to construct a query that looks for an employee with the code "AVAL" for 5 consecutive 15 min time intervals. My columns appear as:

State_Time |Employee| Code| Nominal_Date|
1/1/2012 7:45:00 AM| Employee1| OFF| 1/1/2012
1/1/2012 8:00:00 AM| Employee1| AVAL| 1/1/2012
1/1/2012 8:15:00 AM| Employee1| NOAVAL| 1/1/2012
1/1/2012 8:30:00 AM| Employee1| AVAL| 1/1/2012
1/1/2012 9:00:00 AM| Employee1| NOAVAL| 1/1/2012

The database usually consists of 30 days worth of dates, the 15 min intervals go until 10:00:00 PM, and there are roughly 100 employees at any given time. The desired result is to return the dates where this is true for each interval. i.e. if Employee1 shows AVAL for 7:45:00 AM, 8:00:00 AM, 8:15:00 AM, 8:30:00 AM, 8:45:00 AM, 9:00:00 AM on 1/5/2012 then they are able to take an 8AM appointment on 1/5/2012 with prep time from 7:45-8AM and query shows as:

Interval Date Employee


7:45:00 AM 1/5/2012 Employee1

Result must be done by query and no VBA.
Thanks in advance for your help and let me know if I need to clarify further!

[help_me_with_access]

Result must be done by query and no VBA.

i don't think you can do it, sir. even with stacked queries. good luck though!

[ng1751]

I believe this post is trying to do a similar task with min/max times and comparing the previous records to each other. I am having trouble tailoring it to my situation. Any thoughts?

https://www.accessforums.net/queries...data-5680.html