Expand range - Microst Access

[Trent1976]

am attempting to create a query/program which can expand a group of ‘Certificates’ into individual Certificates.

To explain, I start with the following

Accreditation Start No. End No. Sum Month

R00999RREN 0000000105 0000600105 61 Jan-05

I need to break the number apart so that this would show as 61 records, eg

R00999RREN 0000000105 0000000105 1 Jan-05

R00999RREN 0000010105 0000010105 1 Jan-05

R00999RREN 0000020105 0000020105 1 Jan-05

R00999RREN 0000030105 0000030105 1 Jan-05

Through to record….

R00999RREN 0000600105 0000600105 1 Jan-05


Can anyone help?

 

[SkipVought]

Trent1976,

Here's an example of storing aggregate data instead of source/detail data -- it's a bad database design to begin with.

I think that you're gonna have to write som VB code to take each row and expand it accordingly.

m = cint(left("0000000105", 6))
n = cint(left("0000600105", 6))
for i = m to n
    sNum = Format(i,"000000") & "0105"
next

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Trent1976]

Thanks for your input Skip