Filling a field with an increasing set of values

[Jay]

Hi, would somebody please help?
I've got a table tOrderbook with Fields: OderBookID, BookNo and OrderNo.
OrderBookID is Autonumber, BookNo is Numeric and OrderNo is Text. The values
entered would be something like: BookNo (938) and OrderNo (PRL95100 to
PRL95200, or WHK5200 to WHK5300). Is there a way that I would be able to fill
field 'BookNo' with "938" and fill 'OrderNo' with "PRL95100" right through to
"PRL95200". I would gues that I should be able to do it with an append
query, but I can't seem to get anywhere.
Many thanx

 

[John Spencer]

Yes, but you would need an auxiliary table to do it. The table would
have a one number field with the values from 0 to 101 (or whatever
maximum you needed). tblNumbers Field TheNumber

INSERT INTO tOrderbook (BookNo, OrderNo)
SELECT 938 as BookNumber, "PRL" & (95100 + TheNumber)
FROM tblNumbers.TheNumber
WHERE TheNumber Between 0 and 100

You might want between 0 and 99 if you don't want to generate OrderNo
PRL95200.

Of course you can make that simpler to modify by using some parameters
or references to values on a form. The form would have controls to
specify the BookNumber, the Prefix, the StartingPoint and the number of
records to generate.

INSERT INTO tOrderbook (BookNo, OrderNo)
SELECT Forms![YourFormName]![txtBookNumber] as BookNumber
, Forms![YourFormName]![txtPrefix] & (Forms![YourFormName]![FirstValue]
+ TheNumber) as OrderNumber
FROM tblNumbers.TheNumber
WHERE TheNumber Between 0 and Forms![YourFormName]![RecordCount]

If you can't use SQL view to build a query, post back and someone can
try to lead you through the steps of building the query. If you can't
add an auxiliary table to hold the numbers then let us know that also.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jay]

Thanks for the quick response. I am only just starting to get to know SQL,
but I think I understand what you are getting at. I'm going to give it a go
and get back to you asap.

Yes, but you would need an auxiliary table to do it. The table would
have a one number field with the values from 0 to 101 (or whatever
maximum you needed). tblNumbers Field TheNumber

INSERT INTO tOrderbook (BookNo, OrderNo)
SELECT 938 as BookNumber, "PRL" & (95100 + TheNumber)
FROM tblNumbers.TheNumber
WHERE TheNumber Between 0 and 100

You might want between 0 and 99 if you don't want to generate OrderNo
PRL95200.

Of course you can make that simpler to modify by using some parameters
or references to values on a form. The form would have controls to
specify the BookNumber, the Prefix, the StartingPoint and the number of
records to generate.

INSERT INTO tOrderbook (BookNo, OrderNo)
SELECT Forms![YourFormName]![txtBookNumber] as BookNumber
, Forms![YourFormName]![txtPrefix] & (Forms![YourFormName]![FirstValue]
+ TheNumber) as OrderNumber
FROM tblNumbers.TheNumber
WHERE TheNumber Between 0 and Forms![YourFormName]![RecordCount]

If you can't use SQL view to build a query, post back and someone can
try to lead you through the steps of building the query. If you can't
add an auxiliary table to hold the numbers then let us know that also.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi, would somebody please help?
I've got a table tOrderbook with Fields: OderBookID, BookNo and OrderNo.
OrderBookID is Autonumber, BookNo is Numeric and OrderNo is Text. The values
entered would be something like: BookNo (938) and OrderNo (PRL95100 to
PRL95200, or WHK5200 to WHK5300). Is there a way that I would be able to fill
field 'BookNo' with "938" and fill 'OrderNo' with "PRL95100" right through to
"PRL95200". I would gues that I should be able to do it with an append
query, but I can't seem to get anywhere.
Many thanx

 

[Jay]

OK, so I went and tried both, and the form work so much better. Again, thanx
a lot. You've been a great help!

Thanks for the quick response. I am only just starting to get to know SQL,
but I think I understand what you are getting at. I'm going to give it a go
and get back to you asap.

Yes, but you would need an auxiliary table to do it. The table would
have a one number field with the values from 0 to 101 (or whatever
maximum you needed). tblNumbers Field TheNumber

INSERT INTO tOrderbook (BookNo, OrderNo)
SELECT 938 as BookNumber, "PRL" & (95100 + TheNumber)
FROM tblNumbers.TheNumber
WHERE TheNumber Between 0 and 100

You might want between 0 and 99 if you don't want to generate OrderNo
PRL95200.

Of course you can make that simpler to modify by using some parameters
or references to values on a form. The form would have controls to
specify the BookNumber, the Prefix, the StartingPoint and the number of
records to generate.

INSERT INTO tOrderbook (BookNo, OrderNo)
SELECT Forms![YourFormName]![txtBookNumber] as BookNumber
, Forms![YourFormName]![txtPrefix] & (Forms![YourFormName]![FirstValue]
+ TheNumber) as OrderNumber
FROM tblNumbers.TheNumber
WHERE TheNumber Between 0 and Forms![YourFormName]![RecordCount]

If you can't use SQL view to build a query, post back and someone can
try to lead you through the steps of building the query. If you can't
add an auxiliary table to hold the numbers then let us know that also.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi, would somebody please help?
I've got a table tOrderbook with Fields: OderBookID, BookNo and OrderNo.
OrderBookID is Autonumber, BookNo is Numeric and OrderNo is Text. The values
entered would be something like: BookNo (938) and OrderNo (PRL95100 to
PRL95200, or WHK5200 to WHK5300). Is there a way that I would be able to fill
field 'BookNo' with "938" and fill 'OrderNo' with "PRL95100" right through to
"PRL95200". I would gues that I should be able to do it with an append
query, but I can't seem to get anywhere.
Many thanx