Increment field based on another field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 18 Apr 2008 10:12 GMT

Hi

I have a database that comes in from our website. The first fields are TYPE,
DATE and TRAN. Each time the date alters, I would like to restart the TRAN
at 1, and increment it until the date changes - then start at 1 again.
As a sample, the info I have is:

TYPE DATE TRAN
AD 160408 34
BA 34
BA 34
CC 34
AD 160408 35
BA 35
CC 35
AD 170408 36
BA 36
BA 36
CC 36

What I would like is this:

TYPE DATE TRAN
AD 160408 1
BA 1
BA 1
CC 1
AD 160408 2
BA 2
CC 2
AD 170408 1
BA 1
BA 1
CC 1

At the moment, I have 23000 lines, with over 5000 transactions, so it can't
be done manually.

Thanks for reading this. If anyone could help - or provide a pointer in the
right direction - I would really appreciate it.

Andy.

Reply to this Message

Damon Heron - 19 Apr 2008 01:33 GMT

From your example, it looks like the only time a date is entered is when the
Type is "AD". If so, then create a form with a
command button and add this code to the click event. You might want to make
a copy of your table first!!!
My table has three fields: Type, MyDate, and Tran. (Date is one of those
reserved words with Access)

Private Sub Command0_Click()
Dim DB As DAO.Database
Dim Holddate As Date
Dim ct As Long
Dim RS As Recordset
Set DB = CurrentDb
Set RS = DB.OpenRecordset("table1") ' enter your table name
RS.MoveFirst
Holddate = RS!myDate
ct = 0
Do While Not RS.EOF
RS.Edit
If RS!Type = "AD" Then
If RS!myDate = Holddate Then
RS!tran = ct + 1
ct = RS!tran
Else
RS!tran = 1
Holddate = RS!myDate
ct = 1
End If
Else
RS!tran = ct
End If
RS.Update
RS.MoveNext
Loop
RS.Close
End Sub

HTH
Damon

> Hi
>
[quoted text clipped - 39 lines]
>
> Andy.

Reply to this Message