This Month and Last Month

  • Thread starter Thread starter RussCRM
  • Start date Start date
R

RussCRM

In the same query, I am trying to get two counts so I can compare them
in a pivot table.

I have

ServicesID
ServicesDate

I want a count of ServicesID for the current date this month AND a
count of ServicesID for the same date last month in the same query.

Any ideas?
 
Use a subquery to get the count of services for the same date last month.

Here's an example of how to do that sort of thing with a subquery:
http://allenbrowne.com/subquery-01.html#YTD
That example does year-to-date calcuations, but the process is the same. In
your case you will be counting the ServicesID field where TheDate in the
subquery matches this from the main table:
DateAdd("m", -1, Table1.TheDate)
 
If it is just one set of dates - Today and the date one month ago. You can
use criteria of
ServicesDate = Date() OR
ServicesDate = DateAdd("m",-1,Date())

SELECT ServicesDate, Count(ServicesID)
FROM YourTable
WHERE ServicesDate = Date() OR
ServicesDate = DateAdd("m",-1,Date())
GROUP BY ServicesDate

Note that for March 29, 30, and 31 (of 2008) the DateAdd function will
return Feb 29
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top