insert increasing number with SQL

[Jesper F]

Is it possible to do something like this:
INSERT INTO table1 (field1,field2) SELECT field1,x FROM table2
where x is an increasing number. Ex starting at 1 (or any number that I
determine) and increasing by one for each row that's inserted?


Jesper

 

[strive4peace]

Hi Jesper,

Make 2 procedures and a global variable in a general module

dim gX as Long

Sub SetX()
gX = 1 'or whatever number you want to start with
End Sub

Function GetX(pDummy) as Long
GetX = gX
gX = gX + 1
End Function

you need to pass it a dummy parameter that is not used but
is one of your fields so Access will evalulate the function
for each record

Another option is to make X an autonumber field and not
include it in your SQL

then, before you run your SQL, Run SetX to initialize the
global variable

in your SQL

INSERT INTO table1 (field1,field2)
SELECT field1,GetX([field1]) FROM table2


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Jesper F]

Make 2 procedures and a global variable in a general module

dim gX as Long

Sub SetX()
gX = 1 'or whatever number you want to start with
End Sub

Function GetX(pDummy) as Long
GetX = gX
gX = gX + 1
End Function

you need to pass it a dummy parameter that is not used but is one of your
fields so Access will evalulate the function for each record

Another option is to make X an autonumber field and not include it in your
SQL

then, before you run your SQL, Run SetX to initialize the global variable

in your SQL

INSERT INTO table1 (field1,field2)
SELECT field1,GetX([field1]) FROM table2

Awesome. Thanks very much.

Jesper

 

[Bill (Unique as my name)]

I don't understand. (I got hit in the head with a large brick five
minutes ago.)
I understand about setting up the variable in the module, but I'm not
getting the adjustment to the SQL. I set up a table with three
records; A, B, C in the letter field. Below is what my query SQL looks
like. What change to I make to get the increasing number field? Thank
you, (and pardon the splatters of hair and blood on the page.)

SELECT Deleteanytime.letter
FROM Deleteanytime
WITH OWNERACCESS OPTION;

 

[Jesper F]

I understand about setting up the variable in the module, but I'm not

getting the adjustment to the SQL. I set up a table with three
records; A, B, C in the letter field. Below is what my query SQL looks
like. What change to I make to get the increasing number field? Thank
you, (and pardon the splatters of hair and blood on the page.)

SELECT Deleteanytime.letter
FROM Deleteanytime
WITH OWNERACCESS OPTION;

In what field do you want to display the increasing number?
You must pass a field as a dummy parameter for it to give you a new,
increasing number for each record.
Does this work:

Dim gX as Long 'in the declarations section

Sub SetX()
gX = 1 'or whatever number you want to start with
End Sub

Function GetX(pDummy) as Long
GetX = gX
gX = gX + 1
End Function

SELECT letter,GetX([letter]) as ekspr1 FROM Deleteanytime



Jesper Fjølner

 

[strive4peace]

try this:

SELECT Deleteanytime.letter, GetX([letter])
FROM Deleteanytime;

This is the same thing I see you have below... is it not
working?

Before you rtun the query,

SetX

to reset the global variable


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

I understand about setting up the variable in the module, but I'm not
getting the adjustment to the SQL. I set up a table with three
records; A, B, C in the letter field. Below is what my query SQL looks
like. What change to I make to get the increasing number field? Thank
you, (and pardon the splatters of hair and blood on the page.)

SELECT Deleteanytime.letter
FROM Deleteanytime
WITH OWNERACCESS OPTION;

In what field do you want to display the increasing number?
You must pass a field as a dummy parameter for it to give you a new,
increasing number for each record.
Does this work:

Dim gX as Long 'in the declarations section

Sub SetX()
gX = 1 'or whatever number you want to start with
End Sub

Function GetX(pDummy) as Long
GetX = gX
gX = gX + 1
End Function

SELECT letter,GetX([letter]) as ekspr1 FROM Deleteanytime



Jesper Fjølner

 

[Bill (Unique as my name)]

Nevermind. I'm brain dead on this issue and totally clueless.
I'll read some manuals and try to figure it out.

Thanks for the help anyway