Solved

Updat query error due to lock violations

Posted on 2009-05-20
9
637 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
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…

777 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