Update Query won't update

[Clddleopard]

I have the following query:
UPDATE EnrichmentAssignments INNER JOIN Query6 ON
(EnrichmentAssignments.[Enrichment Type] = Query6.[Enrichment Type]) AND
(EnrichmentAssignments.[Animal Name] = Query6.[Animal Name]) SET
EnrichmentAssignments.[Enrichment Type] = "Toy"
WHERE (((Query6.MinOfAssignmentID) Is Null)) OR
(((EnrichmentAssignments.AssignmentID)<>[MinOfAssignmentID] And
(EnrichmentAssignments.AssignmentID)<>[MaxOfAssignmentID]));


Query6 has the following SQL:
SELECT [Find duplicates for EnrichmentAssignments].[Animal Name Field],
EnrichmentAssignments.[Animal Name], EnrichmentAssignments.[Enrichment Type],
Max(EnrichmentAssignments.AssignmentID) AS MaxOfAssignmentID,
Min(EnrichmentAssignments.AssignmentID) AS MinOfAssignmentID
FROM EnrichmentAssignments INNER JOIN [Find duplicates for
EnrichmentAssignments] ON EnrichmentAssignments.[Animal Name]=[Find
duplicates for EnrichmentAssignments].[Animal Name Field]
GROUP BY [Find duplicates for EnrichmentAssignments].[Animal Name Field],
EnrichmentAssignments.[Animal Name], EnrichmentAssignments.[Enrichment Type]
HAVING ((Not ([Find duplicates for EnrichmentAssignments].[Animal Name
Field]) Is Null) AND ((EnrichmentAssignments.[Enrichment Type]) Like
"enviro*"));

The first sql won't update (it says Operation must use an updateable query).
What I have is a table with the fields Enrichment Type, Animal Name, Give
Date, and Assignment ID. If an Animal Name has five records with Enrichment
Type "enviro", I want to change all but two of them to "toy". Or, if an
Animal Name has 6 records with Enrichment Type "enviro", I want to change 4
of them (all but two) to toy.That's what my sql is trying to do. The top
query selects exactly what I want (when it is a select query), but then it
won't let me update. I am not writing sql, I'm using the design query grid in
Access. If anyone can tell me what to change in the sql I've got or has a
better way of doing what I want, I'd be very grateful.

 

[John W. Vinson]

The first sql won't update (it says Operation must use an updateable query).

The fact that Query6 is a Totals query is the problem. No Totals query, nor
any query including a Totals query, is ever updateable; this is an
(annoying!!) Access restriction.

You may be able to replace Query6 with a DMax() and a DMin() function call for
the maximum and minimum. Since I'm not sure what [Find duplicates for
EnrichmentAssignments] is, nor is it clear just how your table is structured,
I'm not able to write the function for you - but it would be of the form
DMin("Assignments", "Queryname", "<criteria>"

where the criteria would be your JOIN clause.

John W. Vinson [MVP]