Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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
0
iamnamja
Asked:
iamnamja
1 Solution
 
gnetgnetCommented:
It is the join it doesnt like. Can you find the keys and after that update the row with the data you want.
0
 
Rey Obrero (Capricorn1)Commented:
see this link for reasons and possible solution

http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx
0
 
Rey Obrero (Capricorn1)Commented:
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
iamnamjaAuthor 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?
0
 
hnasrCommented:
Supply an example demonstrating the problem.
0
 
peter57rCommented:
'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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you post the SQL of your Update query here?
0
 
iamnamjaAuthor Commented:
Thanks all, I have decided to go by dmax which seems to work fine.  Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now