Sum of values from subform to table

[E9-Tech]

I am slowly getting to the bottom of my project but have hit a further rock!

I am trying to get the total of the field RIDOCR/RIDITSOR/RIDCPCR from the frmRuns into the relative fields on the frmDuties and copy that value in the table so that I can use it for statistic purposes later on.

In the sfrmRuns I have a field for the sum of all various values

=Sum([RIDOCR])

So I get the total for every run in the sfrmRuns, I then need to paste this value in the frmDuties

I used the code to transfer the value to the tblDuties, this does not seem to be working.

 Me.RIDOC.Value = Me.RIDOCDT

Also I have noted that the total in the form not be updated until I moved out from the record and I get to the next record in the sfrmRuns.

I believe I have chosen the wrong approach, is there a better option to my issue and also the total update to be responsive once I move off the field from the sfrmRuns?

I attached the sample DB I am trying to work on

Thanks.

 

[Pat Hartman]

Storing calculated values violates normal forms and therefore is never recommended. However, if the calculated values are static and will NEVER, EVER change, then storing them shouldn't do any damage. Use an update query to calculate the sums and append the records to the history table. No code is required.

 

[June7]

Saving calculated data, especially aggregates, is usually a bad idea. Use an aggregate (GROUP BY) query to calculate sums when needed.

Correct, total will not update until data is committed to table and moving to another record triggers that - as does code to saved record.

Pat, did you mean an INSERT query? Perhaps an INSERT INTO SELECT?

 

[Pat Hartman]

I did mean Insert. Thanks. As I said, storing calculated data isn't recommended but if it is static - which it almost never is which is always the problem - then storing it can save processing for other tasks.

I'll say it again. If the underlying data can change - DO NOT STORE the calculated results. PERIOD.

 

[E9-Tech]

Hi @Pat Hartman @June7 I am a novel with access so I may need some more direction on this, the values on sfrmRun once entered they do not change at all so I would believe they are static (if this is the meaning of static).
What is the best approach, and how do I go with the INSERT query?

 

[June7]

Why would you say the sums are static if you have an Edit functionality on the form that allows adding records or edit existing that will change the sums? At what point do the records become "unchangeable"?

Why such a huge form?

 

[arnelgp]

You do not need those 3 remaining fields from duty table since you can Calculate them on a query.

 

[E9-Tech]

Thanks @arnelgp for the solution, is there a way to have the immediate response on the form, I noticed that the result does not appear when you add the record but only when you have closed the form and reopen it.
How do I go for a more prompt visual information on the main form frmDuties whilst is open?
I understand that a calculated value should never be stored in a table, but moving the project to the next level and having to look at some statistic if I can work with data stored in one table only might be easier for me to find the solution.

 

[E9-Tech]

Hi @June7 I have very limited knowledge of access and DB and have built this with the help from the community and every suggestion is greatly appreciated.
The huge form is because I deleted some fields to upload a smaller file onto this site.

Just to give an insight of the DB for every daily duty there are various 'task' which are tracked in the subform with a 'one to many relation'.
Ultimately I need the total of all RIDOC, RIDITSO and RIDCPC in the main tblDuties as having the total in one table I can work out some statistics (I know that calculated queries can be done with multiple tables but again with my limited knowledge I may find it easier to make it as simple as possible.

I could redesign the form and just add the total manually in the frmDuties, but I would like to be able to track the breakdown for every task, hence having the fields in the sfrmRuns, getting a total and adding it up.

Hope this makes a bit more sense from how I look at things.

 

[arnelgp]

I noticed that the result does not appear when you add the record

you need to Refresh the Parent form when you make changes to the subform.

 

[E9-Tech]

@arnelgp thanks for the help, just learned something new with refreshing forms!

The solutions works perfectly!

Now to add the cherry to the cake I would really like to have the total value copied to the tblDuties, I understand this is against the logic of programmers and you may cringe with this, but this would help me a lot!

Thanks again.

 

[Gasman]

I will load your gun for you.

Use an update query.

 

[GPGeorge]

@arnelgp thanks for the help, just learned something new with refreshing forms!

The solutions works perfectly!

Now to add the cherry to the cake I would really like to have the total value copied to the tblDuties, I understand this is against the logic of programmers and you may cringe with this, but this would help me a lot!

Thanks again.

"Against the logic of programmers" is not quite the reason for discouraging people from storing calculated values in tables.

The point is that doing so creates a risk of data anomalies, also known as "bad data". The whole point of relational database design is to minimize the risk of data anomalies.

It's against the logic of providing your users with valid, reliable data to store calculated values, with a few exceptions, of course.

 

[E9-Tech]

I will load your gun for you.

Use an update query.

ahahah I am sure there will be a few here reading my post happy to load that gun and wait for me to fall over my logic

Now I have no clue how an update query is achieved to update one record......

 

[plog]

...I am trying to get the total of the field RIDOCR/RIDITSOR/RIDCPCR from the frmRuns into the relative fields on the frmDuties and copy that value in the table...
... if I can work with data stored in one table...
...The huge form is because I deleted some fields to upload a smaller file onto this site...
... I would really like to have the total value copied to the tblDuties,

Every post you've made in this thread contains at least one huge red flag that you've not set up your tables correctly.

In a relational database data doesn't get moved hither and yon. It doesn't have calculations done to it in one spot and stored in another. It doesn't get stored in multiple fields named specifically after the data within it.

In a relational database data is stored in a normalized table structure. Achieving that is called 'Data Normalization':

Database normalization - Wikipedia

en.m.wikipedia.org

Give that a read, Google a few tutorials and try to apply those concepts to your database. Once your data is properly structured you then use queries to do calculations on your data and you reference those queries instead of storing the calculated values or moving data to duplicate places.

The issues you are posting about should not be issues in a relational database

 

[Gasman]

ahahah I am sure there will be a few here reading my post happy to load that gun and wait for me to fall over my logic

Now I have no clue how an update query is achieved to update one record......

You use criteria to identify the one record.
Try googling 'Access update query'
You will likely need to concatenate the sql syntax with your control values to select the correct record.
I am with everyone else on not doing this though. That value you seek can be easily obtained with a simple aggregate query.