Generate Data

S

Sazabi

Dear All,

To make it simple. Here's an example
First, I got a table,
first colume, "Name", with three records:
'David', 'Peter', 'John'
second colume, "Number",
'10', '20' and '30' respectively

I want to have a new table with............
first column, 1 to 60 ( 10 + 20 + 30 )
secound column,
10 'David' from 1 to 10;
20 'Peter' from 11 to 30; and
30 'John' from 31 to 60

I realy need somone to help me..............thanks first
 
D

Doug Munich

Here is a function using DAO that will generate the records in tblNameList
based on tblNameRepetitions. Note that the table the data is going into has
to be created and empty.

========================
Private Sub cmdMakeTable_Click()
On Error GoTo err_cmdMakeTable

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim Name As String
Dim reps As Long
Dim counter As Long
Dim i As Long

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblNameRepetitions")
Set rs2 = db.OpenRecordset("tblNameList") ' table must already be
created and empty
rs.MoveFirst
counter = 1
Do While (Not rs.EOF)
reps = rs.Fields("Repetitions")
Name = rs.Fields("Name")
For i = 1 To reps
rs2.AddNew
rs2.Fields("Index").Value = counter
rs2.Fields("Name").Value = Name
rs2.Update
counter = counter + 1
Next i
rs.MoveNext
Loop

exit_cmdMakeTable:
Exit Sub

err_cmdMakeTable:
MsgBox Err.Description
Resume exit_cmdMakeTable

End Sub
======================

Doug
 
Top