2

I would be grateful for some help, please, in understanding why the below query returns the "Operation must use an updateable query" message:

UPDATE QC_Costs
SET    QC_Costs.No_Makes = (SELECT Count(Temp_Tbl.Unique_ID) AS MakesCount
                            FROM   (SELECT GSR.Unique_ID,
                                           GSR.Month_Name,
                                           GSR.Year_No
                                    FROM   GSR
                                           INNER JOIN Products
                                                   ON Products.Unique_ID = GSR.Product_ID
                                    WHERE  ( ( ( Products.Item_category ) = "MAKE" )
                                             AND ( ( GSR.Transaction_Type ) = "Invoice" ) )) AS Temp_Tbl
                            WHERE  ( ( ( Temp_Tbl.Month_Name ) = QC_Costs.Month_Name )
                                     AND ( ( Temp_Tbl.Year_No ) = QC_Costs.Year_No ) ));

I have read numerous articles that this message appears when permissions to update the database are not set correctly. I have tried to make those changes to the folder permissions (although everything already looked fine), and what I don't fully understand is if I write a different query where I simply want to make the No_Makes field in the QC_Costs table a 1, the query runs fine. This leads me to believe the issue is in my count query or sub-query, but when I turn this section of the query into a SELECT query and run it on its own it returns a result.

I am not an expert at all - I have only tried to work with sub-queries today for the first time - so I would really appreciate some help.

For context - I have a table full of invoices and credit notes (the GSR). Another table called Products contains a list of products that has an Item_Category field. The GSR is linked to Products via a Product_ID field. A third table called QC_Costs has a record for each month and a field called No_Makes. I want to capture the number of invoices for MAKE products for a given month in the GSR and update the No_Makes field in the QC_Costs table with that number. This number will be used in another query I am yet to write or may add to this one where an amount of costs is divided by No_Makes and then this value is posted back into the GSR against each of these records.

I hope the above makes sense - please let me know if I need to provide anything further.

Thank you

EDIT:

In response to Parfait's answers I have written the following and it runs - thank you:

UPDATE QC_Costs 
SET QC_Costs.QC_Cost_Allocation = 
DLookup("SumOfQC_Costs_To_Allocate", "Sum_Of_QC_Costs","Month_Name = " & [QC_Costs].[Month_Name] & " AND Year_No = " & [QC_Costs].[Year_No]) / 
DLookUp("CountofUniqueID","Count_Of_Makes","Month_Name = " & [QC_Costs].[Month_Name] & " AND Year_No = " & [QC_Costs].[Year_No]);

I am really close but I get a message for every record where this will execute that there is a type conversion failure. The data type of the destination field is Double, and I have made sure the denominator Dlookup query isn't a zero so the answer to this division should be a numeric decimal, so can anyone explain to me how to troubleshoot what's wrong, please?

I'm now off the question topic so I apologise for that - please let me know if I have to repost as a new question.

Thanks

Improve this question

1 Answer 1

Reset to default
1

MS Access update queries must not be read-only in order to be run. It does not have to do with proper permissions. Aggregate queries are not updateable which you are attempting to assign to No_Makes with COUNT().

Consider DLookup function which externally retrieves your value from a saved query:

Select Query (save as stored query object)

SELECT Count(GSR.Unique_ID) AS CountUniqueID,
       GSR.Month_Name,
       GSR.Year_No
FROM   GSR
INNER JOIN Products
     ON Products.Unique_ID = GSR.Product_ID
WHERE (((Products.Item_category) = 'MAKE')
  AND  ((GSR.Transaction_Type) = 'Invoice'))
GROUP BY GSR.Month_Name,
         GSR.Year_No

Update Query

UPDATE QC_Costs
SET    QC_Costs.No_Makes = DLookup("CountUniqueID", "mySavedQuery", 
                                   "Month_Name='" & QC_Costs.Month_Name & "' 
                                    AND Year_No = " & QC_Costs.Year_No)

Aside - it's often said that one should not save computations or calculations in database tables as they can easily be retrieved from queries for reporting. Instead, use table storage for raw values.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Thank you so much. I’ll study this and see if I can get it to work. On your final point - sorry but would you explain what you mean please? What’s table storage as opposed to what I’m doing here?
Understood. For final point, you are using table resources to save calculations which can be achieved via queries of the tables. This is a bit redundant. When you need such calculations simply query the tables.
If I was to say that initially I wanted to nest this query within the main query that allocated the costs would that be doing what you mean? I broke it down only because it was getting a bit too complicated for me, but I agree saving the number somewhere isn’t great. I agree - getting that count on the fly is what I really wanted to do. Thank you again for helping me.
I know what you are attempting to do. It's no the how but the why question, COUNT() from group by aggregate queries are inexpensive to run. But again, this is just a best practice note. Let me know if soluion worked as needed.
Ok will do. I’m out now so haven’t looked at this properly yet but one last thing has come to mind - how do you create stored query objects in Access? Will I have to do this through VBA or is it something on the menu bar? Thanks again.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.