Access Update query

[polettima]

Hello I need help with an update query

I got 2 ms Access tables.

-Table_A
Fields:
ID - FatherID - Price - Quantity

-Table_B
Fields:
ID - Price

I have to update Table_B.Price With the sum of Table_A.Price GROUP BY Table_A.FatherID , With the condition that Table_A.FatherID = Table_B.ID.


The query must be something like (I know that isn't correct):
UPDATE Table_B AS t2
INNER JOIN Table_A AS t1 ON (t1.FatherID = t2.ID)
SET t2.Price = (SELECT SUM (Round(((t1.Price * t1.Quantity)),2)) FROM Table_A AS t1 GROUP BY t1.FatherID )

Thank you

 

[PWise]

how about

UPDATE Table_B
INNER JOIN (
SELECT FatherID ,SUM (Round(((t1.Price * t1.Quantity)),2)) as Pricesumed
FROM Table_A
GROUP BY t1.FatherID
)AS t1
ON (t1.FatherID = Table_B.ID)
SET t2.Price = t1.Pricesumed

 

[polettima]

Thank you, but i got an error message if i try to execute your query.

 

[PWise]

where do you get the error

try
UPDATE Table_B
INNER JOIN (
SELECT FatherID ,SUM (Round(((t1.Price * t1.Quantity)),2)) as Pricesumed
FROM Table_A
GROUP BY t1.FatherID
)AS t1
ON (t1.FatherID = Table_B.ID)
SET Table_B.Price = t1.Pricesumed



what does this give

SELECT FatherID ,SUM (Round(((t1.Price * t1.Quantity)),2)) as Pricesumed
FROM Table_A
GROUP BY t1.FatherID

 

[polettima]

Both of the query (the update and the select) returns the same error:
(i translate from italian): 'Impossible to execute a query that doesn't have "fatherId" as part of an aggregation'

Usually you resolve this problem using HAVING at the end of the query, in this case HAVING FatherID , but it doensn't work.

The select query that extracts the value that i need to update is:

SELECT t2.ID, SUM (Round(((t1.Price * t1.Quantity)),2)) as sum FROM Table_B AS t2 INNER JOIN Table_A AS t1 ON (t1.FatherId = t2.ID)
GROUP BY t1.fatherId, ID

Executing this query i got:
first column: the id of the product to update, second column: the value to put in the table_b.price field.

Thank you

 

[PHV]

Why not simply this ?
UPDATE Table_B AS t2
SET t2.Price = (SELECT SUM(Round(t1.Price * t1.Quantity,2)) FROM Table_A AS t1 WHERE t1.FatherID = t2.ID)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[polettima]

@ PHV

I've already tryed that kind of query , but access returns me the error message (translated from italian): "for the operation it's necessary an updatable query "

(i work locally and i have all the permissions on the .mbd file)

 

[PHV]

And this ?
UPDATE Table_B
SET Price=Round(DSum("Price * Quantity","Table_A","FatherID=" & [ID]),2)
WHERE ID IN (SELECT FatherID FROM Table_A

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[polettima]

@ PHV

The query runs, now I'll check the output (many lines to check), but I thank you so much.

 

[PHV]

Sorry for the missing closing parenthesis ...

 

[polettima]

No problem for the parenthesis.
The query returns me some cast problem and doesn't update the table. Now I'll try to solve.

 

[polettima]

The query is now ok, only a wrong space error. Thanks