asked on
Update on Access?
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
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
Microsoft Access
Last Comment
iamnamja
It is the join it doesnt like. Can you find the keys and after that update the row with the data you want.
see this link for reasons and possible solution
http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx
http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx
see this too
http://allenbrowne.com/ser-61.html
http://allenbrowne.com/ser-61.html
ASKER
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?
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?
Supply an example demonstrating the problem.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Can you post the SQL of your Update query here?
ASKER
Thanks all, I have decided to go by dmax which seems to work fine. Thanks.