Retrieve data with same values from different rows 2

[antonycat]

I am needing to create order references for data that we are then going to pass into 3rd party software. So the structure of the order reference has to be in a specific format for their software.

Here is some example data from the Access table 'Transactions' and the order reference that I need to create but I do not know how to do this either using update queries or VBA. I know very little with regards VBA.

The problem I have is when I have a 2 rows of order data where the Type = P and F, the order reference for both rows needs to be the same except 'S' at the end for Type = P and 'F' at the end for Type = F. So I was thinking of using the TransID and SourceID fields to link the 2 pairs together. I am wanting to use the OrderID and LineNo fields to create the Order Reference.

OrderID TransID LineNo StopID SourceID Type Reference
102 743 6 13 D 000102006
102 744 7 12 B 000102007
102 745 8 48 P 000102008S
102 746 9 98 745 F 000102008F
102 747 10 48 P 000102010S
102 748 11 67 747 F 000102010F

After I have created the Order References, I then need to update the SourceID to the corresponding StopID from the row, where TransID = SourceID.

Thanking you in advance.

Regards
Antony

 

[MajP]

This works for your data, but I am not sure for all cases. Place this function in a module, and then you could use it in a query in a calculated field

Public Function fncReference(theOrderID As Integer, theLineNo As Integer, theType As String) as string
  If theType = "F" Then
    theLineNo = theLineNo - 1
  End If
  fncReference = Format(theOrderID, "000000") & Format(theLineNo, "000")
  If theType = "P" Then
    fncReference = fncReference & "S"
  ElseIf theType = "F" Then
    fncReference = fncReference & "F"
  End If
End Function

 

[antonycat]

MajP

Thanks for the start but the corresponding F type may not be the next line so I was wanting to use the TransID and SourceID fields to link the 2 corresponding 'P' and 'F'type orders but obviously the linked data is on different rows.

Thank you in advance.

Regards
Antony

 

[PHV]

And what about a pure SQL SQL way ?
SELECT A.OrderID, A.TransID, A.LineNo, A.StopID
, Nz(B.StopID, A.SourceID) AS SourceID, A.Type
, Format(1000*A.OrderID+Nz(B.LineNo,A.LineNo),'000000000') & IIf(A.Type='P','S',IIf(A.Type='F','F','')) As Reference
FROM Transactions AS A LEFT JOIN Transactions AS B ON A.OrderID = B.OrderID AND A.SourceID = B.TransID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[antonycat]

Thanks PHV

It worked a treat.