Update all but two records per name

[Guest]

I have a table with data like so:

AniName EnriName AssignID
beagle toy 1
beagle toy 2
beagle toy 3
ostrich toy 4
ostrich toy 5
ostrich toy 6
ostrich toy 7

For beagle, I would like to update all but two of the "toy" to "enviro" (so,
in beagle's case, that would be any *one* of the three entries). For ostrich,
I would like to update all but two of the "toy" to "enviro" (so, in ostrich's
case, that would be any *two* of the four entries). For each animal, I want
to maintain the same number of records, but have only two of the records
remain "toy".

AniName and EnriName are text fields. AssignID is an autonumber field. There
are 45 different Aninames with a varying number of records per name in the
table. But for each animal, I want to maintain the same number of records it
has now, but keep only two of them the same and update the rest to "enviro".
Any ideas?

 

[Guest]

Hi

What if there is only 1 record? - Update it?
What if there are only 2 records? - Update both?

The following will update the above so will need to be modified if you
shouldn't update in the above cases.

Step 1: Backup your table in case following doesn't have desired effect

Step 2: Create and run this make-table query...

SELECT AniName, First(AssignID) AS FirstOfAssignID, Last(AssignID) AS
LastOfAssignID
INTO tblFirstLast
FROM [your_table]
GROUP BY AniName;

Step 3: Create and run this update query...

UPDATE [your_table] INNER JOIN tblFirstLast ON [your_table].AniName =
tblFirstLast.AniName
SET [your_table].EnriName = "enviro"
WHERE ([your_table].AssignID = [FirstOfAssignID])
Or ([your_table].AssignID = [LastOfAssignID])


Regards

Andy Hull

 

[John Spencer]

The following query should work. TEST IT on a copy of your data OR backup
your data first (or be paranoid and do both).

UPDATE [YourTable]
SET AniName = "Enviro"
WHERE AssignID Not IN
(SELECT TOP 2 AssignID
FROM YourTable As T
WHERE T.AniName = [YourTable].AniName
ORDER BY AssignID)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..