Ok, i have tried various things, including Lebans RowNumber, but the best I can do is a small vba proc as below.
I took your sample data and imported it into Access in a table called OrdersAndSKU
My proc writes out the fields in the Order record and the LineItem.
You could write this to a table. I wasn't able to do it with a query.
Here's the output
1001,1234,1,2039 abc drive dallas tx,
1
1002,9876,1,2040 abc drive dallas tx,
1
1003,1234,1,2041 abc drive Dallas tx,
1
1003,1235,1,2041 abc drive Dallas tx,
2
1003,8765,1,2041 abc drive Dallas tx,
3
Here's the procedure
Code:
'---------------------------------------------------------------------------------------
' Procedure : OrderLineItems
' Author : Jack
' Date : 23/08/2012
' Purpose : To add a sequnce number (called LineItem) to records. The objective is to
' show sequential numbers for multiple records with the same Order_Number
'---------------------------------------------------------------------------------------
'
Sub OrderLineItems()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim LineItem As Integer
10 On Error GoTo OrderLineItems_Error
20 LineItem = 0
Dim HOrder As String
30 Set db = CurrentDb
40 Set rs = db.OpenRecordset("Select * from OrdersandSKU order by 1,2", , dbReadOnly)
50 HOrder = rs!order_number
60 Do While Not rs.EOF
70 If HOrder = rs!order_number Then
80 LineItem = LineItem + 1
90 Else
100 HOrder = rs!order_number
110 LineItem = 1
120 End If
130 Debug.Print rs!order_number & "," & rs!SKU & "," & rs!quantity & "," & rs![Shipping Address] & "," & LineItem
140 rs.MoveNext
150 Loop
160 On Error GoTo 0
170 Exit Sub
OrderLineItems_Error:
180 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure OrderLineItems of Module AWF_Related"
End Sub
Windows 7 64bit 
Access 2010 32bit
Reply With Quote
Originally Posted by orange
