Consecutive group dates acumulated

[carfj1]

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

 

[dhookom]

Is this a one time process to fill a field in a table? If so, I would probably create some code to loop through all records and update the ACUM field.

I'm not sure that any solution without storing the value, would have horrible performance.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[carfj1]

I'm not a visual basic expert.
Can you recomend some loop?

Its actually a one time only process, but is continuously required. Every week for arround 200 records

So the idea of updating the acum field is a good option,

Thanks a loooot for your response,
Carlos

 

[dhookom]

Try some DAO code like the following. This requires you to set a reference to the MS DAO object library. The code requires that you enter a starting date so you can start anywhere in the data records.

Other comments:
- you didn't provide a table name
- I don't care for field names like Date and Day since they are function names

Public Sub UpdateACUM(datStartDate As Date)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim datPrevious As Date
    Dim intCount As Integer
    Dim intID As Integer
    Set db = CurrentDb
    strSQL = "SELECT ID, Date, ACUM " & _
        "FROM carfj1 " & _
        "WHERE Date >= #" & _
            Format(datStartDate, "yyyy/mm/dd") & "# " & _
        "ORDER BY ID, Date"
    Set rs = db.OpenRecordset(strSQL)
    With rs
        Do Until .EOF
            
            If DateAdd("d", 1, datPrevious) = ![Date] _
                    And ![id] = intID Then
                intCount = intCount + 1
             Else
                intCount = 1
                intID = ![id]
            End If
            .Edit
                ![ACUM] = intCount
            .Update
            datPrevious = ![Date]
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[carfj1]

I'm sorry, I have tried to change all the references in the VB references and I cant get to take out the red color of lines like: Dim db As DAO.Database

Can you tell me which is the necesary DAO refence I need to have?

Thanks a lot,
Carlos

 

[PHV]

Microsoft DAO 3.x Object Library

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[carfj1]

Thanks,
I have DAO 3.6 installed,
Do you have any other options,

Is it possible that my Access is not fully installed?
The help file for VB is not working.

 

[dhookom]

DAO is installed on all Windows PCs. Please check all of your references in VBA to see if DAO is checked and none are "MISSING".

Also, can you reply back with your actual code you are using?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]