Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select Query will not make changes to database

Status
Not open for further replies.

sblanche

Technical User
Jun 19, 2002
95
US
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"));
 

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


Randy
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top