Select Query will not make changes to database

[sblanche]

When I do my other "Select" queries and make changes to the data from the queries my table data is changed. However the query below will not allow me to make changes. The data from the query is correct but can't make changes to it. One post said to change Record Set type to "dynaset" or "dynaset (inconsistent updates) and neither worked. Otherwise I have to go to 200 individual records and make the changes. Any help would be most appreciated-Thank you--


SELECT TBLOCCMain.Year, TBLOCCMain.OCCNumber, Min(TBLLinkCounDate.[In OCC]) AS [MinOfIn OCC], TBLOCCMain.AssignType
FROM TBLOCCMain LEFT JOIN TBLLinkCounDate ON TBLOCCMain.OCCNumber = TBLLinkCounDate.OCCNumber
GROUP BY TBLOCCMain.Year, TBLOCCMain.OCCNumber, TBLOCCMain.AssignType
HAVING (((TBLOCCMain.Year)<>"FY06") AND ((Min(TBLLinkCounDate.[In OCC])) Between #10/1/2005# And #9/30/2006#) AND ((TBLOCCMain.AssignType)="c"));

 

[randy700]

If I remember right, any query containing aggregate functions is non-updatable.


Randy

 

[dhookom]

Randy is correct. You might be able to remove the TBLLinkCounDate and use DMin() in its place.

Duane
Hook'D on Access
MS Access MVP

 

[FledglingAnalyst]

I'm not an Access MVP, but I've solved a similar problem in my own experiences by creating a table with the results of the query, modifying the data on the new table, and running an update query from the new table to modify the original table. Make sure to back up your data.

There's probably a better way to do this, but it will save you from accessing the 200 records individually.

 

[dhookom]

FledglingAnalyst's suggest will often work and be quite efficient. The OCCNumber field in the "created" table should be a primary key. You might want to use a permanent table that has records deleted and then appended so you have the index.

Duane
Hook'D on Access
MS Access MVP