DSum Confusion

[vcs]

I have a subform where we are entering data entries for each date. This
subform is mapped to a PrsnID to get all the data entries in the subform
"Quality_Entries" and the MainForm is "Employees". There is a field in the
"Quality_Entries" table where a number is entered in the "TotalReports"
field.

Am I able to create a running sum of the "TotalReports" entered each time a
new record is entered? So, as of today my "TotalReports" are 10. Then my
entry for tomorrow will add my new entry to the Running Total from the
previous day. Example: if I enter 5 today then my running total is now 15.
Is this where I use a DSum function? If so, I can't get it to work properly
and need some guidance.

Thank you.

 

[Marshall Barton]

I have a subform where we are entering data entries for each date. This
subform is mapped to a PrsnID to get all the data entries in the subform
"Quality_Entries" and the MainForm is "Employees". There is a field in the
"Quality_Entries" table where a number is entered in the "TotalReports"
field.

Am I able to create a running sum of the "TotalReports" entered each time a
new record is entered? So, as of today my "TotalReports" are 10. Then my
entry for tomorrow will add my new entry to the Running Total from the
previous day. Example: if I enter 5 today then my running total is now 15.
Is this where I use a DSum function? If so, I can't get it to work properly
and need some guidance.

You never said what DSum you are trying to use. I would
expect something like this to work (slowly):
=DSum("TotalReports", "Quality_Entries", "datefield >= " &
Format(Me.datefield, "\#yyyy-m-d\#"))

Generally a running total is not all that useful, its the
grand total that's important. If the above is irritatingly
slow, and it probably will be when there are a lot of
records, now or in the future, I recommend doing away with
the running total.

 

[Chegu Tom]

If you are counting the number of records then you probably want to use
Dcount() instead

There is usually no need to write the total to a table. make a query of
that table with a computed field = to the sum you want

 

[garvic82]

OK, thank you. I understand and it is subject to change if I need to have
it. But, what if I want to increment these so the previous day's running
total stays and then adds on to the next record and so on.

Like

EntryID Date Reports RunningTotal
1 1/12/09 25 25
2 1/13/09 17 42
3 1/15/09 9 51

etc.

Would this be an AfterUpdate Event I would need in the Reports field? Or
will a DSum work? I tried this one you gave me and I get the overall running
total on all the entry records.

 

[Marshall Barton]

OK, thank you. I understand and it is subject to change if I need to have
it. But, what if I want to increment these so the previous day's running
total stays and then adds on to the next record and so on.

EntryID Date Reports RunningTotal
1 1/12/09 25 25
2 1/13/09 17 42
3 1/15/09 9 51

etc.

Would this be an AfterUpdate Event I would need in the Reports field? Or
will a DSum work? I tried this one you gave me and I get the overall running
total on all the entry records.

Storing calculated values back to a table is a really bad
thing to do. (How would you propose to fix things if
someone comes along and say they made a mistake last month
an need to add two more reports to the 12/15 entry?)

Ignoring the performance issue, you can get a running total
for just the records in the form by using:
=DSum("TotalReports", "Quality_Entries", "datefield
Between " & Format(Min(datefield), "\#yyyy-m-d\#") & " And "
& Format(datefield, "\#yyyy-m-d\#"))