Solved

Update Query

Posted on 2015-01-15
9
121 Views
Last Modified: 2015-01-16
I am trying to use an update query and it's giving me an error "Operation must use an updateable query"  and I think it's the way the relationship is set for the two tables..is there a work around??
0
Comment
Question by:Jass Saini
  • 4
  • 3
  • 2
9 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 40552361
Without more detail we cannot help but here is what Microsoft has to say on this error

Possible causes:

You attempted to run a query that tried to update a field that cannot be updated. For example, you may have created the query in such a way that you tried to update a field on the one side of a one-to-many relationship.

You tried to use the obsolete OpenQueryDef method on a query that is in a database opened for read-only access.

The database is read only

http://office.microsoft.com/en-au/access-help/HV080760740.aspx
0
 

Author Comment

by:Jass Saini
ID: 40552397
Yea..I did the help thing too...But I can't change the relationship of my two tables..that's what the whole DB is built on!!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40552398
Does your query aggregate data?  Sum, Group, Distinct, etc.
Do both of the tables have primary keys?
Are you joining on  PK to FK or data field to data field ?
Show us your schema.  Show us your query.
0
 

Author Comment

by:Jass Saini
ID: 40552413
No no query aggregate data....Both tables have primary keys

Here is the query that I used and I was converting it into an Update query

SELECT DISTINCT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Line Item], Final_Table.[Item Number], Final_Table.[Total Initial], Final_Table.BC1Chng1, Final_Table.ADJ1Ch1, Final_Table.Remarks1, Final_Table.BC1Chng2, Final_Table.ADJ1Ch2, Final_Table.Remarks2, Final_Table.BC1Chng3, Final_Table.ADJ1Ch3, Final_Table.Remarks3, Final_Table.BC1Chng4, Final_Table.ADJ1Ch4, Final_Table.Remarks4, Final_Table.BC1Chng5, Final_Table.ADJ1Ch5, Final_Table.Remarks5, Final_Table.BC1Chng6, Final_Table.ADJ1Ch6, Final_Table.Remarks6, Final_Table.BC1Chng7, Final_Table.ADJ1Ch7, Final_Table.Remarks7, Final_Table.BC1Chng8, Final_Table.ADJ1Ch8, Final_Table.Remarks8, Final_Table.BC1Chng9, Final_Table.ADJ1Ch9, Final_Table.Remarks9, Final_Table.BC1Chng10, Final_Table.ADJ1Ch10, Final_Table.Remarks10, Final_Table.UpdatedBy, Final_Table.UpdatedDate
FROM Final_Table INNER JOIN dbo_tblOrgLook_master ON (Final_Table.PEC = dbo_tblOrgLook_master.PEC) AND (Final_Table.CostCen = dbo_tblOrgLook_master.CostCenter)
WHERE (((dbo_tblOrgLook_master.Analyst) In ('C.King')) AND ((dbo_tblOrgLook_master.Org) In ('4605')) AND ((dbo_tblOrgLook_master.CostCenter) In ('0655800000')) AND ((dbo_tblOrgLook_master.Fund) In ('15G0010000')) AND ((dbo_tblOrgLook_master.PEC) In ('1010007')))
ORDER BY Final_Table.[Item Number];

Open in new window



And here is my Update query

UPDATE Allot_Q SET Allot_Q.Remarks1 = [Forms]![fsub_Final_Table Query1]![Remarks1];

Open in new window

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40552512
DISTINCT - does aggregate so you need to remove it.

I see you are still trying to update multiple records with the same remarks field.  Have you given any thought (I also asked in one of your other threads) as to what you are going to do if someone changes one of them or changes the original?  I told you in the other thread how to do this with a join.

In addition to duplicating these remarks, I see you have several repeating groups which also violate first normal form.  You are making excessive work for yourself by not properly normalizing the schema.  Essentially, you have created a spreadsheet and are calling it a database.  You are also still using spreadsheet techniques such as duplicating data and using repeating groups.  That's the way spreadsheets work - just add a column!  Unfortunately, that always affects formulas and ranges, etc and leads to work to get everything working again.  You are propagating that same unnecessary work into your Access application and it is so unnecessary.
0
 

Author Comment

by:Jass Saini
ID: 40552530
This is my very DB...I am trying my best with the tools I have.  Isnt Access a big speadsheet of records?  I am sure a lot of people start out this and perfect as they go along!  You had said that my table was not normalized..It's a table with the essential fields.  Like I said I am still new at this and I have my boss breathing down my neck!  I am doing the best I can with no guidance.  I am sure there are easier ways...but until I learn how ...this is the only way I know!
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40552547
One of the key ideas with relational databases is to avoid data duplication.

Normalisation is the process of identifying redundant data and ensuring data dependances make sense.
http://databases.about.com/od/specificproducts/a/normalization.htm

You may want to have a look at this tutorial
http://holowczak.com/database-normalization/
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40552759
Access isn't a big spreadsheet.  It is a relational database.  Spreadsheets are flat files and quite different to design and use.  I understand you might be under pressure but as a former manager was fond of saying -- "if you don't have time to do it right, what makes you think you have time to do it twice?"  You should probably come clean with the boss and tell him that you don't know Access as well as you thought you did.  The most important part of developing an app is designing the schema.  If you get the tables and relationships correctly defined, building the app will be much easier.

One of the things you will run into with your repeating groups is that relational databases (of which Access is one, along with SQL Server, Oracle, and DB2 just to name a few) have no support for them since they violate the basic principles of database design.  So when you go looking for functions to work with them, you won't find any.  You might want to take some time to study normalization before continuing.  It doesn't matter what database they are using although, you'll be more comfortable watching them do things with the Access interface.  But in reality, the concepts of normalization have nothing to do with Access in particular, they apply to relational databases in general.

Did you remove the Distinct predicate yet?
0
 

Author Comment

by:Jass Saini
ID: 40553704
Thanks for the pep talk....
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

947 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