We help IT Professionals succeed at work.

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

Shooter54 asked
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?
Watch Question

SimonPrincipal Analyst

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.


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.
Distinguished Expert 2017

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.
Most Valuable Expert 2015
Distinguished Expert 2018
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
    col.Add col.Count + 1, strKey
  End If
  RowCounter = col(strKey)
  Exit Function
  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