Having a table with ID and DATE I need to have an acumulated field (ACUM)
Note: DAY is the consecutive working days for each fiscal year in our company. I'm using this table to identify the consecutive dates more easily using a related table.
DAY ID DATE ACUM
40 11 1/1/07 1
41 11 2/1/07 2
48 11 9/1/07 1
49 11 10/1/07 2
50 11 11/1/07 3
51 11 12/1/07 4
60 11 19/1/07 1
70 11 29/1/07 1
70 12 29/1/07 1
This means that consecutive dates for the same ID are counted and acumulated in the ACUM column. When DAY becomes non-consecutive for the same ID the ACUM column goes to 1 for the row.
The table has more than 400,000 records of more than 15,000 ID's. And I need to clasify each record with an the ACUM value.
I have tried with Dlookup and get to the point were the the group starts by using a null value for the first of the consecutive dates. This got me stuck. I belive there must be some query criteria to do this.
Thanks a lot in advance
Note: DAY is the consecutive working days for each fiscal year in our company. I'm using this table to identify the consecutive dates more easily using a related table.
DAY ID DATE ACUM
40 11 1/1/07 1
41 11 2/1/07 2
48 11 9/1/07 1
49 11 10/1/07 2
50 11 11/1/07 3
51 11 12/1/07 4
60 11 19/1/07 1
70 11 29/1/07 1
70 12 29/1/07 1
This means that consecutive dates for the same ID are counted and acumulated in the ACUM column. When DAY becomes non-consecutive for the same ID the ACUM column goes to 1 for the row.
The table has more than 400,000 records of more than 15,000 ID's. And I need to clasify each record with an the ACUM value.
I have tried with Dlookup and get to the point were the the group starts by using a null value for the first of the consecutive dates. This got me stuck. I belive there must be some query criteria to do this.
Thanks a lot in advance