=========
QUESTION
———
I have a table with one record per entity, and a field with the number drawing entries they have. How do I take this and create a table with one record for each of those drawing entries?

 

=========
ANSWER
———
Use the code in EXAMPLES below to open each record in your source table and create the specified number of records in the destination table.

Then follow the steps in this article to draw the winners:
http://www.anysitesupport.com/how-do-i-query-the-top-x-of-something/

 

=========
EXAMPLES
———
Me.CurrentStatus = _
"Setting up Drawing" & vbCrLf & _
vbCrLf & _
vbCrLf & _
"=========" & vbCrLf & _
Me.CurrentStatus
Me.Repaint

DoCmd.Hourglass True
DoCmd.SetWarnings False

Dim dbs As Database
Dim rsIn As Recordset
Dim rsOut As Recordset
Dim MaxRec As Integer

Set dbs = CurrentDb()
Set rsIn = dbs.OpenRecordset("5900T_ResultsForReport")
Set rsOut = dbs.OpenRecordset("6000_DrawingEntries")

Me.CurrentStatus = _
"Backing Up DrawingEntries Table" & vbCrLf & _
vbCrLf & _
Me.CurrentStatus
Me.Repaint

DoCmd.RunSQL "SELECT * INTO 6000_DrawingEntries_Backup FROM 6000_DrawingEntries;"
DoCmd.RunSQL "DELETE * FROM 6000_DrawingEntries;"

Me.CurrentStatus = _
"Generating Drawing Entries" & vbCrLf & _
vbCrLf & _
Me.CurrentStatus
Me.Repaint

With rsIn
.MoveFirst

Do Until .EOF
MaxRec = rsIn.Fields("TotalEntries")

For Totals = 1 To MaxRec
With rsOut
.AddNew
!REGION = rsIn.Fields("REGION")
!DISTRICT = rsIn.Fields("DISTRICT")
!STORE = rsIn.Fields("STORE")
'![Name_Full] = rsIn.Fields("Name_Full")
'!EMPLOYEE = rsIn.Fields("EMPLOYEE")
'!Trans = rsIn.Fields("Trans")
!Entries = rsIn.Fields("TotalEntries")
'!PPts = rsIn.Fields("PPts")
![DrawingNumber] = Rnd()
![DrawingDate] = Now()
.Update
End With
Next

.MoveNext
Loop

End With

rsIn.Close
rsOut.Close
dbs.Close

'NOTE: Setting up RS to learn how many drawing entries (i.e. table records) were just created
Set rst = CurrentDb.OpenRecordset("6000_DrawingEntries")
If rst.RecordCount = 0 Then
' the recordset is empty
rowcnt = 0
Else
rst.MoveLast
rst.MoveFirst
rowcnt = rst.RecordCount
End If

Me.CurrentStatus = _
"Complete: " & rowcnt & " drawing entries prepared" & vbCrLf & _
vbCrLf & _
Me.CurrentStatus
Me.Repaint

DoCmd.SetWarnings True
DoCmd.Hourglass False

 

=========
APPLIES TO / KEY WORDS
———
Microsoft Access
Contest Drawing
VBA

 

=========
REF
———
Many thanks to our friend and colleague George Schick on this one!

——— 
http://www.anysitesupport.com/vba-rnd-function-pick-a-random-number/

 


http://www.anysitesupport.com/vba-loop-to-create-a-table-with-the-number-of-records-specified-in-a-field-of-another-table/ 
http://anySiteHosting.com