Expanding a Series

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!

re: Expanding a Series

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:40da190a_5@127.0.0.1...[color=blue]
> 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![/color]

re: Expanding a Series

Mal Reeve wrote:
[color=blue]
> 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:40da190a_5@127.0.0.1...[color=green]
>> I have an interesting dilemma.
>>[/color][/color]

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



--
Thanks a million!

re: Expanding a Series

aW <a@no.spam.net> wrote in message news:<40da190a_5@127.0.0.1>...[color=blue]
> 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.[/color]

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

re: Expanding a Series

Pieter Linden wrote:
[color=blue]
> <sniped>
>
> End Sub[/color]

Thank you, I'll see what happens.

--
Thanks a million!