Solved

Update Query

Posted on 2015-01-15
9
120 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

708 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

16 Experts available now in Live!

Get 1:1 Help Now