Solved

Updat Access 2010 Records with Sequential Numbers

Posted on 2015-01-30
37
557 Views
Last Modified: 2015-01-31
I have an access table that I want to update each record with sequential number starting at 1. Thanks
0
Comment
Question by:shieldsco
  • 17
  • 6
  • 5
  • +4
37 Comments
 
LVL 75
ID: 40580946
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 40580950
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
 

Author Comment

by:shieldsco
ID: 40580952
I want to use an update query
0
 

Author Comment

by:shieldsco
ID: 40580960
Can you give me the syntax
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40580965
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
 
LVL 75
ID: 40580970
No internal function per se.  Requires a custom function.
0
 

Author Comment

by:shieldsco
ID: 40580971
I do not think there is a built in function in Access like thant
0
 
LVL 75
ID: 40580973
is this a one shot thing ... or ongoing ?
0
 

Author Comment

by:shieldsco
ID: 40580974
One shot
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40580975
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
 

Author Comment

by:shieldsco
ID: 40580977
Sorry that manual process will not work for me. I need to the syntax for an update query...
0
 
LVL 75
ID: 40580987
Then you will need a custom function to generate the sequential number.

How many records are you updating with this number ?
0
 

Author Comment

by:shieldsco
ID: 40580988
5000
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40581085
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
 
LVL 75
ID: 40581093
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
 

Author Comment

by:shieldsco
ID: 40581355
What is the ID
0
 

Author Comment

by:shieldsco
ID: 40581358
I'm creating a copy of a linked table so therefore there is no key hence the reason for building a key
0
 

Author Comment

by:shieldsco
ID: 40581365
Also the field types are all text
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40581430
> 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
 

Author Comment

by:shieldsco
ID: 40581444
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40581463
> 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
 

Author Comment

by:shieldsco
ID: 40581473
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40581496
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
 

Author Comment

by:shieldsco
ID: 40581500
Delete the old table
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40581505
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
 

Author Comment

by:shieldsco
ID: 40581521
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40581522
what kind of file are you importing? excel, .csv, .txt?
0
 

Author Comment

by:shieldsco
ID: 40581525
txt
0
 

Author Comment

by:shieldsco
ID: 40581528
It is a linked excel file that I create a copy using VBA
0
 

Author Comment

by:shieldsco
ID: 40581529
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40581532
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40581541
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40581546
'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
 

Author Closing Comment

by:shieldsco
ID: 40581569
Rey you are the man... Thanks
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40581593
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
 
LVL 75
ID: 40581672
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40581690
Yes. Way out.

/gustav
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now