Query out a Duplicate entry based on criteria in another field

[jyellis]

How could I query this so that if the Wireless Numbers are duplicated as in the picture; then look at the PersonStatus and keep only the active?


Before someone asks, no I cannot just eliminate the Terminated unless there is a duplicate wireless number.


Thank you.

[Bulzie]

Maybe something like this: (change the name of table and fields as needed).

qryWirelessActive:
SELECT tblWireless.PersonStatus, tblWireless.WirelessNumber
FROM tblWireless
WHERE (((tblWireless.PersonStatus)="Active"));

qryWirelessTerminated:
SELECT tblWireless.PersonStatus, tblWireless.WirelessNumber
FROM tblWireless
WHERE (((tblWireless.PersonStatus)="Terminated"));

qryWirelessTerminatedNoActive
SELECT qryWirelessTerminated.PersonStatus, qryWirelessTerminated.WirelessNumber
FROM qryWirelessActive RIGHT JOIN qryWirelessTerminated ON qryWirelessActive.WirelessNumber = qryWirelessTerminated.WirelessNumber
WHERE (((qryWirelessActive.WirelessNumber) Is Null));

Now do a union query on qryWirelessActive and qryWirelessTerminatedNoActive

qryWirelessUnion:
SELECT tblWireless.PersonStatus, tblWireless.WirelessNumber
FROM tblWireless
WHERE (((tblWireless.PersonStatus)="Active"))
UNION ALL SELECT qryWirelessTerminated.PersonStatus, qryWirelessTerminated.WirelessNumber
FROM qryWirelessActive RIGHT JOIN qryWirelessTerminated ON qryWirelessActive.WirelessNumber = qryWirelessTerminated.WirelessNumber
WHERE (((qryWirelessActive.WirelessNumber) Is Null));


You can copy the code into the query designer in Access to see it in the grid, sometimes easier to read it that way.

[Micron]

Something like this would find the dupes (via the Find Duplicates Query Wizard)

SELECT Table2.[Wireless], Table2.[ID], Table2.[PersonStatus], Table2.[PersonW], Table2.[TotalCurrent]
FROM Table2
WHERE (((Table2.[Wireless]) In (SELECT [Wireless] FROM [Table2] As Tmp GROUP BY [Wireless] HAVING Count(*)>1 )))
ORDER BY Table2.[Wireless];

then this would delete from that query, thus deleting from your table as well

DELETE Table2Dupes.PersonStatus
FROM Table2Dupes
WHERE (((Table2Dupes.PersonStatus)<>"Active"));

You would only have to execute the second query, as it will call the first without it being open.

Always, and noted in my signature, test anyone's supplied code or solution on a copy of your db - especially when it involves deletion.

[Micron]

It was pointed out in another post https://www.accessforums.net/showthread.php?t=67321
where this might be done using a sub query. I thought of that, but I do struggle with those, not having wrapped my mind around their syntax. I repeat the link posted in that thread: http://allenbrowne.com/subquery-01.html and committed myself to devise a similar solution for you. Have to say it took me much longer than the first method I proposed, but I'll include it here as an alternative for you. It performed the same deletion on my test data as before, but there are no more records to deal with than what you posted. Perhaps it is not as discriminating as it seems to be. Again, the sql is not formatted for use in a form, but it should work in a query sql view as written, provided the fields a table names are a match for what you have.

DELETE Table2.[PersonStatus] FROM Table2 WHERE 
(((Table2.[PersonStatus])=(SELECT PersonStatus AS Tmp FROM [Table2] As Dupe 
WHERE (Dupe.PersonStatus <>"Active"))));