Create duplicate rows based on Qty field

[applestoapples]

Example I have a table that has item #ABC and the qty field has 10

I need this to then repeat the same line 10 times. How can I get an Access Query to do that?

 

[Andrzejek]

There may be an issue with your data base design.
Why would you need 11 records in a table with the same data...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[applestoapples]

I need to have labels printed for each sku ordered. I have a larger list of these skus ordered so to get it to print the right amount of labels this is my best scenario. Some skus also have more than 1 label.

so if I have

sku QTY
ABC 5
DEF 8
LMN 3

it need to print this many labels for each. Suggestions?

 

[Andrzejek]

By 'labels', do you mean labels in MS Word?

So, if you have a record:[pre]
sku QTY
XYZ 6[/pre]
you will get the above 6 labels with the information for XYZ?

Or am I missing your point...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[applestoapples]

I have Access pointing to a .png file that loads through bartendar and using a Sato label printer.

So If I have 10 different skus with 10 different quantities in my table , I need to print the qty of each sku without printing them separately - how do I do that?

Can someone help me create separate rows based on a qty # in a row?

 

[Andrzejek]

How about a little Sub where you can just pass your sku:

not tested[/ignore]]
Private Sub InsertRecords(ByRef strSKU As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intR As Integer

Set dbs = CurrentDb

strSQL = "SELECT QTY From tblMyTable Where sku = '" & strSKU & "'"

Set rst = dbs.OpenRecordset(strSQL)
If rst.EOF = True Then Exit Sub

For intR = 1 To rst!QTY.Value
    strSQL = "Insert Into tblMyTable (sku, QTY) Values ('" & strSKU & "', " & rst!QTY.Value & ")"
    dbs.Execute(strSQL)
Next intR

rst.Close
Set rst = Nothing

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

I use a numbers table with a single numeric field and values from 1 to 999999. Then create a query with your table and tblNumbers. Don’t join the tables. Set the criteria under the Num field to <= [Qty]. This will create virtual records as desired.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Hello!?! Anybody here...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson