CODEWITHSUNDEEP
ms-access
Stefano
Stefano

Reputation: 199

Access: how to write update from query?

In my table Progetti I need to update the field Eroso with the query Query2.

The value to put on Progetti.Eroso has to be taken from the query [Fatture Query] where [Fatture Query].[Codice Progetto] = Progetti.[Codice Progetto].

The problem is that the SQL that I am using in Query2 could be wrong because I don't have the expected result: the system asks to insert [Fatture Query].Sum. The code in Query2 is the following.

UPDATE Progetti SET Progetti.Eroso = [Fatture Query].Sum;

EDIT

This is the SQL code of [Fatture Query]:

SELECT Fatture.[Codice Progetto], Sum(Fatture.Fattura) AS [Sum]
FROM Fatture
GROUP BY Fatture.[Codice Progetto];

enter image description here

enter image description here

enter image description here

Upvotes: 0

Views: 72

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

Since your query Fatture Query uses an aggregate function (SUM) any query which references this query is no longer updateable, as there is an inherent one-to-many relationship as soon as the records are aggregated.

As such, I believe you will need to use a function such as DSum to calculate the appropriate value for each record to be updated, e.g.:

UPDATE
    Progetti 
SET 
    Progetti.Eroso = DSUM("Fattura", "Fatture", "[Codice Progetto] = '" & [Codice Progetto] & "'")

If instead the query from which you are sourcing the values had not used an aggregate function (e.g. SUM, MIN, MAX etc.) then I would have suggested incorporating an INNER JOIN in your UPDATE query such that the values used to update the target field are sourced from the appropriate records in your Fatture Query query, e.g.:

UPDATE
    Progetti 
    INNER JOIN 
    [Fatture Query]
    ON
    Progetti.[Codice Progetto] = [Fatture Query].[Codice Progetto]
SET
    Progetti.Eroso = [Fatture Query].Sum

Upvotes: 1

Elizabeth Ham
Elizabeth Ham

Reputation: 196

I think the problem is that your update query doesn't specify how the rows in [Fatture Query] match the rows in [Progretti] so the system doesn't know which row to look at. Try using a WHERE clause like this:

    UPDATE Progetti SET Progetti.Eroso = [Fatture Query].Sum WHERE [Fatture Query].[Codice Progetto] = Progetti.[Codice Progetto];

Upvotes: 1

Related Questions