Solved

Updat query error due to lock violations

Posted on 2009-05-20
9
635 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
  • 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24432461
is [Calculate next call date] a query?
post the SQL here
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

757 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

20 Experts available now in Live!

Get 1:1 Help Now