Query YTD and MTD totals

A

Aaron

I have a table with the following fields: Name, Spend, Date. If I have multiple records spanning from 2002 - 2004, how can I create a query that will show a running total for Month to Date and Year to Date? It's not the total calculation I am having trouble with, it's the filtering of records just by this month or year. I want the query to look at today's Date() and realize it is January, so only show records in Jan '04
Thanks!
 
D

Duane Hookom

"running total" generally refers to multiple values that sum all the values
from previous records and shows all (or a subset) of records. Is this what
you want? Or, do you want to return a single record/result that contains one
value for MTD and one value for YTD?

Also, Name and Date are poor choices for field names since one is a property
of every object in Access and the other is a function name.

--
Duane Hookom
MS Access MVP


Aaron said:
I have a table with the following fields: Name, Spend, Date. If I have
Click to expand...
multiple records spanning from 2002 - 2004, how can I create a query that
will show a running total for Month to Date and Year to Date? It's not the
total calculation I am having trouble with, it's the filtering of records
just by this month or year. I want the query to look at today's Date() and
realize it is January, so only show records in Jan '04.
 
A

Aaron

----- Duane Hookom wrote: -----

"running total" generally refers to multiple values that sum all the values
from previous records and shows all (or a subset) of records. Is this what
you want? Or, do you want to return a single record/result that contains one
value for MTD and one value for YTD?

Also, Name and Date are poor choices for field names since one is a property
of every object in Access and the other is a function name.

--
Duane Hookom
MS Access MVP


I hear you on the field names but don't worry, they are not the actual fields of my db. I used them in the message just for clarity of what I was trying to accomplish (which I still seemingly failed at).

My goal was the latter of your suggestion. I wanted one value for MTD and one value for YTD. I was able to accomplish this through the Format(var,var) function. What I did for YTD was CurYear: Format([spnddate], "yyyy") and set the criteria to be Format(Date(), "yyyy"). Grouped by that and summed the spend. I probably could have done the same thing with less typing with the Month() and/or Year() functions.
 
D

Duane Hookom

To get the MTD and YTD totals in a single returned line in a resultset:
SELECT Sum(Abs(Format(Datefld,"yyyymm") = Format(Date(),"yyyymm")) * Spend)
as MTD,
Sum(Abs(Year(Datefld) = Year(Date())) * Spend) as YTD
FROM tblA;
Don't use any filter/criteria.

--
Duane Hookom
Microsoft Access MVP


Aaron said:
----- Duane Hookom wrote: -----

"running total" generally refers to multiple values that sum all the values
from previous records and shows all (or a subset) of records. Is this what
you want? Or, do you want to return a single record/result that contains one
value for MTD and one value for YTD?

Also, Name and Date are poor choices for field names since one is a property
of every object in Access and the other is a function name.

--
Duane Hookom
MS Access MVP


I hear you on the field names but don't worry, they are not the
Click to expand...
actual fields of my db. I used them in the message just for clarity of what
I was trying to accomplish (which I still seemingly failed at).
My goal was the latter of your suggestion. I wanted one value for MTD
Click to expand...
and one value for YTD. I was able to accomplish this through the
Format(var,var) function. What I did for YTD was CurYear:
Format([spnddate], "yyyy") and set the criteria to be Format(Date(),
"yyyy"). Grouped by that and summed the spend. I probably could have done
the same thing with less typing with the Month() and/or Year() functions.
 
Top