Updat Access 2010 Records with Sequential Numbers

Quick and Dirty ...
Add a new field and make it the AutoNumber
Save the Table design.
Records will be numbered sequentially from one.

The other way, if you need better assurance that the number will be sequential, is to use the MAX function on that field and add 1 "BeforeInsert".  This can have problems if multiple people access the table at the same time so it may require locks on the table if you expect that.

BTW Autonumber is very reliable as long as people don't cancel the record after initially edited.  If the records are created in the backgroumd, that's definitely the way to go.

I want to use an update query

Can you give me the syntax

I'm trying to determine how Access handles "row number". I'm pretty sure you can create a custom function to return this, but I think there may be an internal function for it like in Oracle.

UPDATE MyTable Set SeqNum = getNextRow()

Where getNextRow() is something like SELECT Count(*) From MyTable Where LastUpdated <= current.record

No internal function per se.  Requires a custom function.

I do not think there is a built in function in Access like thant

is this a one shot thing ... or ongoing ?

One shot

One shot? Hmmm...create a new table with the key from the table you have now, add the autonumber field to the new table, copy all keys to this new table, copy the autonumber field back where the keys match. :)

Sorry that manual process will not work for me. I need to the syntax for an update query...

Then you will need a custom function to generate the sequential number.

How many records are you updating with this number ?

5000

Here's something that will work, but it will take 3 parts (and may take a while to run with 5000 records)

1) Create a custom function
a) Go to VBA area: Tab "Database Tools", Group "Macro", Item "Visual Basic"
b) Add a module: menu Insert | Module
c) Add the following function code:

Function RowNumber(TableName As String, ID As Long) As Long
    Dim cnt As Long
    cnt = DCount("*", TableName, "ID <= " & ID)
    RowNumber = cnt
End Function

Select all Open in new window

d) Save the module (Ctrl+S) using any name such as Module1

2) Create a query to generate the count of records, based on your table (MyTable)
a) Create a new query: Tab Create, Queries, Query Design
b) Add your table, MyTable
c) Add the ID field
d) Add a calculated field based on the function (notice the quote around your table, but not the ID):
     NewSeqNum: SeqNum: RowNumber("MyTable",[ID])
e) optionally run your query and verify that it shows a sequence from 1 to 5000
f) Save the query as something like "qrySeqNum"

3) Create an update query to transfer the sequence number into your field
a) Create a new query
b) Add your table (MyTable) and the above query (qrySeqNum, from the Queries tab of the Show Table window)
c) Add the SeqNum field from your table
d) Change this to an Update query: Tab Design, Query Type Update
e) In the "Update To" row at the lower part of the query add this: [NewSeqNum]

If you run the query, it should tell you that you are about to update 5000 records.  If you say yes, they should get updated if you carefully followed the steps above.

FYI
If this is for a customer, you will need to deliver the above 3 things and they will need to ensure that they enable VA/Macros.

5000 records is a very small recordset.
And if this is a one off thing, why not just open the Table in a recordset, loop through it with a counter and add the incrementing number. This would be super fast - even on 500,000 records.  I guess I'm not seeing why you need to use an SQL 'update' query ?

What is the ID

I'm creating a copy of a linked table so therefore there is no key hence the reason for building a key

Also the field types are all text

> I'm creating a copy of a linked table so therefore there is no key hence the reason for building a key

Then listen to Joe: Import the table. Then you have the copy. Open it in design mode and add an AutoNumber field. Name it ID. Save, and you have your copy with an ID.

It will take you less time than reading the comments here.

/gustav

That is not what I want to do... This is a part of an automated process. The end user will not know how to do that plus it will be inefficient. The process will be run on a recurring basis.

> is this a one shot thing ... or ongoing ?

You answered:
> One shot

Now:
> The process will be run on a recurring basis.

So?

/gustav

Can you help me with what I described? I do not want to manually create the numbers. I need an automated way to increment each record with a number starting with 1

When the user has imported the table, what will happen next time she imports the table?

Delete the old table?
Append/update?
Overwrite?
Create new table with different name?

/gustav

Delete the old table

The ONLY way you can generate a sequence number using a query is if the recordset ALREADY has a unique ID.  You said it doesn't, therefore, you can't use a query.  Assigning a sequential number using a query uses a counting method - count the number of records less than or equal to (by comparing to the uniqueID) this one.  If you don't have a uniqueID, you don't have any way of determining where a record lies in a set.  The alternative is to write a function that returns a new number each time it is called.

Any of the other methods can be automated so I'm not sure why you are so fixated on a query.  Pick one and we'll help you.  But do get your requirements sorted out first.

ok then what would the function look like.. I'm not necessary fixed on a query... I just want to add a  number to each record starting at 1

what kind of file are you importing? excel, .csv, .txt?

txt

It is a linked excel file that I create a copy using VBA

Here is the code
Dim strLinkedTable As String
Dim strNewTable As String
On Error GoTo 10
For Each t In CurrentDb.TableDefs
   
    'Assumes all linked tables are prefixed with "lnk"
    If t.Name Like "lnk*" Then
        strLinkedTable = t.Name
        strNewTable = "Copy_" & t.Name
       
        'Copy data into a new table
        DoCmd.RunSQL "SELECT [" & strLinkedTable & "].* INTO [" & strNewTable & "] FROM [" & strLinkedTable & "];"

With a function similar to the following, you should be able to write a simple update query to add a sequential number to each record in your table.

  
Public Function fnSeq(SomeField as variant, _
                    Optional StartVal as long = 1, Optional Reset as boolean = false) as long

    Static mySeq as long

    if Reset = True Then mySeq = StartVal - 1

    mySeq = mySeq + 1
    fnSeq = mySeq

End Function

Select all Open in new window

This will depend on two things:
1.  You must first call the function, setting the starting value to 1 less than you want to use for your first value, so if you want your record numbering to start at 1, then you would use:

Call fnSeq(0, 0, True)

Select all Open in new window

you could do this in the immediate window or in your code just prior to executing your update query.  Then you would call the function in an update query, something like:

UPDATE yourTable SET RecSeqField = fnseq([SomeFieldName]);

Select all Open in new window

This will work as long as you pass the function a value from one of the fields in your table.  If you pass the function a numeric value, like fnSeq(1), it will return the next value in the sequence, but will do so for every record in your query.

Here's a simple method:

Create a query, qdyYourQuery:

SELECT CDbl(0) AS ID, tblYourTableLink.* INTO tblYourTableLocal
FROM tblYourTableLink;

Then run this code:

Public Function ImportTable()

    Dim db      As DAO.Database
    Dim qd      As DAO.QueryDef
    Dim rs      As DAO.Recordset
    Dim Index   As Integer
   
    Set db = CurrentDb
    db.TableDefs.Delete "tblYourTableLocal"
    Set qd = db.QueryDefs("qdyYourQuery")
    qd.Execute
    qd.Close
    Set rs = db.OpenRecordset("Select ID From tblYourTableLocal")
   
    While rs.EOF = False
        Index = Index + 1
        rs.Edit
            rs!Id.Value = Index
        rs.Update
        rs.MoveNext
    Wend
    rs.Close
   
    Set rs = Nothing
    Set qd = Nothing
    Set db = Nothing
   
End Function

/gustav

Rey you are the man... Thanks

I'm confused.  You indicated you wanted to update the seq # field in a query.  But then eventually accepted a procedure which just loops through records.

Meanwhile way back at ID: 40581093 I suggested:

"And if this is a one off thing, why not just open the Table in a recordset, loop through it with a counter and add the incrementing number. This would be super fast - even on 500,000 records. "

But you said it had to be an update query ... so I said you will need to call a Function ... and so on.

!

Yes. Way out.

/gustav