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 Vinson]

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.

Actually, it's very likely that you DON'T want or need to store this
value.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

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]));

No SUM query - or other Totals query - is ever updateable, even if (as
in this case) it appears that it should be. If you want to store this
redundant sum, and are willing to risk the stored value being WRONG,
and to explain to the tax auditor why you used that wrong sum anyway,
you can use the DSum() function:

UPDATE Customer
SET Customer.YTDBusiness = DSum("[FareAgreed]", "[Trip]",
"[CustomerID] = " & Customer.CustomerID & " AND <some date field> >
DateSerial(Year(Date()), 1, 1)");

assuming that you in fact want a year-to-date sum from the Trip table.

Note again - unless you run this query every time you add a record to
Trip, the value stored in Customer WILL BE WRONG. That's the price you
pay for storing data redundantly.

John W. Vinson[MVP]