Locating Duplicates and Flagging in another column

[chazgaz]

I have a query which produces two columns called PKGID and ERPNO. The ERPNO field includes either a number or blank. The PKGID includes a number which can be duplicated numerous times. I have added a column called STATUS in which I have built an IF formula into that looks at the ERPNO column and if there is a value it populates "COMPLETE" and if it is blank it populates "PENDING". The problem I have is that there are some duplicate PKGID values that could have "COMPLETE" and "PENDING" values in the STATUS column I created. What I am trying to do is add another column to my query result that looks at all "PENDING" values in the STATUS column and if there is any duplicate PKGID value that has "COMPLETE" in the status column I would like to populate "IN PROGRESS" and if not leave as "PENDING". For all items already marked as "COMPLETE" in my existing STATUS column, I would like those to remain as COMPLETE in my new column.

SCWOOO...does that make any sense?

Here is my SQL view
SELECT Queue_Workflow_Lookup.WORKFLOW, [Berlex Pkg View].PKGID, [Additional Accounts].ERPNO, IIf([Additional Accounts]![ERPNO] Is Null,"PENDING","COMPLETE") AS STATUS
FROM (Queue_Workflow_Lookup INNER JOIN [Berlex Pkg View] ON Queue_Workflow_Lookup.QUEUE = [Berlex Pkg View].QUEUE) INNER JOIN [Additional Accounts] ON [Berlex Pkg View].PKGID = [Additional Accounts].PRIMENO;

 

[PHV]

You may try something like this:
SELECT Q.WORKFLOW, B.PKGID, A.ERPNO,
IIf(IsNull(A.ERPNO), IIf(0<(SELECT Count(*) FROM [Additional Accounts] AS X WHERE X.PRIMENO=A.PRIMENO AND X.ERPNO Is Not Null), "IN PROGRESS", "PENDING"), "COMPLETE") AS STATUS
FROM (Queue_Workflow_Lookup AS Q INNER JOIN [Berlex Pkg View] AS B ON Q.QUEUE = B.QUEUE) INNER JOIN [Additional Accounts] AS A ON B.PKGID = A.PRIMENO;


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

 

[chazgaz]

WOW...It appears to work great. Thank you very much. I am very new to these types of queries and would have never come up with this.

Thank you