Recorn Number = current number of record (1 Viewer)

[BukHix]

Record Number = current number of record

I have a table that will be rebuilt every week with new data (batch table). I need to run a query on that table that will start at the first record giving a new field (the field name is BatchSeq) the value of 1 and then incrementing it one value for each record.

So if this week I have 100 records the BatchSeq for the first record would be 1 and the last would be 100.

Any ideas of how I can do this?

 

[Friday]

Buk:

will all the data in the table be overwritten? Can the table start out with the new field in it? (or does it have to be created each week?)

 

[BukHix]

Yes it will be overwritten every week. I figured I could do the new field in the query like this:

RecordNumb: [SQL command to get row number]

 

[Friday]

This code is probably crap, but it does put 1 in the first nufield when ran, the loop isn't working, but that is one area I am stupid in.

With rstRace
' Populate Recordset.
.MoveLast
.MoveFirst
intCount = .AbsolutePosition + 1

For X = 1 To strCount
With rstRace
.MoveFirst
.Edit
!nufield = intCount
.Update
.MoveNext
Next X
End With

dbsTest.Close

 

[BukHix]

Is there anyway to do it in a query? This way I can do it dynamically and add the field to the table on the fly and as I may need it.

 

[Friday]

I'm not the one to answer that, I'm not that strong on SQL. I am just beginning to use query defs, and like them a lot, but you are using VBA with query defs. I'm at home right now, but tommorow (yes I get to work the holiday) when I'm at the office I'll look at this more...

 

[Mile-O]

Why do you need to rebuld the table?

 

[BukHix]

Mile-O-Phile the table is a "batch" table meaning that it is a temporally storage place for some data that will be updated with other criteria and then uploaded into a SQL Server database.

Each week (that is until we are working in a 100% SQL environment) the batch table will be cleared of last weeks data before the new weeks data is added.

I have not been able to resolve this inside a query so I am guessing that I am going to have to create a sub to add and increment my numbers with a For Next Loop.

Thanks…

 

[Mile-O]

I wouldn't use a For... Next loop.

I'd use a Do While...Loop structure.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngRecord As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")

With rs
    Do While Not .EOF
        lngRecord = lngRecord + 1
        .Edit
        .Fields("ID") = lngRecord
        .Update
        .MoveNext
    Loop
    .Close
End With

Set rs = Nothing
Set db = Nothing

Does this help?

 

[Jon K]

Is there anyway to do it in a query? This way I can do it dynamically and add the field to the table on the fly and as I may need it.

You can use an Alter Table query to add an autonumber field to the table (type in the SQL View of a new query, replacing with the correct table name):-

ALTER TABLE [TableName] ADD COLUMN [BatchSeq] AutoIncrement;


There are some limitations though:
The table must not already have an autonumber field.
When the query is run, the BatchSeq field is added at the end of the fields.


If you need to re-run the query, you must drop the column first:-
ALTER TABLE [TableName] DROP COLUMN [BatchSeq];

 

[BukHix]

Thanks Jon K that worked perfectly.