counter value

[doug]

I would like to write a query that inserts data into a table based on
parameter data. The number of records inserted would be a parameter, but i
also want to create a unique serial number for each record, incrementing by
1, starting with one of the parameter values.

For example:
Parameter item number - enter 123
Parameter Qty - enter 20
Parameter Starting Serial # - enter 1000

I then went 20 individual records inserted into the table. All with item
number 123 and serial numbers 1000-1019.

What is the best and most efficient way to accomplish this?

Thanks in advance.

 

[Michel Walsh]

Use a driver table. Say IOTAS, one field, the primary key, iota, with values
from say, 0 to 99. Then:


SELECT itemNumber, [starting] + iotas.iots
FROM iotas
WHERE iotas < [qty]



Hoping it may help,
Vanderghast, Access MVP

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing number from 0
(zero) through your maximum quanity. Change the table and field names in the
query below to match yours.

INSERT INTO YourTable ( ID, SN )
SELECT [Enter item number] AS Expr1, [Enter Starting Serial #]+[CountNUM] AS
Expr2
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter qunaity of records]-1));

 

[doug]

That will get me what i need. Thank you for the advice.

Use a driver table. Say IOTAS, one field, the primary key, iota, with values
from say, 0 to 99. Then:


SELECT itemNumber, [starting] + iotas.iots
FROM iotas
WHERE iotas < [qty]



Hoping it may help,
Vanderghast, Access MVP

I would like to write a query that inserts data into a table based on
parameter data. The number of records inserted would be a parameter, but
i
also want to create a unique serial number for each record, incrementing
by
1, starting with one of the parameter values.

For example:
Parameter item number - enter 123
Parameter Qty - enter 20
Parameter Starting Serial # - enter 1000

I then went 20 individual records inserted into the table. All with item
number 123 and serial numbers 1000-1019.

What is the best and most efficient way to accomplish this?

Thanks in advance.