Purchase Orders and lines - how to make the sorting of 1 field dependent upon another

[DBCamden]

I have a database of purchase orders
Many of the purchase orders have a revision No against them, eg:-

Purchase Order No / Line No / Revision No / Value
1001 / 001 / 00 / £50
1001 / 001 / 01 / £100
1001 / 001 / 02 / £200




I am trying to find a way to write a query that will show me the actual value of the final revised value of each Purchase order line ie: the answer to above is

1001 / 001 / 02 / £200

Could anyone please help?

 

[neileg]

If you combine the Purchase Order No, Line No, Revision No , eg 1001 / 001 / 02 / becomes 1001001002, then use Max on that field, you will get the latest revision.

 

[stopher]

If you want to list the latest revision for all PO/Line combinations then try this:

SELECT PONo, LineNo, RevNo, Value
FROM myPOtable
WHERE RevNo=DMax("RevNo","myPOtable","PONo='" & [PONo] & "' and LineNo='" & [LineNo] & "'")

I've assumed that both PONo and LineNo are strings.

Stopher