update table with sum from subform

J

Jenny

Hi There

I have a subform which calculates totals, but it does not store them in the
table on which the subform is based.

I'm sure the answer is easy as to why this is not happening, but I cannot
figure it out!!

All other information entered on the subform gets stored in the table - just
not the calculations.

Please can anyone help me.

Thanks a million
Jenny
 
J

Jeff Boyce

Jenny

It's probably a good thing...

If your table holds the values you need to use to calculate a total, you
generally DON'T want to include the total in the table (there are
exceptions, but, well, they're exceptions -- describe your situation further
to have the 'group offer opinions about whether you have an exception). It
is difficult to keep all the component fields AND the total in complete
synchronization.

If you need to use the total elsewhere (e.g., in a report), use a query that
calculates the total.

I would suspect that your form does not bind the "total" control to any
field, where the other controls are bound to fields in the table.
 
J

Jenny

Hi Jeff

Thanks very much for your reply. Actually my total control is bound to a
field (even though its not behaving as if it is - by assigning the value to
the field in the table). Does this make it an exception I wonder?!

There are many occasions where I need the totals to be displayed on a form
for the user to see. I also need the total information to be stored in a
table somewhere (so that other queries and reports can access it). Should I
create a new table for totals? Or perhaps store my totals in the table on
which the mainform is based?

Here is an overview of the structure

Table: Event (One)
Table: Invoice (to Many)

Form: Event
Subform:Invoice

so at the moment the user put the different costs into the invoice subform
to create total cost and therefore supply finance info to other areas of the
database (invoicing, forecasting, sales figures etc etc)

Unless I'm barking up a mad tree it would be much easier to store these
subtotals and totals in a table rather than have to write the formula every
time a query or report needs to include the totals....? Am I mad?! I'm
beginning to feel it!!!

Many thanks for your help
 
J

Jeff Boyce

Jenny

The "exceptions" I spoke of have to do with the data and relationships, not
the table structure. For example, if you needed to preserve a calculated
total for performance/lookup reasons, that might be an exception.

I'll say again, you probably do NOT need the total in the table. Create a
query against your table. Add an extra field in the query to calculate the
total. Now use the query as the source for your form.

You don't have to rewrite the formula/calculation each time, just use the
query as a source.

Or maybe I don't get it yet?!
 
J

Jenny

Thanks Jeff. I think you do get it, I am just approaching things in an
unusual (wrong!) way because I'm a novice!

Thanks for your help. I will do as you say with the query.

Happy Friday
 
V

Vincent Johns

Jenny said:
Thanks Jeff. I think you do get it, I am just approaching things in an
unusual (wrong!) way because I'm a novice!

Thanks for your help. I will do as you say with the query.

Happy Friday
Click to expand...

I posted an answer to your duplicate message -- just ignore it. (I
wouldn't have said anything if I'd seen Jeff Boyce's answer first.)

Concerning Queries, you can use a Query just about anywhere you could
use a Table, and, unlike a Table, a Query allows you to include
calculated fields. You can also set a Query to "Snapshot" type, making
it read only and thus protecting the underlying Table from accidental
changes.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Top