CODEWITHSUNDEEP
sqlms-access
user3340983
user3340983

Reputation: 51

Access query count by months

I have 2 tables - "club members" table with 3 fields For example:

"Member_ID",   "Date_Of_Birth"
         1         13/4/1980              
         2         20/4/1990
         3         30/12/1970
         4         20/11/1960

"months list" table with the 1 field for example:

   "Month"  
   4-2017
   5-2017
   ...
   11-2017

   12-2017
   ...
   4-2018
   ...
   11-2018
   12-2018

I wish to generate a query that displays Month , Number_Of_Birthdays

for example:

 4-2017     2  
   5-2017     0  
   ...  
   11-2017    1 
   12-2017    1  
   ... 
   4-2018     2  
   ...  
   11-2018    1  
   12-2018    1

How can I do it in access?

Thank you

Upvotes: 2

Views: 547

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use inner join and aggregate function count

As you edit your Question so try below query,remember date formating is a major issue here so keep it same format 

select ML.Month,Number_Of_Birthdays_Of_Club_Memebers   from

    (
        select format(Member_Date_Of_Birth,"mm-yyyy") as  month_number,count(Member_ID) as Number_Of_Birthdays_Of_Club_Memebers  
        from club_members
        group by format(Member_Date_Of_Birth,"mm-yyyy")
    )as T1
    inner join months_list as ML
    T1.month_number=ML.Month

Upvotes: 1

Sandip Chavan
Sandip Chavan

Reputation: 39

Try this : 

SELECT Month_Date, sum(MemberCount) 
  FROM (
SELECT c.Member_ID, c.Member_Join_Date, m.Month_Date,CASE WHEN c.Member_Join_Date < m.Month_Date THEN 1 ELSE 0 END MemberCount 
  FROM club_members c, months_list m  
 ) s
GROUP BY Month_Date

Upvotes: 1

KT11
KT11

Reputation: 85

Use this if you want count the members as per join date.

Select b.Month_Date, Count(a.*) as Total_Members
From Club_Members as a INNER Join Month_List as b 
ON a.Member_Join_Date=b.Month_Date
Group BY  B.month_Date

Upvotes: 1

Related Questions