Update Totals on Related Tables (1 Viewer)

[The_Doc_Man]

Part of the problem is your fixation that the total needs to be in the table. No, it doesn't. (Yes, I know it is YOUR design and not mine, but bear with me.)

Everything you do with Access should be centered around recordsets. These can come from either tables or queries. Rather than store totals in a table, you compute them in a query when you need to see them. You can open the query in the same place as you could open the table. BUT... the aggregate field cannot be updated. And it SHOULDN'T be.

If this field is to represent the count or total of something that is in another table, nobody should be able to edit that to make it anything other than the count or total. I.e. if you store it and allow it to be edited later (you said "edit all fields..."), then it has no meaning because Joe Schmuckatelli can come in and bollix up that table totally so that it has no relationship to reality.

Stated another way, the records in the Initiatives table represent real things that can be added or removed from consideration. That, I understand. But fields representing COUNTS or TOTALS of real things are NOT real things. They are meta-data, statistical data, about those real things. As they are descriptive and intended to reflect reality, they should NEVER be editable. They can be changed by changing the table on which they are based but should never be changed by the man on the street. EVER.

 

[fedebrin]

Good day CJ, The Doc Man,

Apologize if I haven't been clear enough, when I wrote "Edit All fields" the Total field shouldn't be one... let me rephrase it "all fields with the exception of the Total which should be calculated from each of the Initiatives"

And I do not need to have the total updated on the ACTIONS table but we do need to have visibility in a form.

See below and example of the ACTIONS DS form, as you see we have multiple Actions and we are missing the total from the initiatives within each action... and also we would like to be able to edit fields shown below

These actions have multiple initiatives each with an amount, see expanded view of the same ACTIONS DS form.

thank you for your patience!

 

[GK in the UK]

I'm not sure if you can get a total to display on your form when it shows many records from Actions.

Consider designing a new main form for Actions which opens only a single record from the table.
On this form you add controls (text fields) for the fields from Actions Table that you want to edit.

Then add a sub form datasheet with the related records in Initiatives.

In the sub form for related records in the InitiativesTable, you add a header and footer. You add a control in the footer with the controlsource like post #14. eg. txtActionTotal = sum([InitiativeTotal])

However the subform header and footer won't be visible, I believe that's how it is in Access when you display a sub form datasheet.
So to show the total on the parent form you need another control (text field) with the controlsource set to the name of the control in the sub form footer.

Since it counts as a different form, you can re-use the name, so you can have a text box on the parent form also called txtActionTotal with the control source set to =Me!Subform1.Form!txtActionTotal (where subform1 is whatever name you give to your subform)

ActionTotal isn't saved anywhere in your tables but you can always see the calculated value when you open the main form.

 

[fedebrin]

Thanks KG

Yes, I believe I will have to settle with 2 different forms:

1) that will be regular with single records and with the Total for its initiatives
2) I will still keep a Datasheet Form as I show above but this one will not display this total

I will try option 1 above and let you know

thanks all