build table with multiple lines some duplicates

[chainedtodesk]

i have to create a pick list for an order system, and what i have to do as part of this is generate labels. some items have multiple quantities. so example is (ItemNbr, ItemDesc, Qty) 123456 SamplePart 3. i would like the query to dup that particular line into the label table 3 times, then move on to the next item and do the same based on whatever quantity was in there be it 1 or 100. so that at the end of the query when they select to run the labels off they would get the correct amount of labels. thanks

 

[dhookom]

I would create a table of numbers [tblNums] with a numeric field [Num] and values from 1 to 100. Add this table to your report's record source and don't join the table. Set the criteria under Qty to:
<=[Num]

This will create multiple records from one.




Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I usually use [red]cartesian product or CROSS JOIN[/red] for this type of situation rather than creating the duplicates... Cross join is not syntactically supported explicitly, instead you have to just list it like old ANSI-89 SQL.

Given some tables...

NumberId
1
2
3
4
5
6
...
Keep going until you hit your maximum labels possible... I'd use a for loop to populate

PartID PartDesc
1 Widget
2 Widget2


Order_Detail
ODID OrderID PartID QTY
1 1 1 1
2 1 2 2

Select Part.Part_Desc
From (Order_Detail Inner Join Part ON Order_Detail.Part_ID = Part.Part_ID)[red], [Numbers] [/red]
Where Numbers.NumberID <= Order_Detail.QTY

Oh... lucky you I have code to populate the numbers that I could actually find...

Function FillNumbers(ByVal lngMaxNeeded) As Boolean
    Dim lngNumAvailable As Long
    Dim lngLoop As Long
    Dim RS As DAO.Recordset
    On Error GoTo FillNumbers_err
    
    lngNumAvailable = Nz(DMax("NumberId", "Numbers"), 0)
    
    If lngNumAvailable < lngMaxNeeded Then
        Set RS = CurrentDb.OpenRecordset("Numbers")
        For lngLoop = (lngNumAvailable + 1) To lngMaxNeeded Step 1
            RS.AddNew
            RS!NumberId = lngLoop
            RS.Update
        Next
    End If
    FillNumbers = True

Exit Function
FillNumbers_err:
    MsgBox "Error " & Err.Number & " :" & Err.Description, vbCritical, "Error in FillNumbers"
    Err.Clear
    FillNumbers = False
End Function

 

[MajP]

they would get the correct amount of labels.

In the report you can use code to determine how many labels to print.

Dim countPrinted As Integer
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
 On Error Resume Next
 countPrinted = countPrinted + 1
 If countPrinted = Me!qty Then
   Me.NextRecord = True
   countPrinted = 0
 Else
  Me.NextRecord = False
 End If
End Sub

 

[chainedtodesk]

thanks all for your responses, i will try all suggestions a post which one works best for this application.

 

[lameid]

Duane and I have the same answer I just have an example but I have to say go with MajP's suggestion.

 

[MajP]

but I have to say go with MajP's suggestion

There are some problems with the approach I used. Allen Browne points out why you may want to go with the query method.

http://allenbrowne.com/ser-39.html

A common suggestion is to toggle NextRecord (a runtime property of the report) in the Format event of the Detail section.
This approach works if the user previews/prints all pages of the report. It fails if only some pages are previewed/printed: the events for the intervening pages do not fire, so the results are inconsistent.
This approach also fails in the new Report view in Access 2007 and later, since the events of the sections do not fire in this view.

A Better Solution
A simpler and code-free solution uses a query with a record for each label. To do this, you need a table containing a record from 1 to the largest number of labels you could ever need for any one record.
Create a new table, containing just one field named CountID, of type Number (Long Integer). Mark the field as the primary key (toolbar icon). Save the table as tblCount.
Enter the records into this table manually, or use the function below to enter 1000 records instantly.
Create a query that contains both this table and the table containing your data. If you see any line joining the two tables, delete it. It is the lack of a join that gives you a record for each combination. This is known as a Cartesian Product.
Drag tblCount.CountID into the query's output grid. Use the Criteria row beneath this field to specify the number of labels. For example, if your table has a field named Quantity, enter:
<= [Quantity]
or if you always want 16 labels, enter:
<= 16
Include the other fields you want, and save the query. Use it as the RecordSource for your label report.
Optional: To print "1 of 5" on the label, add a text box to the report, with this in its ControlSource:
=[CountID] & " of " & [Quantity]
Ensure the Name of this text box is different from your field names (e.g. it can't be named "CountID" or "Quantity"). To ensure the labels print in the correct order, include CountID in the report's Sorting And Grouping dialog.
That's it.