Generatiing Sequential Number Table Using Query 1

[scottparks]

I have a table of product codes. I have another table that is populated by user input through a form. The user enters the product code, beginning serial number and ending serial number. I want to take that info and create another table with all of the entries. Example of user input:
Product Code = 1015020
BSerial = 89330
ESerial = 89335
Table generated would be:
1015020 89330
1015020 89331
1015020 89332
1015020 89333
1015020 89334
1015020 89335
I would like to do this without using VB if possible. I am new to Access and would appreciate any help.
Thanks in advance.

 

[Golom]

Construct a table of integers like this
[tt]Integers
Num
0
1
2
3
:
8
9
[/tt]
and then construct a query

SELECT 10*[I1].[num]+[I2].[Num] AS n
FROM Integers AS I1, Integers AS I2

And call it [blue]Integers2[/blue]

Then

Select PCode, I.N + BSerial As [Serial]

From Integers2  I, SerialNumbers M

Where I.N + BSerial <= M.ESerial 

Order By 1, 2

 

[scottparks]

I don't exactly understand how this works but it does pretty much what I wanted. I will work with it some more. Thanks for you help.

 

[Golom]

The above will work as long as there are fewer than 100 values between BSerial and ESerial (including the end-points.)

If you have instances where there are more than 100 values then you will need a query [blue]Integers3[/blue]

SELECT 100*[I1].[num] + 10*[I2].[num]+[I3].[Num] AS n
FROM Integers AS I1, Integers AS I2, Integers AS I3

and use that in place of [blue]Integers2[/blue].

 

[scottparks]

I will probably have a few instances where there will be more than 100.

Let me add something to this. Let's say I want to have an "A" and "B" serial number. Example
A89330
B89330
A89331
B89331
A89332
B89332
How much trouble is it to add this as a prefix. It can actually be another field and I can just concatenate the two fields. Basically it would need to generate to entries for every serial number. Maybe I'm not looking at this right. Do you have a suggestion?
Thanks for you great help. Your original solution works perfectly.

 

[Golom]

I suppose you could

Select PCode, ("A" & I.N + BSerial) As [Serial]
From Integers2  I, SerialNumbers M
Where I.N + BSerial <= M.ESerial 

UNION ALL

Select PCode, ("B" & I.N + BSerial) As [Serial]
From Integers2  I, SerialNumbers M
Where I.N + BSerial <= M.ESerial 

Order By 1, 2

 

[scottparks]

That doesn't work. It says Action-Query can't be used as a row source. But I does work if you leave of the Union and second select statement. It does add and A to the front of the number. I could create two queries and run them together in a macro. I think I can make that work.

Thanks for you help.

 

[Golom]

It works on my simple test table. Can you show the SQL that's giving the error message?

 

[scottparks]

SELECT M.ProductCode, ( "A" & I.N+BSerial) AS Serial INTO tblSerialNumbers
FROM tblIntegers3 AS I, tblProductTicket AS M
WHERE (((I.N+[BSerial])<=M.ESerial))

UNION ALL

SELECT M.ProductCode, ( "B" & I.N+BSerial) AS Serial INTO tblSerialNumbers
FROM tblIntegers3 AS I, tblProductTicket AS M
WHERE (((I.N+[BSerial])<=M.ESerial))
ORDER BY 1, 2;

I changed names to match my tables. Access added some items to this query. I cut and pasted your solution and made table name changes, saved and ran.

 

[PHV]

INSERT INTO tblSerialNumbers SELECT * FROM (
SELECT M.ProductCode, ( "A" & I.N+BSerial) AS Serial
FROM tblIntegers3 AS I, tblProductTicket AS M
WHERE (((I.N+[BSerial])<=M.ESerial))
UNION ALL
SELECT M.ProductCode, ( "B" & I.N+BSerial) AS Serial
FROM tblIntegers3 AS I, tblProductTicket AS M
WHERE (((I.N+[BSerial])<=M.ESerial))
) AS U
ORDER BY 1, 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Use PHV's solution ... but note that the table tblSerialNumbers must exist before you do this.

Your problem was that you put the [blue]INTO tblSerialNumbers[/blue] on each of the SELECTs within the UNION rather than on a SELECT that contains the combined data as PHV has done. When you use the [blue]INTO[/blue] keyword the query doesn't return records ... it rather writes the records to a new table and the implication of your construction was that each SELECT would create a new table called tblSerialNumbers.

 

[scottparks]

Thanks for all your suggestions. This solution is closer. Here is a sample of the table the last solution generated. I cleared the table before I ran it. The starting values were:
Product Code 1015020
BSerial: 890362
ESerial: 890475
This excerpt is from the middle of the table. It starts out all A's down to 890468 then:

ProductCode Serial
1015020 A890362
:: ::
1015020 A890466
1015020 A890467
1015020 A890468
1015020 B890459
1015020 B890460
1015020 B890461
1015020 B890462
1015020 B890463
1015020 B890464
1015020 B890465
1015020 B890466
1015020 B890467
1015020 B890468
1015020 B890469
1015020 B890470
1015020 B890471
1015020 B890472
1015020 B890473
1015020 B890474
1015020 B890475
1015020 A890469
1015020 A890470
1015020 A890471
1015020 A890472

I cut and pasted the solution verbatim. No changes.
I appreciate your patience with my learning curve on this one.

 

[Golom]

Is it the order that concerns you?

There is no guaranteed order in which records are returned when you just display a table or use a query that has no ORDER BY clause. If you want a particular ordering displayed then

Select ProductCode, Serial

From tblSerialNumbers

ORDER BY ProductCode, Val(Mid([Serial],2)), [Serial]

Should give it to you as
[tt]
ProductCode Serial
1015020 A890466
1015020 B890466
1015020 A890467
1015020 B890467
1015020 A890468
1015020 B890468
etc.
[/tt]