Update Query Problem

[Din Zavery]

Hello Everyone,

I'm trying to UPDATE a field in a table from a sum of fields from another
table. The field is called YTDBusiness in Customer Table. I need a total/sum
from a field called FareAgreed in Trip Table to reflect in Customer table.

Both the tables are related and have customerid as a common field in both.
The SQL statement i'm trying to run is as below but all I get are zeros
rather than the sum of each customers fareagreed total reflecting in
customer table.

UPDATE Customer INNER JOIN Trip ON Customer.CustomerID = Trip.CustomerID SET
Customer.YTDBusiness = Sum([Trip].[FareAgreed])
WHERE (([Customer].[CustomerId]=[Trip].[CustomerID]));

All the help will be appreciated

Thanks and Regards
Din Zavery

 

[John Spencer]

In Access that will never work. You should be getting an error message
that the query must be updateable. You can use the VBA aggregate
function DSum.



UPDATE Customer
SET Customer.YTDBusiness =
DSum("FareAgreed","Trip","CustomerID=" & Customer.CustomerID)

That assumes that customerId is a numeric field.

That said, why are you attempting to store the sum of the values at all.
You should be getting the value as a calculated value when you need
it and not storing it.

 

[Fakhruddin Zavery]

Thanks John

In Access that will never work. You should be getting an error message
that the query must be updateable. You can use the VBA aggregate function
DSum.



UPDATE Customer
SET Customer.YTDBusiness =
DSum("FareAgreed","Trip","CustomerID=" & Customer.CustomerID)

That assumes that customerId is a numeric field.

That said, why are you attempting to store the sum of the values at all.
You should be getting the value as a calculated value when you need it and
not storing it.

Hello Everyone,

I'm trying to UPDATE a field in a table from a sum of fields from another
table. The field is called YTDBusiness in Customer Table. I need a
total/sum
from a field called FareAgreed in Trip Table to reflect in Customer
table.

Both the tables are related and have customerid as a common field in
both.
The SQL statement i'm trying to run is as below but all I get are zeros
rather than the sum of each customers fareagreed total reflecting in
customer table.

UPDATE Customer INNER JOIN Trip ON Customer.CustomerID = Trip.CustomerID
SET
Customer.YTDBusiness = Sum([Trip].[FareAgreed])
WHERE (([Customer].[CustomerId]=[Trip].[CustomerID]));

All the help will be appreciated

Thanks and Regards
Din Zavery