Solved

Update Query

Posted on 2015-01-15
9
123 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 35

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 
LVL 35

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 35

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

813 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

13 Experts available now in Live!

Get 1:1 Help Now