• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

How can I create a field in a table using a make table query which increments?

I have a make table query that i would like to create a field, not to be populated by the results of my query, but rather to start with 1, and increment by 1 for each successive record. Can someone point me in the right direction?
0
Shooter54
Asked:
Shooter54
1 Solution
 
SimonCommented:
I'd suggest you do it as an append query, after first creating a table with an autonumber field + the columns in your query. The query appends to every field other than the autonumber field.
0
 
Shooter54Author Commented:
I've tried that method. I created the table, appended records to it, and the first time it autonumbered correctly beginning with the number "1". Then I ran a delete query that deleted the records, preparing it for the next time I need to do the same thing, however the autonumber field doesn't begin numbering at "1" during subsequent appends, rather beginning at the next number that was available prior to running my delete query. I suppose there is a brilliant way to do this using code, however I'm not the greatest programmer using VBA.
0
 
PatHartmanCommented:
For starters, autonumbers  are simply a means to creating a unique identifier.  No meaning should be assigned to them.  If you want to have a number that you can control, use DMax() to assign a sequence number.

If you insist on using an autonumber, you can force it to reset by resetting its Seed.  I've attached the text of a KB article explaining how to do it.
Reseed.docx
0
 
Gustav BrockCIOCommented:
You can use this function which can reset or continue the counter as you like.
The in-line comments give typical code examples:
Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long
  
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If
  
  RowCounter = col(strKey)
  
Exit_RowCounter:
  Exit Function
  
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

Open in new window

/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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now