Help with Duplicates Update Query

[Hanif Merali]

Hello, I'm having some trouble updating a particular field based on
the results of a duplicate query. I have a table which I am trying to
query for duplicate values in the ADDRESS field and the PCODE field,
there is also a CUSTNUM field and a DNP field. I wish to search for
duplicates and update the DNP field to =9 based on CUSTNUM field.

An example would be this

DNP CUSTNUM ADD1 PCODE .....
1 ABC 123
3 ABC 123
9 ABC 123

after the query was run I'd want it to look like this

DNP CUSTNUM ADD1 PCODE .....
9 1 ABC 123
9 3 ABC 123
9 ABC 123

right now I have the query updating all duplicates to 9 which is
useless, if anyone could be any advice as to how to do this it would
be greatly appreciated, I was thinking of exporting the data to excel
and writing a macro but I think that would get a bit complicated, let
me know.

Thanks in advance

Regards,
Hanif Merali

 

[John Spencer (MVP)]

UPDATE TheTable
Set DNP = DMax("CustNum","TheTable",
"Add1 =""" & TheTable.Add1 &
""" AND PCODE =""" & TheTable.PCode & """")
WHERE CustNum NOT IN
(SELECT MAX(CustNum)
FROM TheTable as Tmp
GROUP BY Add1, PCode)

That will probably be really slow. You might try this with multiple queries,
which may be faster

Mark the CustNum that ARE the Max for a specific address and Pcode
UPDATE theTable
SET DNP = 1
WHERE CustNum IN
(SELECT MAX(CustNum)
FROM TheTable as Tmp
GROUP BY Add1, PCode)

Mark all the DNP that aren't marked as 1
UPDATE TheTable
Set DNP = DMax("CustNum","TheTable",
"Add1 =""" & TheTable.Add1 &
""" AND PCODE =""" & TheTable.PCode & """")
WHERE DNP <> 1

Remove the mark from all those that are marked as 1
UPDATE TheTable
Set DNP = null
WHERE DNP = 1