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.

Ultimately, I want to count how many PKGID's are COMPLETE and PENDING.

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;