Increment Number on a Make Table

[Bart]

How do you add an increment number to a Make Table query
such that each record in the new table can have a unique
number? (Other than concatenating multiple fields)

 

[Guest]

Someone may come up with a better way, but it seems to me
you'd do better with an Append query, appending your query
output into an existing table which includes an Autonumber
field.
I would make the table the first time with a dummy run of
a Make Table query to establish field types. Then, having
made the table, modify its design to include an Autonumber
RecordID field. Sweep out the existing records. Change
the Make Table query that you use to create the table to
an Append Query with the new table as the target.
If your target table needs to be swept clean each time (as
it would be using repeated runs of the Make Table query),
create a Delete query that does this. Run that query just
prior to runs of the Append query. If you want to
automate the process somewhat, you can sequence these two
queries in a macro and then run the macro.

Just an idea. I'll be be looking to see if someone comes
up with a way to create a sequential set of numbers on the
fly.

 

[John Spencer (MVP)]

Write a function that will increment by one and then call it in the maketable statement.

UNTESTED AIRCODE FUNCTION

Public Function fCountUp(lngNumber As Long) As Long
Static lngCount As Long

If lngCount = 0 Or lngCount < lngNumber Then
lngCount = lngNumber
Else
lngCount = lngCount + 1
End If

fCountUp = lngCount
End Function

Query where you force the function to be called for every record.

SELECT FAQ.fSubject,
fCountup(Len([fSubject])/Len([fsubject])) AS Expr1 INTO NEWTABLE
FROM FAQ;