Determining if data is consecutive in VB

[cori25]

I have a query which displays the employee name, month, date, exception. I need to have the query display when an employee has more then 7 consecutive days with a certain exception. Meaning the same employee name and month has to be >= 7 days which would be "Blocked" anything under this is "Intermittent". I though about and iif statement but it has to have the same employee name and month so I get thrown off.

Any ideas?

Thanks for the help!

[NeoPa]

If I suggest that you GROUP BY your [Name] and [Exception] fields then, in the WHERE clause play with Min(), Max() and Count() of the [Date] field, would that be enough to set you on the right track?

[cori25]

If I suggest that you GROUP BY your [Name] and [Exception] fields then, in the WHERE clause play with Min(), Max() and Count() of the [Date] field, would that be enough to set you on the right track?

I have already attempted the group by and min, max, count approach with no luck. I ws thinking more along the lines of having an IIF statement of some sorts, but it is a little tricky.

Thanks anyway...

I will continue trying to determine if this can be done

Thanks again
Cori

[Fiddler2]

Google "data analysis" and "Ms Access." I came across some sharp code to do this yesterday, but don't remember where. I do recall that it was under "data analysis" though.

[aprpillai]

I think you can give it a try again with the Group By, Mix(), Max() method and with more than one Query. Use the above Query and take the Count() if days also.

Create a second Query using the above as source. Create a new column, say 7thDay:MixDate + 7 which will give you the exact 7th day date after the min() date. You have the count of Days too. If the count of days are less 7 but the Max() Date is more than the 7thDay Date then the values are not consecutive.

Give it a try on these lines.

[NeoPa]

I think the last post is broadly on the right lines.

Look again and see what you can come up with.

Come back in a day or so if you really can't make it work. Hopefully by then I'll have a little more free time to be able to knock something up for you.

[cori25]

Thanks everyone for all the feedback! I actually got it to work by using an IIF statement to look st anything with more then 56 hours per month(seven 8 hour days).

Thanks again!
:)Cori

[aprpillai]

Could you please give more details on how you arrive at the result by using IIF() Function and 56 hrs of working. I am very much interested to know the technique that you have used to arrive at the result.

Thanks in advance.