By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,709 Members | 2,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,709 IT Pros & Developers. It's quick & easy.

Expanding a Series

P: n/a
aW
I have an interesting dilemma.

I have a table with the following records:

================================================== =
Box | Series Start | Series End
========+=======================+================= =
1 | 0001 | 0025
---------------------------------------------------
2 | 0026 | 0050
---------------------------------------------------
3 | 0051 | 0075
---------------------------------------------------
4 | 0076 | 0100
---------------------------------------------------

Now, I need to create a new table that will expand each of the series in
the above table:

==========================
Box | Serial No.
========+=================
1 | 0001
--------------------------
1 | 0002
--------------------------
1 | 0003

... ... ...

--------------------------
1 | 0025
--------------------------

What comes to mind right now is creating a form with a 'while' loop. But I
would have to load the form and then press a button to run it. It would be
nice to somehow create a make table query that will do the trick.

Any ideas will be greatly appreciated!

aW.
--
Thanks a million!
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Just a stab....
but how about a cartesean result instead of a new table.

You would have a table with all the values as individual records (1....100)
Then make a query with this table and the table (or query ) with the series
data.
Don't make any join betwen them......
You will then get a record for each possibility.
Limit this for each Box....by using Between [Series Start] and [Series End]

?????
Mal.

"aW" <a@no.spam.net> wrote in message news:40********@127.0.0.1...
I have an interesting dilemma.

I have a table with the following records:

================================================== =
Box | Series Start | Series End
========+=======================+================= =
1 | 0001 | 0025
---------------------------------------------------
2 | 0026 | 0050
---------------------------------------------------
3 | 0051 | 0075
---------------------------------------------------
4 | 0076 | 0100
---------------------------------------------------

Now, I need to create a new table that will expand each of the series in
the above table:

==========================
Box | Serial No.
========+=================
1 | 0001
--------------------------
1 | 0002
--------------------------
1 | 0003

... ... ...

--------------------------
1 | 0025
--------------------------

What comes to mind right now is creating a form with a 'while' loop. But I
would have to load the form and then press a button to run it. It would be
nice to somehow create a make table query that will do the trick.

Any ideas will be greatly appreciated!

aW.
--
Thanks a million!

Nov 13 '05 #2

P: n/a
aW
Mal Reeve wrote:
Just a stab....
but how about a cartesean result instead of a new table.

You would have a table with all the values as individual records
(1....100) Then make a query with this table and the table (or query )
with the series data.
Don't make any join betwen them......
You will then get a record for each possibility.
Limit this for each Box....by using Between [Series Start] and [Series
End]

?????
Mal.

"aW" <a@no.spam.net> wrote in message news:40********@127.0.0.1...
I have an interesting dilemma.


Thank you for your response, I'll play with this for a while.

--
Thanks a million!
Nov 13 '05 #3

P: n/a
aW <a@no.spam.net> wrote in message news:<40********@127.0.0.1>...
I have an interesting dilemma.

I have a table with the following records:

================================================== =
Box | Series Start | Series End
========+=======================+================= =
1 | 0001 | 0025
---------------------------------------------------
2 | 0026 | 0050
---------------------------------------------------
3 | 0051 | 0075
---------------------------------------------------
4 | 0076 | 0100
---------------------------------------------------

Now, I need to create a new table that will expand each of the series in
the above table:

==========================
Box | Serial No.
========+=================
1 | 0001
--------------------------
1 | 0002
--------------------------
1 | 0003

... ... ...

--------------------------
1 | 0025
--------------------------

What comes to mind right now is creating a form with a 'while' loop. But I
would have to load the form and then press a button to run it. It would be
nice to somehow create a make table query that will do the trick.

Any ideas will be greatly appreciated!

aW.


Something like this maybe... note, this uses DAO, so if you're using a
version after 97, you'll have to make sure the DAO 3.6 library is
checked in your references...

Option Compare Database
Option Explicit

Sub CreateBoxSeries()
Dim db As DAO.Database
Dim rsSrc As DAO.Recordset '-read series from this table
Dim rsDest As DAO.Recordset '--write created records to this
table
Dim intCounter As Integer

Set db = CurrentDb
Set rsSrc = db.OpenRecordset("tblCreateRecords",
dbOpenForwardOnly)
Set rsDest = db.OpenRecordset("tblBoxSerials", dbOpenTable,
dbAppendOnly)

Do Until rsSrc.EOF
'--insert a record in to rsDest for each number between the
ranges
For intCounter = rsSrc.Fields("SeriesStart") To
rsSrc.Fields("SeriesEnd")
With rsDest
.AddNew '--add a new record to the table
.Fields("BoxNo") = rsSrc.Fields("BoxNo")
.Fields("SerialNo") = Format(intCounter, "0000")
'--save it formatted as 0075 etc
.Update '--save the record
End With
Next intCounter
rsSrc.MoveNext '--go to the next series to create
Loop

'--Clean up
Set rsSrc = Nothing
Set rsDest = Nothing
Set db = Nothing

End Sub
Nov 13 '05 #4

P: n/a
aW
Pieter Linden wrote:
<sniped>

End Sub


Thank you, I'll see what happens.

--
Thanks a million!
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.