add rows together into single record

[delphi6BDE]

I have a stupid calculated values question.. I know it's bad to store calculated values, but I have a need to. I have built an application in Delphi using access as the database that updates values in a record from a text file daily.
Each row in the text file updates the database in the corresponding record without issues. However, three values must be the sum of the related area the belong to
My table structure is:
tblArea
-------
areaID
strArea

tblTruck
--------
truckID
strTruck
lngValue

strArea | strTruck | lngvalue
-----------------------------
1 | 1 | 34
1 | 2 | 16
1 | 99 | 50 <-- this should be the total of the previous two records

How do I wrote a query to do this? The 'total value' is needed later for reporting and other functions that cannot be done any other way. Currently I manually change these totals daily and it's getting to be annoying

 

[PHV]

How is related tblTruck to tblArea ?

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

 

[SkipVought]

Aso, LOGICALLY, how do you know 1) what the previous rows are and 2) how many to aggregate in every instance and 3) is that all, just 3 rows in this join?

In other words, your source information is very scant.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[delphi6BDE]

PHV: sorry, tblTruck is related via areaID which I forgot.

SkipVought: That's just it, I don't know what the previous rows are except that they can be grouped by the areaID. Each area has a different amount of trucks. Think of an area like a city; New York is much bigger than Great Falls, thus there are more trucks At least I think it's bigger, I'm Canadian . I thought if somebody was able to help I would modify their example query to my needs.

I was able to get this going though by making a temporary table to store the results, and then building code to:
1) empty the temp table
2) get the total for the area group
3) append the total into the temp table with the corresponding area
4) zero out all of the trucks that are getting added together
5) update tblTruck with the total on the right row
6) move to the next area

It works, but it was tedious to get there, so I'm still open to any suggestions. I don't like my method because if any more areas are added that need this consideration I have to modify the code

 

[PHV]

5) update tblTruck with the total on the right row
What is the right row ?

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

 

[delphi6BDE]

Fair question. In my original example, truck 99 is the right row, so my append query into the temp table rolls up the various trucks in the area into 99, then after updating all of them to 0, I update truck 99 with the total

 

[PHV]

Something like this ?
UPDATE tblTruck T SET lngValue = (
SELECT Sum(lngValue) FROM tblTruck WHERE areaID=T.areaID AND strTruck<>99)
WHERE strTruck = 99

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