How do I add two table values and publish to a third table?

[Prellyan]

I have value to add two values from two tables and publish results to a third
table.

ie:

(v1 in table A) + (v2 in table B) = (v3 in table C)

Is there a quick way to do this.

Thanks

 

[tina]

publish results to a third table.

do you mean *store the calculated value in the third table* ? well, normally
you wouldn't. a standard rule is that you don't store calculated values when
you're already storing the raw numbers; you just recalculate the values
whenever you need to display them (in a query, form, or report).

there are exceptions to every rule, of course, so if you have a valid need
to store a calculated value in a table: you can easily calculate a total in
a query. add both tables to the query, then create a calculated field with
the A + B expression (leave out the "= C" part). make it an Append query if
you need to add the calculated value to the table as a new record. make it
an Update query if you need to add the value to a field in an existing
record in the table.

hth

 

[Tim Ferguson]

(v1 in table A) + (v2 in table B) = (v3 in table C)

Is there a quick way to do this.

Which v1, v2 and v3 values? There is one of each for every row in the
tables, so if you have 1000 rows in each table there are a billion ways
of answering this! At the moment, all you have described is that you want
to add NumberOfPassengers to PencePerKilogramme and store the answer in
DaysSinceDischarge.

Presumably you do have some way of joining all three tables together:
once you have done that, then the addition is a simple UPDATE query. Post
more information if you need further help.

B Wishes


Tim F

 

[Prellyan]

Yes i know about the report extraction from raw data using query - basically
i was trying to ensure that query result data was available "real time" as a
table subform under an input form

ie - main form = user data
subform inside main form = additions
subform2 inside main form = subtractions
subform3 inside main form = SUM(additions + subtractions)

Needed is the ability to add to subform2 and subform3 live with results
published to subform3

Does this make it clearer?

Chris

 

[tina]

well, if you're summing a specific field of a specific record from one table
with a specific field of a specific related record in another table, you
still don't need to store that value in a third table. simply link the two
tables in a query, creating a calculated field that adds the two fields.
then bind that query to your third subform.

hth

 

[Prellyan]

Tina,

Yes thanks - silly me - subform query - for some reason i though i could not
do that even though had done it previously - dont know why

Thanks again

Chris