Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MS Access Insert Records Using Record Row Value

Status
Not open for further replies.
pa2

pa2

Programmer
Apr 17, 2007
19
GB
Hello,

This is my data in a table named Tablke1:
RefID Date Date2 LineNo Des Num Months
4345 21/03/2005 28/06/2010 1 4 3 M
4345 21/03/2005 28/06/2010 2 4 3 M
4345 21/03/2005 28/06/2010 3 4 3 M
4345 21/03/2005 28/06/2010 4 4 3 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M
I need VBA to Produce the follwing records in a table named Table2:
The records are simply duplicates of Table1 records with each record copied number of times by the value in field "Num".
So my finished Table2 data would be:
RefID Date Date2 LineNo Des Num Months
4345 21/03/2005 28/06/2010 1 4 3 M
4345 21/03/2005 28/06/2010 1 4 3 M
4345 21/03/2005 28/06/2010 1 4 3 M
4345 21/03/2005 28/06/2010 1 4 3 M
4345 21/03/2005 28/06/2010 2 4 3 M
4345 21/03/2005 28/06/2010 2 4 3 M
4345 21/03/2005 28/06/2010 2 4 3 M
4345 21/03/2005 28/06/2010 2 4 3 M
4345 21/03/2005 28/06/2010 3 4 3 M
4345 21/03/2005 28/06/2010 3 4 3 M
4345 21/03/2005 28/06/2010 3 4 3 M
4345 21/03/2005 28/06/2010 3 4 3 M
4345 21/03/2005 28/06/2010 4 4 3 M
4345 21/03/2005 28/06/2010 4 4 3 M
4345 21/03/2005 28/06/2010 4 4 3 M
4345 21/03/2005 28/06/2010 4 4 3 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M
 
Sort by date Sort by votes
Sounds like afairly simple task, loop through the recordset using the value under num to start a second loop that inserts to table2.

What have you tried?

please post your code.
 
Upvote 0 Downvote
Hello CaptainD,

I thought it would be simple too but I can't find the solution. This is my code:

Dim rs As DAO.Recordset
Dim strSQL As String
Dim x As Byte

strSQL = "DELETE Table1.* " & _
"FROM Table1;"
CurrentDb.Execute strSQL
Set rs = CurrentDb.OpenRecordset("SELECT Table2.RefID, Table2.Date, Table2.Date2, Table2.LineNo, Table2.Des, Table2.Num, Table2.Months " & _
"FROM Table2;")

For x = 1 To rs!Num
strSQL = "INSERT INTO Table1 ( RefID, [Date], Date2, LineNo, Des, Num, Months ) " & _
"SELECT Table2.RefID, Table2.Date, Table2.Date2, Table2.LineNo, Table2.Des, " & _
"Table2.Num , Table2.Months " & _
"FROM Table2;"
CurrentDb.Execute strSQL
rs.MoveNext
Next x
Set rs = Nothing

I know it's not right but it's all I have.

Amendment. I failed to mention one thing about the data returned in Table2. The column "Des" needs to be updated to a sequential number invcremented by one for each distinct "RefID".
So my finished Table2 data would in fact be:
Table2
RefID Date Date2 LineNo Des Num Months
4345 21/03/2005 28/06/2010 1 4 3 M
4345 21/03/2005 28/06/2010 2 4 3 M
4345 21/03/2005 28/06/2010 3 4 3 M
4345 21/03/2005 28/06/2010 4 4 3 M
4345 21/03/2005 28/06/2010 5 4 3 M
4345 21/03/2005 28/06/2010 6 4 3 M
4345 21/03/2005 28/06/2010 7 4 3 M
4345 21/03/2005 28/06/2010 8 4 3 M
4345 21/03/2005 28/06/2010 9 4 3 M
4345 21/03/2005 28/06/2010 10 4 3 M
4345 21/03/2005 28/06/2010 11 4 3 M
4345 21/03/2005 28/06/2010 12 4 3 M
4345 21/03/2005 28/06/2010 13 4 3 M
4345 21/03/2005 28/06/2010 14 4 3 M
4345 21/03/2005 28/06/2010 15 4 3 M
4345 21/03/2005 28/06/2010 16 4 3 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M
62633 20/06/2007 18/03/2009 7 2 6 M
62633 20/06/2007 18/03/2009 8 2 6 M
62633 20/06/2007 18/03/2009 9 2 6 M
62633 20/06/2007 18/03/2009 10 2 6 M
62633 20/06/2007 18/03/2009 11 2 6 M
62633 20/06/2007 18/03/2009 12 2 6 M
62633 20/06/2007 18/03/2009 13 2 6 M
62633 20/06/2007 18/03/2009 14 2 6 M
62633 20/06/2007 18/03/2009 15 2 6 M
62633 20/06/2007 18/03/2009 16 2 6 M
62633 20/06/2007 18/03/2009 17 2 6 M
62633 20/06/2007 18/03/2009 18 2 6 M
62633 20/06/2007 18/03/2009 19 2 6 M
62633 20/06/2007 18/03/2009 20 2 6 M
62633 20/06/2007 18/03/2009 21 2 6 M
62633 20/06/2007 18/03/2009 22 2 6 M
62633 20/06/2007 18/03/2009 23 2 6 M
62633 20/06/2007 18/03/2009 24 2 6 M
62633 20/06/2007 18/03/2009 25 2 6 M
62633 20/06/2007 18/03/2009 26 2 6 M
62633 20/06/2007 18/03/2009 27 2 6 M
62633 20/06/2007 18/03/2009 28 2 6 M
62633 20/06/2007 18/03/2009 29 2 6 M
62633 20/06/2007 18/03/2009 30 2 6 M
62633 20/06/2007 18/03/2009 31 2 6 M
62633 20/06/2007 18/03/2009 32 2 6 M
62633 20/06/2007 18/03/2009 33 2 6 M
62633 20/06/2007 18/03/2009 34 2 6 M
62633 20/06/2007 18/03/2009 35 2 6 M
62633 20/06/2007 18/03/2009 36 2 6 M

 
Upvote 0 Downvote
Code: 
...
Set rs = CurrentDb.OpenRecordset("SELECT RefID, [Date], Date2, LineNo, Des, Num, Months FROM Table2")
While Not rs.EOF
  For x = 1 To rs!Num
    'build strSQL here
    CurrentDb.Execute strSQL
  Next x
  rs.MoveNext
Wend
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Amendment. I failed to mention one thing about the data returned in Table2. The column "Des" needs to be updated to a sequential number invcremented by one for each distinct "RefID".
Click to expand...

To amend PH's code
Code: 
Dim iIDHolder as Integer
Dim y as Integer
...
Set rs = CurrentDb.OpenRecordset("SELECT RefID, [Date], Date2, LineNo, Des, Num, Months FROM Table2")
While Not rs.EOF
  If iIDHolder <> rs!RefID Then
     y = 1 '[COLOR=green]Set to 1[/color]
  End IF
  For x = 1 To rs!Num
    'build strSQL here
    CurrentDb.Execute strSQL
  y = y + 1 [COLOR=green]'Use y for the "Des" number[/color]
  Next x
  rs.MoveNext
  iIDHolder = rs!RefID
Wend
...
 
Upvote 0 Downvote
Create a table of numbers
tblNums
Numb integer

Add numbers 1 through max Num.

Create a query like:
Code: 
SELECT RefID, [Date], [Date2], LineNo, Des, Num, Months
FROM tablke1, tblNums
WHERE tablke1 Num = Numb;
If this returns the correct number of records, change it into a maketable or append query.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Thank you for your responses. I have run both codes but unfortunately they do not produce the required results. PHV’s code produces 480 records (192 with "RefID" 4345 and 288 with "RefID" 62633) . CaptainD’s amendment to PHV’s code produces “Run-time Error ‘6’ Overflow”.

I thought it might be helpful to clarify what the code needs to do:
Table2 contains the source data the records shown are only a small sample to indicate the data pattern. Table2 will have 30,000 records approximately.. The code will generate many, many times that number of records for Table1.

The code must insert into Table1 each record duplicating it by the number shown in the field “Num”. It also needs to update the number in the field “LineNo” to maintain a sequential number for each "RefID" group.

Table2 (this is the source data)
RefID Date Date2 LineNo Des Num Months
4345 21/03/2005 28/06/2010 1 4 3 M
4345 21/03/2005 28/06/2010 2 4 3 M
4345 21/03/2005 28/06/2010 3 4 3 M
4345 21/03/2005 28/06/2010 4 4 3 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M

Using the sample data shown in Table2 (above) Table1 (below) would contain the following data: 48 records (12 with "RefID" 4345 and 36 with "RefID" 62633). Note the filed “LineNo” is a sequential number for each "RefID" group.

Table1 (this is the data the code needs to generate)
RefID Date Date2 LineNo Des Num Months
4345 21/03/2005 28/06/2010 1 4 3 M
4345 21/03/2005 28/06/2010 2 4 3 M
4345 21/03/2005 28/06/2010 3 4 3 M
4345 21/03/2005 28/06/2010 4 4 3 M
4345 21/03/2005 28/06/2010 5 4 3 M
4345 21/03/2005 28/06/2010 6 4 3 M
4345 21/03/2005 28/06/2010 7 4 3 M
4345 21/03/2005 28/06/2010 8 4 3 M
4345 21/03/2005 28/06/2010 9 4 3 M
4345 21/03/2005 28/06/2010 10 4 3 M
4345 21/03/2005 28/06/2010 11 4 3 M
4345 21/03/2005 28/06/2010 12 4 3 M
62633 20/06/2007 18/03/2009 1 2 6 M
62633 20/06/2007 18/03/2009 2 2 6 M
62633 20/06/2007 18/03/2009 3 2 6 M
62633 20/06/2007 18/03/2009 4 2 6 M
62633 20/06/2007 18/03/2009 5 2 6 M
62633 20/06/2007 18/03/2009 6 2 6 M
62633 20/06/2007 18/03/2009 7 2 6 M
62633 20/06/2007 18/03/2009 8 2 6 M
62633 20/06/2007 18/03/2009 9 2 6 M
62633 20/06/2007 18/03/2009 10 2 6 M
62633 20/06/2007 18/03/2009 11 2 6 M
62633 20/06/2007 18/03/2009 12 2 6 M
62633 20/06/2007 18/03/2009 13 2 6 M
62633 20/06/2007 18/03/2009 14 2 6 M
62633 20/06/2007 18/03/2009 15 2 6 M
62633 20/06/2007 18/03/2009 16 2 6 M
62633 20/06/2007 18/03/2009 17 2 6 M
62633 20/06/2007 18/03/2009 18 2 6 M
62633 20/06/2007 18/03/2009 19 2 6 M
62633 20/06/2007 18/03/2009 20 2 6 M
62633 20/06/2007 18/03/2009 21 2 6 M
62633 20/06/2007 18/03/2009 22 2 6 M
62633 20/06/2007 18/03/2009 23 2 6 M
62633 20/06/2007 18/03/2009 24 2 6 M
62633 20/06/2007 18/03/2009 25 2 6 M
62633 20/06/2007 18/03/2009 26 2 6 M
62633 20/06/2007 18/03/2009 27 2 6 M
62633 20/06/2007 18/03/2009 28 2 6 M
62633 20/06/2007 18/03/2009 29 2 6 M
62633 20/06/2007 18/03/2009 30 2 6 M
62633 20/06/2007 18/03/2009 31 2 6 M
62633 20/06/2007 18/03/2009 32 2 6 M
62633 20/06/2007 18/03/2009 33 2 6 M
62633 20/06/2007 18/03/2009 34 2 6 M
62633 20/06/2007 18/03/2009 35 2 6 M
62633 20/06/2007 18/03/2009 36 2 6 M

Thanks.
 
Upvote 0 Downvote
PHV's code produces
Why not posting YOUR version of my suggested starting point ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Hello PHV,

This is your code with my sql:

Function eight()

Dim rs As DAO.Recordset
Dim strSQL As String
Dim x As Integer

Set rs = CurrentDb.OpenRecordset("SELECT RefID, [Date], Date2, LineNo, Des, Num, Months FROM Table2")
While Not rs.EOF
For x = 1 To rs!Num
'build strSQL here
strSQL = "INSERT INTO Table1 ( RefID, [Date], Date2, LineNo, Des, Num, Months ) " & _
"SELECT Table2.RefID, Table2.Date, Table2.Date2, Table2.LineNo, Table2.Des, " & _
"Table2.Num , Table2.Months " & _
"FROM Table2;"
CurrentDb.Execute strSQL
Next x
rs.MoveNext
Wend

Set rs = Nothing

End Function

It does produce records but unfortunately it produced 480 and for the data example I posted it should produce 48. Also it did not perform the sequential number change required to the field "LineNo".

Many thanks.
 
Upvote 0 Downvote
Use the INSERT INTO ... VALUES (...) syntax instead.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Sorry but I need more detailed help. If you have code that produces the required data can you please post it.

Many thanks.
 
Upvote 0 Downvote
Sorry, but you really have to learn the basis of SQL.
 
Upvote 0 Downvote
On my code change Dim y as Integer to a Long]

My gues is that many records made the value to large for an integer
 
Upvote 0 Downvote
Dim rs As DAO.Recordset
Dim strSQL As String
Dim x As Integer

Set rs = CurrentDb.OpenRecordset("SELECT RefID, [Date], Date2, LineNo, Des, Num, Months FROM Table2")
While Not rs.EOF
For x = 1 To rs!Num
'build strSQL here
strSQL = "INSERT INTO Table1 ( RefID, [Date], Date2, LineNo, Des, Num, Months ) " & _
"SELECT Table2.RefID, Table2.Date, Table2.Date2, Table2.LineNo, Table2.Des, " & _
"Table2.Num , Table2.Months " & _
"FROM Table2;"
CurrentDb.Execute strSQL
Next x
rs.MoveNext
Wend

Set rs = Nothing
Click to expand...

In the newest code you posted, you are not using the recordset you created. You are just trying to insert the data from a select statement.

The idea we showed you would step through each record one at a time to build "strSql" with the data you want, one at a time.

With as many records as you indicate, it would be slow

I have not done a "rolling sum" query in a long time but that might work. I'll have to take a look and "refresh" my memory to see if I could make that work

dhookom's suggestion might be a version of that but I have not tried it his way before.
 
Upvote 0 Downvote
An example of what PHV suggested

Note: Not Tested
Code: 
Dim iIDHolder as Integer
Dim y as Long
...
Set rs = CurrentDb.OpenRecordset("SELECT RefID, [Date], Date2, LineNo, Des, Num, Months FROM Table2")
While Not rs.EOF
  If iIDHolder <> rs!RefID Then
     y = 1 'Set to 1
  End IF
  For x = 1 To rs!Num
    strSQL = "INSERT INTO Table1 ( RefID, [Date], Date2, LineNo, Des, Num, Months ) VALUES(" & rs!RefID & ", '" & rs!Date & "', '" & rs!Date2 & "', " & rs!LineNo & ", " & y & ", " & rs!Num & ", '" & rs!months & "')"
    CurrentDb.Execute strSQL
  y = y + 1 'Use y for the "Des" number
  Next x
  rs.MoveNext
  iIDHolder = rs!RefID
Wend

If you place a stop point prior to the strSql you can step through your code to check your values
 
Upvote 0 Downvote
Thanks to everyone who contributed to this thread. I will add your suggestions to my own ideas and continue to work towards the best solution. Thanks again.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
559
strongm
strongm
Andrzejek
  • Locked
  • Question
Outlook VBA - Trust access
Replies
3
Views
490
hermanlaksko
hermanlaksko
remeng
  • Locked
  • Question
Using Err.Raise question
Replies
2
Views
422
mintjulep
M
philrock
  • Locked
  • Question
Searching equations in MS Word
Replies
4
Views
148
philrock
philrock
dgillz
  • Locked
  • Question
Back coloring an entire row if a duplicate is found in a dynamic range 1
Replies
18
Views
360
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top