Solved

Update on Access?

Posted on 2012-03-12
8
227 Views
Last Modified: 2012-03-13
Hi,

I'm trying to do an update on access, but I keep getting "Operation must use an updateable query"

I have full access to the table that I'm trying to do updates on, and on some REAL simple updates, it seems to go through.  

Basically I have a table A that contains client info (name, etc.. and also updateuser, updatedate)
Table B contains the clientname, updateuser, rundate
I first try to find the latest run dates of each clients so I have a query called C:
SELECT a.*
FROM B as a, (SELECT clientname, max(rundate) as latest_date FROM B GROUP BY clientname) as F
WHERE a.clientName = F.clientName and a.rundate = f.latest_date

So basically I'm just trying to update the table to include the latest user and the date...  
I believe the issue has to do with me trying to update from a query that includes grouping...  Any idea how to get around this?  Thanks!



I want to update the
0
Comment
Question by:iamnamja
8 Comments
 

Expert Comment

by:gnetgnet
ID: 37711953
It is the join it doesnt like. Can you find the keys and after that update the row with the data you want.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37712004
see this link for reasons and possible solution

http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37712010
0
 

Author Comment

by:iamnamja
ID: 37712029
Ok...

so according to: http://allenbrowne.com/ser-61.html

It's a read only when there's a group by...  But it has group by on the other table that i'm trying to take values from and not on the table that i'm trying to update.

Why would this cause an issue?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 30

Expert Comment

by:hnasr
ID: 37712190
Supply an example demonstrating the problem.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 37713569
'It's a read only when there's a group by...  But it has group by on the other table that i'm trying to take values from and not on the table that i'm trying to update.'

I'm afraid that even though your logic is fine, it doesn't alter reality.
Any query involving a GroupBy anywhere in its structure is regarded as non-updateable by Access.

You can use a DMax() function instead of the subquery as the update value.
0
 
LVL 84
ID: 37713836
Can you post the SQL of your Update query here?
0
 

Author Closing Comment

by:iamnamja
ID: 37715380
Thanks all, I have decided to go by dmax which seems to work fine.  Thanks.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now