calcuating Total-to-date

I have a field in a table that holds the number of title points earned
for that record tblAgilityRec.TitlePoint. Each record in tblShow has
many records in tblAgilityRec. On my form for tblShow, I have a button
that brings up a new form [fsubAgilityRec] that displays the
corresponding records contained in tblAgilityRec. On this subform, I
want to display a total-to-date for the field "Titlepoint." Do I do
this in the query for the subform, or do I put a textbox on the
subform? I tried putting in a text box with this property:
=Sum([TitlePoint]), but of course that does not work because it only
totals the titlepoint field for the records in tblAgilityRec that
correspond with tblShow - not ALL tblAgilityRecs. I also want it to
total records up to the date field appearing on that record (not
today's date). So if the agility record is dated 2/25/06, I want a
running sum of the field "titlepoint" for all records contained in
tblAgilityRec up to that date.

Sorry, it is very hard to explain. I'll provide more info if needed.
Thanks.
S. Jackson

S. Jackson,

One possible approach would be to use a domain function. Put an unbound
textbox in the Detail section of the [fsubAgilityRec] form, with its
Control Source set to something like this...
=DSum("[TitlePoint]","tblAgilityRecs","[DateField]#" & [DateField & "#")

If you have a lot of records in this table, loading the form might be a
bit clunky. If so, setting an Index on the DateField field may help.

--
Steve Schapel, Microsoft Access MVP


sljack63 wrote:
I have a field in a table that holds the number of title points earned
for that record tblAgilityRec.TitlePoint. Each record in tblShow has
many records in tblAgilityRec. On my form for tblShow, I have a button
that brings up a new form [fsubAgilityRec] that displays the
corresponding records contained in tblAgilityRec. On this subform, I
want to display a total-to-date for the field "Titlepoint." Do I do
this in the query for the subform, or do I put a textbox on the
subform? I tried putting in a text box with this property:
=Sum([TitlePoint]), but of course that does not work because it only
totals the titlepoint field for the records in tblAgilityRec that
correspond with tblShow - not ALL tblAgilityRecs. I also want it to
total records up to the date field appearing on that record (not
today's date). So if the agility record is dated 2/25/06, I want a
running sum of the field "titlepoint" for all records contained in
tblAgilityRec up to that date.

Sorry, it is very hard to explain. I'll provide more info if needed.
Thanks.
S. Jackson