?
Solved

qry not updateable error

Posted on 2011-03-19
7
Medium Priority
?
238 Views
Last Modified: 2012-05-11
I have created a query using a find duplicates query based on [mvris code]. I want to update the variantdiff column to true. access wont let me and I am not sure how to achieve this
UPDATE TblSMMTDataDifferences RIGHT JOIN [Find duplicates for TblSMMTDataDifferences] ON TblSMMTDataDifferences.[MVRIS CODE] = [Find duplicates for TblSMMTDataDifferences].[MVRIS CODE Field] SET TblSMMTDataDifferences.variantDiff = True
WHERE ((([Find duplicates for TblSMMTDataDifferences].[MVRIS CODE Field]) Is Not Null));

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35170554
you can't JOIN at the UPDATE command
it must join at FROM clause

anyway, I think your code can be like this

UPDATE TblSMMTDataDifferences
SET variantDiff = True
WHERE NOT EXISTS (
	SELECT [MVRIS CODE Field]
	FROM [Find duplicates for TblSMMTDataDifferences] 
	WHERE TblSMMTDataDifferences.[MVRIS CODE] = [Find duplicates for TblSMMTDataDifferences].[MVRIS CODE Field]
)

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 35170755
its asking for a parameter TblSMMTDataDifferences.MVRIS CODE

something not quite right
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 2000 total points
ID: 35170868
Sorry, the one I given in for SQL
try this one, it should working on Access

UPDATE TblSMMTDataDifferences
SET variantDiff = True
WHERE [MVRIS CODE] NOT IN (
	SELECT [MVRIS CODE Field]
	FROM [Find duplicates for TblSMMTDataDifferences] 
)

Open in new window

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!

 

Author Comment

by:PeterBaileyUk
ID: 35171099
its working in a sense but the results are not correct its trying to update all the rows in the table
there are twelve [mvris code field] in the duplicate query which means there are 24 rows in the main query as the dups have only two rows each.
0
 

Author Comment

by:PeterBaileyUk
ID: 35171105
done it the sql should read in and not not in
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35171108
Sorry (again).
I mistaken your given query "IS NOT NULL" with "IS NULL"
that why my query become NOT IN, instead of IN.

Anyway, you already figure it out :)
0
 

Author Comment

by:PeterBaileyUk
ID: 35171109
amended version is:
UPDATE TblSMMTDataDifferences SET TblSMMTDataDifferences.variantDiff = True
WHERE (((TblSMMTDataDifferences.[MVRIS CODE]) In (SELECT [MVRIS CODE Field]
	FROM [Find duplicates for TblSMMTDataDifferences] 
)));

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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