Rolling Quarterly Total

A

Antonio

Mr Vinson provided the following expression which allows
me to post a rolling 12 month total based on the
current "ReviewDate" specified on the current record
shown...and it worked wonderfully.

=DSum("[ReviewTotal]", "[tblJCOReview]", "[EntryDate]
Between #" & [EntryDate] & "# And DateAdd(""M"", -12, #"
& [EntryDate] & "#)")

On the same form I need to have 4 text boxes giving a
quarterly average (1st Quarter, 2nd Quarter, 3rd Quarter,
4th Quarter)of the review totals based on the date
(Review Date) currently displayed on the record. I
thought specifying these dates would work the same way as
the expression Mr Vinson provided by substituting
the ""W"" for a ""Q"", and a -1 for the 12 for the first
quarter, a -2 for the second quarter, a -3 for the third
quarter, and a -4 for the fourth quarter...but it doesnt
seem to be working out (this was previously created on an
Excel Spread sheet and I am comparing the totals)
Is it because the ""Q"" I am replacing is looking at the
current Quarter...and what I am asking for isnt really
a "quarterly average at all"? Rather what I have in mind
are 4 text boxes that go back and gets an average total
in quarterly segments based on the date specified on my
form??
Example:
ReviewMonth EntryDate ReviewTotal
Nov 2002 11/1/2002 11
Dec 2002 12/1/2002 9
Jan 2003 1/1/2003 10
Feb 2003 2/1/2003 10
Mar 2003 3/1/2003 10
Apr 2003 4/1/2003 10
May 2003 5/1/2003 10
Jun 2003 1/1/2003 11
Jul 2003 2/2/2003 11
Aug 2003 3/1/2003 13
Sep 2003 4/1/2003 10
Oct 2003 5/1/2003 10
Nov 2003 11/1/2003 12
Dec 2003 12/1/2003 11

If you have a record displayed on a form with a Review
Month of Nov 2003 then...
4th Quarter Avg = 32/3 (Sep - Nov 03)
3rd Quarter Avg = 35/3 (Jun - Aug 03)
2nd Quarter Avg = 32/3 (Mar - Apr 03)
1st Quarter Avg = 29/3 (Dec 2002, Jan - Feb 03

Thanks in Advance


-----Original Message-----
I tried to use the following expression:
=DSum("[ReviewTotal]","[tblJCOReview]","[EntryDate]=
Between [EntryDate] And DateDiff("M", 12, [EntryDate])")
Access tells me invalid syntax...but I cant find any
examples of an expression that can be placed in the last
section of the DSum function to return the last 12 months
based on a date field currently displayed.
Please help.

Well, DateDiff returns the number of months between two dates; you
want DateAdd instead. And if you're using the BETWEEN operator you
should not be using the = operator too - just one operator needed!

More subtly, you need two consecutive " marks around the M, because
the entire string is delimited with " marks; and you need to pull the
EntryDate you're using as a criterion out of the string, and add # as
a date delimiter.

Try

=DSum("[ReviewTotal]", "[tblJCOReview]", "[EntryDate] Between #" &
[EntryDate] & "# And DateAdd(""M"", -12, #" & [EntryDate] & "#)")

If the current record has EntryDate of 12/1/03, this will evaluate the
search criterion to

[EntryDate] BETWEEN #12/1/03# AND #12/1/02#

which should work correctly.



.
 
J

John Vinson

Mr Vinson provided the following expression which allows
me to post a rolling 12 month total based on the
current "ReviewDate" specified on the current record
shown...and it worked wonderfully.

=DSum("[ReviewTotal]", "[tblJCOReview]", "[EntryDate]
Between #" & [EntryDate] & "# And DateAdd(""M"", -12, #"
& [EntryDate] & "#)")

On the same form I need to have 4 text boxes giving a
quarterly average (1st Quarter, 2nd Quarter, 3rd Quarter,
4th Quarter)of the review totals based on the date
(Review Date) currently displayed on the record.

I'd use two separate queries to do this, or just calculate the four
textboxes directly by using Control Sources of

=DSum(...

on the textboxes. I read your example twice and I still cannot figure
out how the date on the current record defines which quarter goes in
which textbox though!
 
I

ILYA

Antonio said:
Mr Vinson provided the following expression which allows
me to post a rolling 12 month total based on the
current "ReviewDate" specified on the current record
shown...and it worked wonderfully.

=DSum("[ReviewTotal]", "[tblJCOReview]", "[EntryDate]
Between #" & [EntryDate] & "# And DateAdd(""M"", -12, #"
& [EntryDate] & "#)")

On the same form I need to have 4 text boxes giving a
quarterly average (1st Quarter, 2nd Quarter, 3rd Quarter,
4th Quarter)of the review totals based on the date
(Review Date) currently displayed on the record. I
thought specifying these dates would work the same way as
the expression Mr Vinson provided by substituting
the ""W"" for a ""Q"", and a -1 for the 12 for the first
quarter, a -2 for the second quarter, a -3 for the third
quarter, and a -4 for the fourth quarter...but it doesnt
seem to be working out (this was previously created on an
Excel Spread sheet and I am comparing the totals)
Is it because the ""Q"" I am replacing is looking at the
current Quarter...and what I am asking for isnt really
a "quarterly average at all"? Rather what I have in mind
are 4 text boxes that go back and gets an average total
in quarterly segments based on the date specified on my
form??
Example:
ReviewMonth EntryDate ReviewTotal
Nov 2002 11/1/2002 11
Dec 2002 12/1/2002 9
Jan 2003 1/1/2003 10
Feb 2003 2/1/2003 10
Mar 2003 3/1/2003 10
Apr 2003 4/1/2003 10
May 2003 5/1/2003 10
Jun 2003 1/1/2003 11
Jul 2003 2/2/2003 11
Aug 2003 3/1/2003 13
Sep 2003 4/1/2003 10
Oct 2003 5/1/2003 10
Nov 2003 11/1/2003 12
Dec 2003 12/1/2003 11

If you have a record displayed on a form with a Review
Month of Nov 2003 then...
4th Quarter Avg = 32/3 (Sep - Nov 03)
3rd Quarter Avg = 35/3 (Jun - Aug 03)
2nd Quarter Avg = 32/3 (Mar - Apr 03)
1st Quarter Avg = 29/3 (Dec 2002, Jan - Feb 03

Thanks in Advance


-----Original Message-----
I tried to use the following expression:
=DSum("[ReviewTotal]","[tblJCOReview]","[EntryDate]=
Between [EntryDate] And DateDiff("M", 12, [EntryDate])")
Access tells me invalid syntax...but I cant find any
examples of an expression that can be placed in the last
section of the DSum function to return the last 12 months
based on a date field currently displayed.
Please help.

Well, DateDiff returns the number of months between two dates; you
want DateAdd instead. And if you're using the BETWEEN operator you
should not be using the = operator too - just one operator needed!

More subtly, you need two consecutive " marks around the M, because
the entire string is delimited with " marks; and you need to pull the
EntryDate you're using as a criterion out of the string, and add # as
a date delimiter.

Try

=DSum("[ReviewTotal]", "[tblJCOReview]", "[EntryDate] Between #" &
[EntryDate] & "# And DateAdd(""M"", -12, #" & [EntryDate] & "#)")

If the current record has EntryDate of 12/1/03, this will evaluate the
search criterion to

[EntryDate] BETWEEN #12/1/03# AND #12/1/02#

which should work correctly.



.
 
A

Antonio

I am going to refer to the Original Excel Spread sheet in
the hope that it will make things clearer and hope I dont
offend anyone...

The spread sheet is named after the review month numbers
entered into it. The current month's data is always at
the bottom of cell #'s 4-15. When the next months review
is accomplished someone goes to the previous months
spread sheet,(in our case September 03)and copies all
information and places it a new spread sheet. Then they
empty the month's information found in cells B4 and C4.
Then they shift the information in Cells B5-B15 and C5-
C15 up one cell so that cells B15 and C15 are ready for
the current months review information. (you can see why
my boss asked if we could make this into an Access
database application, you are constantly copy and pasting
the same info all the time) So for October 2003 the
spread sheet looks like this...

A | B |C
1 | Review for October 03|this title fits in a combined
2 | B and C cell)
3 |ReviewMonth |ReviewTotal
4 |Nov 2002 |11
5 |Dec 2002 | 9
6 |Jan 2003 |10
7 |Feb 2003 |10
8 |Mar 2003 |10
9 |Apr 2003 |10
10 |May 2003 |10
11 |Jun 2003 |11
12 |Jul 2003 |11
13 |Aug 2003 |13
14 |Sep 2003 |10
15 |Oct 2003 |10

Then they have summary cells just below that look like
this..
Cell title Cell contents (Calculation Formulas)
"12 Month Average" Sum(C4-C15)/12
"1st Qrt Average" Sum(C4-C6)/3 (These four cells are
"2nd Qrt Average" Sum(C7-C9)/3 what they are calling
"3rd Qrt Average" Sum(C10-C12)/3 "Yearly Quarterly
"4th Qrt Average" Sum(C13-C15)/3 Averages")

When I tried to recreate the entire spread sheet in
Acces, I added an "EntryDate" (It directly corresponds to
the Review Month in MM/DD/YYYY form and the date is
always one... So Oct 2003 is 10/1/2003 and Sep 03 is
9/1/2003, etc.) field to the Acces version so that the
code Mr Vinson provided could have a reference to work
off of. So with Mr Vinsons help, I was able to reproduce
the "12 Month Average" cell function in the Excel Spread
Sheet on my Access form and Reports. Its those 1st, 2nd,
3rd, and 4th Quarter Averages I cant figure out.
In Excel, it just bases it off the numbered cells, but in
access, I thought I had to use the "Entry Date" I created
for each corresponding record to get an 3 month average
between 12 and 10 months from the currently displayed
Review Date, and then another 3 month average between 9
and 7 months from the currently displayed Review Date,
and then another 3 month average between the 6 and 4
months form the currently displayed Review Date, and
finally a 3 month average between 3months and the current
Review date displayed on the current form.
Thats when I attempted to use Mr Vinsons code and just
exchange the W for a Q (which, if I am understanding what
I read, stands for Quarter) and then insert -1, -2, -3,
and -4 to get the respective quarter averages....but, it
didnt work.

FYI - There much more to this spread sheet and is
redundant and in some spots, rediculous, but each time I
suggested an alternative my boss stated that due to
governing regulations, the numbers have to be figured and
presented in this way (there is actually a report form
these figures all fit into that I still have to some how
build in an Access report). I have a query that promts
for the Review Month and Year, and then displays the
corresponding info. Until Mr Vinson's help, I had no way
of displaying a specific month, and then text boxes that
do 12 month totals/averages.....
-----Original Message-----
Mr Vinson provided the following expression which allows
me to post a rolling 12 month total based on the
current "ReviewDate" specified on the current record
shown...and it worked wonderfully.

=DSum("[ReviewTotal]", "[tblJCOReview]", "[EntryDate]
Between #" & [EntryDate] & "# And DateAdd(""M"", -12, #"
& [EntryDate] & "#)")

On the same form I need to have 4 text boxes giving a
quarterly average (1st Quarter, 2nd Quarter, 3rd Quarter,
4th Quarter)of the review totals based on the date
(Review Date) currently displayed on the record.

I'd use two separate queries to do this, or just calculate the four
textboxes directly by using Control Sources of

=DSum(...

on the textboxes. I read your example twice and I still cannot figure
out how the date on the current record defines which quarter goes in
which textbox though!


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top