Counting Consecutive days

[mohsinhq]

Hi,

Is it possible to count the number of consecutive days in a table?

I need a query to calculate for a particular customer the number of consecutive days thay have logged a record if its greater than 1

EG: Table layout:

CUST A RC1 05/05/05
CUST A RC2 06/05/05
CUST A RC3 07/05/05
CUST A RC4 08/05/05
CUST A RC5 15/05/05
CUST A RC6 18/05/05

Possible query results:

CUST A 4 Days First 05/05/05 Last 08/05/05

Is it possible to do something like this??

Thanks in Advance

 

[The_Doc_Man]

Perhaps a tricky little query or some VBA would help you here... I'll say it in English and let you work out the VBA or other equivalent.

For this person, you want for each individual record to "know" the number of days between the current date and maximum date that is less than the current date.

So you want DMax of the date with a criterion clause selecting the person AND a date less than the current date.

Then you want to know if the DateDiff between that DMax date and the current date equals 1.

Then you want to know if that happens several times in a row.