search records in a recordset

[supind]

I need to search for duplicate fields in a recordset then set another field in the duplicate set to null. I cannot seem to make anything I try work. Any help would be welcomed!

Here is what I need:

Table: TBL_ProdScrap
Fiedls: CastingPN
CastGood
FinMachGood

What I need is to search the recordset for matching CastingPN's. Based on that result, I need to compare the FinMachGood in each of the matching CastingPN fields, then whichever has less in FinMachGood I need that records CastGood field set to NULL. I hope I have clearly explained what I need.

Thank you!

 

[PHV]

Create a saved query named, say, qryMinFinMach:
SELECT CastingPN, Min(FinMachGood) AS MinFinMach
FROM TBL_ProdScrap
GROUP BY CastingPN
HAVING Count(*) > 1;
And now the update query:
UPDATE TBL_ProdScrap T INNER JOIN qryMinFinMach Q
ON T.CastingPN = Q.CastingPN AND T.FinMachGood = Q.MinFinMach
SET CastGood = Null;

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

 

[supind]

Thank you for your help PHV. I really appreciate it. I had no problem with the first query, but the second one is giving me problems. I keep getting the error "Join expression not supported." At one point I tried something different and I got the error that I couldnt run the query because it was not updatable.

This is what I have that is giving me the first error:

UPDATE TBL_ProdScrap INNER JOIN QRY_MinFinMach
ON TBL_ProdScrap.CastingPN = QRY_MinFinMach.CastingPN AND TBL_ProdScrap.FinMachGood = QRY_MinFinMach
SET TBL_ProdScrap.CastGood = Null;

 

[PHV]

UPDATE TBL_ProdScrap INNER JOIN QRY_MinFinMach
ON TBL_ProdScrap.CastingPN = QRY_MinFinMach.CastingPN AND TBL_ProdScrap.FinMachGood = QRY_MinFinMach[highlight].MinFinMach[/highlight]
SET TBL_ProdScrap.CastGood = Null;

If access choke on this query not being updatable you may consider converting QRY_MinFinMach into a maketable query and join the update to the newly created table.

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

 

[supind]

I got it to work. Thank you so much!! Now I can stop banging my head on the wall. At least until the next problem arises. ONce again, thank you!