Count active members grouped by month

[bgangelmayer]

Hi,

I am trying to get active member count by month in MS Access.

Table looks like this:

admin ID firstName sDate eDate 1 Adam 01/01/2015 01/04/2015 2 Ben 01/02/2015 15/04/2015 3 Carl 01/02/2015 13/03/2015 4 Don 13/01/2015

Required output would be like this

2015-Jan: 2
2015-Feb: 4
2015-Mar: 3
2015-Apr: 3
2015-May:1
etc..

I got this far:
SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month]
FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null)
GROUP BY year(dimDate.Date), month(dimDate.Date)
ORDER BY year(dimDate.Date), month(dimDate.Date)


Query4 mCount Year Month 31 2015 1 71 2015 2 44 2015 3 13 2015 4

any help appreciated greatly

 

[plog]

Explain April to me. How come Adam isn't included?

Also, do you have a datasource that provides all the months you want to report on? You are going to need that.

 

[bgangelmayer]

SOLVED

SELECT a.Month, a.MonthLabel, COUNT (a.ID) AS aCount
FROM
(
SELECT admin.ID, dimDate.Month, dimDate.MonthLabel
FROM admin
INNER JOIN dimDate ON (dimDate.keyDate >= admin.sDate AND dimDate.keyDate <= admin.eDate) OR (dimDate.keyDate >= admin.sDate AND admin.eDate Is Null)
GROUP BY admin.id, dimDate.Month, dimDate.MonthLabel
) as a
group by a.month, a.MonthLabel;

not allowed to post links but google search on: count(distinct) access 2013 helped
access requires to have all search columns in both select statements

Output is now as expected:

Month MonthLabel aCount
1 January 2
2 February 4
3 March 3
4 April 2
5 May 1
6 June 1
7 July 1
8 August 1
9 September 1
10 October 1
11 November 1
12 December 1

hopes someone else finds this helpful.

Thanks for your reply anyway Plog!

regards