Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Updat query error due to lock violations

Posted on 2009-05-20
9
Medium Priority
?
648 Views
Last Modified: 2012-05-07
i have a query that should update TABLE District 3 total list1 with data from
QUERY Calculate next call date.

The query will run, then give me an error that none of the updates were made due to lock violations. Can anyone help me with this?

Thanks!! Beth
UPDATE [District 3 total list1] INNER JOIN [Calculate next call date] ON [District 3 total list1].[Facility ID] = [Calculate next call date].[Facility ID] SET [District 3 total list1].NEXTCALL = [Calculate next call date].[Next Call Date], [District 3 total list1].[CC Band] = [Calculate next call date].[New CC Band];

Open in new window

0
Comment
Question by:ba_trainer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 

Author Comment

by:ba_trainer
ID: 24432299
The Calculate next call date uses fields from District 3 total list1 for its calculations. Could this be the problem? How would I avoid the table locking?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24432322
check if you have the same Data Type for the fields updated

--------------------vvvvvvv------------------------------vvvvvvvvvv
District 3 total list1].NEXTCALL = [Calculate next call date].[Next Call Date],
[District 3 total list1].[CC Band] = [Calculate next call date].[New CC Band]
0
 

Author Comment

by:ba_trainer
ID: 24432429
I did check data types first, they seem fine.

The error is" Microsoft Office didn't update 0 file(s) due to a type conversion failure, 0 record(s) due to key violations, 4517 record(s) due to lock volations, and 0 record(s) due to validation rule violations".

Does that help?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24432461
is [Calculate next call date] a query?
post the SQL here
0
 

Author Comment

by:ba_trainer
ID: 24432743
Calculate next call date query:
SELECT [District 3 total list1].[Facility ID], [LASTCALLED]+(367/[CC Band]) AS [New Call Date]
FROM [District 3 total list1];

Update last call in the current call cycle query:
UPDATE [District 3 total list1] INNER JOIN [Calculate next call date] ON [District 3 total list1].[Facility ID] = [Calculate next call date].[Facility ID] SET [District 3 total list1].NEXTCALL = [Calculate next call date].[New Call Date];
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24433332
normally, you don't store calculated values to the table, where you can always extract the values using a query.

0
 

Author Comment

by:ba_trainer
ID: 24433461
I need to update the table with values that are calculated in the query. I don't know any other way to do it. Can you suggest something?

I need to get the values FROM the table, do the calculations, then UPDATE the table with the new calculations.?

Thanks!!!
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 24433512
try this update query

UPDATE [District 3 total list1]
SET NEXTCALL = [LASTCALLED]+(367/[CC Band])
0
 

Author Closing Comment

by:ba_trainer
ID: 31583535
Wonderful!! Thanks a million!!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

604 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