Query YTD and MTD totals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
"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.
 
----- 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.
 
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.
 
Back
Top