Generate Sequential Number in Append Query

  • Thread starter Thread starter Bill Sturdevant
  • Start date Start date
B

Bill Sturdevant

I am using the SQL statement below to insert a bunch of
records into a table. I want to generate an Index Number
for each record that is sequential. I cannot use an
autonumber field.

Generate_Next_Sequential_Index_Num() is a routine that
accesses a public variable and bumps it by 1

The problem is, Generate_Next_Sequential_Index_Num() only
gets called ONCE in the query and every inserted record
then gets the value returned from that single call.

What am I doing wrong?

INSERT INTO Tbl_myTable ( Index_Number, Portfolio_Name )
SELECT DISTINCT Generate_Next_Sequential_Index_Num() AS
Expr1, Temp_Input_File.Portfolio_Name
FROM Temp_Input_File;
 
I know of no way to generate sequential numbers in a query like you are
trying to do.
If I wanted to do this I would go with a temporary table, then append
its contents to the real table.

Pavel
 
Actually, there is an easy way to do this: See my posting
of the same subject in the Queries NewsGroup for the
details, but it involves specifying a parameter in the
number generator function to fake Access into calling it
for every record.
 
Back
Top