We help IT Professionals succeed at work.

Update on Access?

iamnamja
iamnamja asked
on
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
Comment
Watch Question

Commented:
It is the join it doesnt like. Can you find the keys and after that update the row with the data you want.
BRONZE EXPERT
Top Expert 2016

Commented:
see this link for reasons and possible solution

http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx
BRONZE EXPERT
Top Expert 2016

Commented:

Author

Commented:
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?
Hamed NasrRetired IT Professional
BRONZE EXPERT

Commented:
Supply an example demonstrating the problem.
BRONZE EXPERT
Commented:
'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.
Scott McDaniel (EE MVE )Infotrakker Software
SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Can you post the SQL of your Update query here?

Author

Commented:
Thanks all, I have decided to go by dmax which seems to work fine.  Thanks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.