Cummulative Sum for Month

  • Thread starter Thread starter Gator
  • Start date Start date
G

Gator

I have a columnar form that basically displays a record from a table. There
is a date field in each record. I want to add a textbox or label that shows
a cummulative sum of a number field, for the month, of the month that is
displayed in the current record. For example, a record might show 7/4/08 as
the date. I want something on the form that will show the sum of a field for
all the records in the table for month of July 2008. If I goto the next
record with a date of 8/3/08 then the sum for August 2008. How do I code
this?
thanks
 
Add an unbound textbox to your form and set its control source property to

=DSum("[Your Number Field]","Your Table Name","Year([Date Field]) = " &
Year([Date Field on Form]) & " And Month([Date Field]) = " & Month([Date
Field on Form]))
 
Dennis' method should work, but if you have a lot of rows in the form, it
might tak a while to run.

Another alternative might be to rewrite the query that your form is based
on, something like:

SELECT T1.Field1, T1.Field2, T1.Field3, SUM(T2.YourNumberField) as MonthTotal
FROM yourTable T1
INNER JOIN yourTable T2
ON Year(T1.YourDateField) = Year(T2.YourDateField)
AND Month(T1.YourDateField) = Month(T2.YourDateField)
GROUP BY T1.Field1, T1.Field2, T1.Field3

Do you really need to display this value with each record, or could you just
compute that value for the currently selected record, and display it in the
forms header or footer? This would probably be a lot quicker.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Dennis said:
Add an unbound textbox to your form and set its control source property to

=DSum("[Your Number Field]","Your Table Name","Year([Date Field]) = " &
Year([Date Field on Form]) & " And Month([Date Field]) = " & Month([Date
Field on Form]))

Gator said:
I have a columnar form that basically displays a record from a table. There
is a date field in each record. I want to add a textbox or label that shows
a cummulative sum of a number field, for the month, of the month that is
displayed in the current record. For example, a record might show 7/4/08 as
the date. I want something on the form that will show the sum of a field for
all the records in the table for month of July 2008. If I goto the next
record with a date of 8/3/08 then the sum for August 2008. How do I code
this?
thanks
Click to expand...
Click to expand...
 
Back
Top