Update values in table with module?

B

bourdon

I have a table that lists a serial number and a date. There ar
multiple lines for one serial number, signifying a transaction, fo
example:
SN Date
12345 08/30/2005
12345 09/25/2005
12345 10/01/2005

I need to run through the table and assign a value of 1 to the firs
transaction, a 2 for the second, etc., based on the latest dates. S
the table is sorted by serial number then date (descending), and th
earliest date for each unique serial number will be 1, the next 2 an
so on until the serial number changes then it will start from 1 for th
next serial number and sets of dates.

I don't even know where to start. I have a little knowledge abou
recordsets and macros, but am not very good at loops.
Thanks for any help/direction
 
B

Brendan Reynolds

Public Sub UpdateTranNum()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim LastSerNum As String
Dim TranNum As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT SerNum, TranDate, TranNum FROM
tblTest3 ORDER BY SerNum, TranDate")
Do Until rst.EOF
If LastSerNum <> rst.Fields("SerNum") Then
TranNum = 1
Else
TranNum = TranNum + 1
End If
rst.Edit
rst.Fields("TranNum") = TranNum
LastSerNum = rst.Fields("SerNum")
rst.Update
rst.MoveNext
Loop
rst.Close

End Sub
 
V

Vincent Johns

bourdon said:
I have a table that lists a serial number and a date. There are
multiple lines for one serial number, signifying a transaction, for
example:
SN Date
12345 08/30/2005
12345 09/25/2005
12345 10/01/2005

I need to run through the table and assign a value of 1 to the first
transaction, a 2 for the second, etc., based on the latest dates. So
the table is sorted by serial number then date (descending), and the
earliest date for each unique serial number will be 1, the next 2 and
so on until the serial number changes then it will start from 1 for the
next serial number and sets of dates.

I don't even know where to start. I have a little knowledge about
recordsets and macros, but am not very good at loops.
Thanks for any help/direction.
Click to expand...

Although Brendan Reynolds suggested a solution involving VB code, you
can do it just using SQL if you wish. Suppose your Table contains these
records:

[tblTest]

SN Date
----- ---------
12367 10/1/2005
12345 10/1/2005
12345 9/25/2005
12356 9/20/2005
12345 8/30/2005
12356 7/25/2005

The following Query:

SELECT tblTest.SN, tblTest.Date,
Count(tblTest_1.Date) AS SeqNum
FROM tblTest INNER JOIN tblTest AS tblTest_1
ON tblTest.SN = tblTest_1.SN
WHERE (((tblTest_1.Date)<=[tblTest].[Date]))
GROUP BY tblTest.SN, tblTest.Date
ORDER BY tblTest.SN, tblTest.Date;

will give you values in field [SeqNum] which I think will give you what
you want:

SN Date SeqNum
----- --------- ------
12345 8/30/2005 1
12345 9/25/2005 2
12345 10/1/2005 3
12356 7/25/2005 1
12356 9/20/2005 2
12367 10/1/2005 1

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Top