Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2607
  • Last Modified:

Updat Access 2010 Records with Sequential Numbers

I have an access table that I want to update each record with sequential number starting at 1. Thanks
0
shieldsco
Asked:
shieldsco
  • 17
  • 6
  • 5
  • +4
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Quick and Dirty ...
Add a new field and make it the AutoNumber
Save the Table design.
Records will be numbered sequentially from one.

AN
0
 
rspahitzCommented:
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.
0
 
shieldscoAuthor Commented:
I want to use an update query
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
shieldscoAuthor Commented:
Can you give me the syntax
0
 
rspahitzCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
No internal function per se.  Requires a custom function.
0
 
shieldscoAuthor Commented:
I do not think there is a built in function in Access like thant
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
is this a one shot thing ... or ongoing ?
0
 
shieldscoAuthor Commented:
One shot
0
 
rspahitzCommented:
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. :)
0
 
shieldscoAuthor Commented:
Sorry that manual process will not work for me. I need to the syntax for an update query...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Then you will need a custom function to generate the sequential number.

How many records are you updating with this number ?
0
 
shieldscoAuthor Commented:
5000
0
 
rspahitzCommented:
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

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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 ?
0
 
shieldscoAuthor Commented:
What is the ID
0
 
shieldscoAuthor Commented:
I'm creating a copy of a linked table so therefore there is no key hence the reason for building a key
0
 
shieldscoAuthor Commented:
Also the field types are all text
0
 
Gustav BrockCIOCommented:
> 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
0
 
shieldscoAuthor Commented:
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.
0
 
Gustav BrockCIOCommented:
> is this a one shot thing ... or ongoing ?

You answered:
> One shot

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

So?

/gustav
0
 
shieldscoAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
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
0
 
shieldscoAuthor Commented:
Delete the old table
0
 
PatHartmanCommented:
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.
0
 
shieldscoAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
what kind of file are you importing? excel, .csv, .txt?
0
 
shieldscoAuthor Commented:
txt
0
 
shieldscoAuthor Commented:
It is a linked excel file that I create a copy using VBA
0
 
shieldscoAuthor Commented:
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 & "];"
0
 
Dale FyeCommented:
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

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)

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]);

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.
0
 
Gustav BrockCIOCommented:
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
0
 
Rey Obrero (Capricorn1)Commented:
'Copy data into a new table
         DoCmd.RunSQL "SELECT [" & strLinkedTable & "].* INTO [" & strNewTable & "] FROM [" & strLinkedTable & "];"

'add the codes below to your codes

CurrentDb.Execute "alter table " & "[" & strNewTable & "]" & " add column SeqNumber Number"
Set rs = CurrentDb.OpenRecordset(strNewTable)
j = 1
Do Until rs.EOF
    rs.Edit
    rs!SeqNumber = j
    rs.Update
   
    rs.MoveNext
    j = j + 1
Loop
rs.Close
0
 
shieldscoAuthor Commented:
Rey you are the man... Thanks
0
 
Dale FyeCommented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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.

!
0
 
Gustav BrockCIOCommented:
Yes. Way out.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 17
  • 6
  • 5
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now