[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Update Query

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
Jass Saini
Asked:
Jass Saini
  • 4
  • 3
  • 2
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
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
 
Jass SainiAuthor Commented:
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
 
PatHartmanCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jass SainiAuthor Commented:
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
 
PatHartmanCommented:
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
 
Jass SainiAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
PatHartmanCommented:
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
 
Jass SainiAuthor Commented:
Thanks for the pep talk....
0

Featured Post

Independent Software Vendors: 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!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now