IF then else to compare rows

[Poco_90]

I hope someone can point me in the right direction. Not really sure what sort of function I need.

I have a table with order numbers and po numbers. Some of the order numbers can be matched to one or multiple po numbers. I am trying to group orders with the same Order number and po number to the one order number, like below. Any idea on my best route to accomplish this?
Thanks in advance.

OrderNo PO New OrderNo
12345 PO127 12345-1
12345 PO127 12345-1
12345 PO136 12345-2
67543 PO334 67543-1
19221 PO2345 19221-1
19221 PO2345 19221-1
19221 PO3456 19331-2

 

[June7]

You want to append a sequence number to the OrderNo?

Query1:

SELECT DISTINCT OrderNo, PO FROM tablename;

Query2:

SELECT Query1.OrderNo, Query1.PO, [OrderNo] & "-" & DCount("*","Query1","OrderNo=" & [OrderNo] & " AND PO<'" & [PO] & "'")+1 AS GrpSeq
FROM Query1
ORDER BY Query1.OrderNo, Query1.PO;

Query3:

SELECT Table1.OrderNo, Table1.PO, Query2.GrpSeq
FROM Query2 INNER JOIN Table1 ON (Query2.PO = Table1.PO) AND (Query2.OrderNo = Table1.OrderNo);

 

[Poco_90]

I do and increment the number if the po number changes.

 

[June7]

See previous post edited probably after you read it.

Alternative would be VBA procedure to write new value to field.

 

[Poco_90]

Hi June7,
Thanks for taking the time to help. Just wondering about query1, it is not displaying all my records. What seems to happen is if there are 2 or more rows with the same po the query only shows one result.
Regards and thanks,
Poco

Table
PO OrderNo
PO P1/1022 11266
PO P1/1238 11266
PO P1/1238 11266
PO P1/302 11266
PO P1/391 11266
PO P1/391 11266
PO P1/491 11266
PO P1/642 11266
PO P1/732 11266
PO P1/832 11266

Query1
PO OrderNo
PO P1/832 11266
PO P1/732 11266
PO P1/642 11266
PO P1/491 11266
PO P1/391 11266
PO P1/302 11266
PO P1/1238 11266
PO P1/1022 11266

 

[June7]

Query1 is not supposed to show all records. It is just step 1 to get to Query3.

Build all 3 queries before you question the results.

 

[Poco_90]

My bad.

June7 you are a star. Thanks you so much for your help with this.