automatic record generation

  • Thread starter Thread starter shanna
  • Start date Start date
S

shanna

Need to create a function to fill a table with multiple
records based on an outline from a source table.
The source table has several entries and needs to be
traversed to get the desired output.

So if the source table has
[ID] , [start num] , [end num] , [status]
47 2 5 good
48 8 11 fair

The destination table should end up with
[ID] , [num] , [status]
47 2 good
47 3 good
47 4 good
47 5 good
48 8 fair
48 9 fair
48 10 fair
48 11 fair

This is a simplification of the real task, but if I could
figure out this, I could do the real (big-advanced) one
that we need. It is the logic that I lack to accomplish
this.

Any ideas or suggestions?
Thanks in advance
 
Make a Table tblItemNo with just a single Field ItemNo, same type as [start
num] and [end num]. Make this Field PK.

Field this Table with values from 1 to the max number for [end num] you
expect to get.

Create a Select Query with the following SQL String:

SELECT YT.ID, IT.ItemNo, YT.[Status]
FROM YourTable AS YT
INNER JOIN tblItemNo AS IT
ON (YT.[start num] <= IT.ItemNo)
AND (YT.[end num] >= IT.ItemNo)

Check the Select Query and make sure it shows the result you want and then
convert the Select Query to an Append Query to append Records to your dest.
Table.
 
Back
Top