Operation must use an updateable query

[Groundrush]

I'm trying to create an update query that finds all records with the value of 4 then replace them with the value of 3.

The problem I have is that this query is linked to two tables & is has a one to many relationship.

To try & solve this problem I created another query to hide the duplicates using DISTINCT after the SELECT

then using my update query I linked the main table with the 2nd qry hoping that it would work but I get the same message.

query 1

SELECT DISTINCT dbo_Project.ProjectRef, dbo_Project.PROJECTSTATUSREF, dbo_vw_KPI_PAYMENT_CUBE.Contractor_Status
FROM dbo_Project RIGHT JOIN dbo_vw_KPI_PAYMENT_CUBE ON dbo_Project.Project_number = dbo_vw_KPI_PAYMENT_CUBE.Project_Number
WHERE (((dbo_Project.PROJECTSTATUSREF)=4) AND ((dbo_vw_KPI_PAYMENT_CUBE.Contractor_Status)="Trade Operative" Or (dbo_vw_KPI_PAYMENT_CUBE.Contractor_Status)="External Contractor"));

query2

UPDATE qryAssignedUpdate1 LEFT JOIN dbo_Project ON qryAssignedUpdate1.ProjectRef = dbo_Project.ProjectRef SET dbo_Project.PROJECTSTATUSREF = 3
WHERE (((dbo_Project.PROJECTSTATUSREF)=4));

Any ideas anyone how to achieve this?

thanks

 

[RuralGuy]

See if this link helps at all.

 

[Groundrush]

See if this link helps at all.

Thanks Rural.... I'm must be missing something here & I can't work out what it is.

 

[Rabbie]

You are not allowed to use Distinct in an updateable query.

 

[Groundrush]

You are not allowed to use Distinct in an updateable query.

I used Distinct in the first query to hide the duplicates

the second query then updates the main table, or alteast it's supposed to.

 

[Rabbie]

I used Distinct in the first query to hide the duplicates

the second query then updates the main table, or alteast it's supposed to.

If you base a query on a non-updateable query then it too will not be updatable. That's why the second table can't update the table.

 

[Groundrush]

How else do I do this?

I can update the table if I only use one table in the query but because I need to filter out the records that need updating I have to link it to another one

Then because there are duplicates in one of the tables I created a qry to hide them.

 

[Rabbie]

You need to find a way of identifying the records that need updating without using "Distinct".

One possible way would be to store the output from your first query in a temporary table and use that in your update query.

 

[Groundrush]

See if this link helps at all.

UPDATE dbo_Project SET dbo_Project.PROJECTSTATUSREF = 3
WHERE (((dbo_Project.PROJECTSTATUSREF) In (Select ProjectStatusRef FROM qryAssignedUpdate1)));

Tried this method & it seems to work on a test database but it's extremely slow

 

[DreamGenius]

Having used search, I'm deliberately adding to this thread because it isn't yet marked as solved and my issue is similar.

I'm trying to update a Linked Table (being an Excel Spreadsheet) to set an Imported field to TRUE where I've successfully imported that record into the database.

The Excel file asks the user to provide real names for objects and an Append query, using joins to other tables, imports that data into the normalised database.

In trying to join back to the Linked Table to update the field, I keep getting the error message above, because the fields necessary to facilitate the join are not in one table in the database, so I'm using either a sub-query or joined tables.

I've tried both methods and neither works, giving the same error. Here are queries I've tried:

UPDATE xlsNewRequests INNER JOIN
(tblProgrammes INNER JOIN tblBookings 
ON (tblBookings.ProgramID = tblProgrammes.ProgramID) 
AND (tblProgrammes.ProgramID = tblBookings.ProgramID)) 
ON (tblBookings.AssociateID = xlsNewRequests.[Associate Number]) 
AND (xlsNewRequests.Programme = tblProgrammes.Programme) 
SET xlsNewRequests.Imported = "TRUE";

UPDATE xlsNewRequests SET xlsNewRequests.Imported = "TRUE"
WHERE xlsNewRequests.Programme & xlsNewRequests.[Associate Number] IN (
SELECT tblProgrammes.Programme & tblBookings.AssociateID
FROM tblProgrammes INNER JOIN tblBookings
ON tblProgrammes.ProgramID = tblBookings.ProgramID
WHERE tblBookings.UpdatedBy = Date());

I'm now at a loss. All I want to do is add a flag to say whether a record in the Excel spreadsheet has been successfully imported today.

 

[gemma-the-husky]

you can do it without the distinct

it will look as if there are loads of rows, but the access query will manage it.

if you do it by hand, and change a value form 3 to 4, you will see everything change instantly for all the related/duplicate rows


----------
the other way, is to take the non-updateable query, and iterate it as a recordset, manually creating and exceuting sql update statements for each row - not particularly tricky for just 1 field, either - although finding a way to use an updateable query is ALWAYS going to be less risky. - access definitely wont get it wrong - we might!

 

[DreamGenius]

Dave

Thanks but my challenge isn't the use of Distinct so much as the subquery comprising two joined tables because of database normalisation.

I've even tried creating a temporary table to link to but, because I'm linking on two fields I think, it's still failing.

This is frustrating because all the client wants is a simple flag telling them whether the Excel record has been imported or not!