CODEWITHSUNDEEP
sqlms-access
user2204315
user2204315

Reputation:

Count records by month where date is greater than X (day, month, year)

I want to count the number of records by month for the last 12 months including the current month.

This works until we get to July, when it combines the data for July 2014 and 2015.

SELECT Max(MonthName(DatePart("m",[FormSentOff]),True)) AS Mth,
       Count(tblDisclosure.ID) AS CountOfID
FROM tblDisclosure
GROUP BY DatePart("m",[FormSentOff])
HAVING (((DatePart("m",[FormSentOff])) Is Not Null)
   AND ((Max(MonthName(DatePart("m",[FormSentOff]),True))) Is Not Null))
ORDER BY Max(tblDisclosure.FormSentOff), Max(MonthName(DatePart("m",[FormSentOff]),True));`

I have tried to build a subquery without success and have also tried manipulating the above code in various ways without success.

Upvotes: 1

Views: 1541

Answers (1)

AVG
AVG

Reputation: 1462

Air Code:

SELECT 
  Format(Max(FormSentOff),"mmm") as Mth, 
  Count(*) as CountOfRecords
FROM tblDisclosure
WHERE FormSentOff >= DateAdd("m",-11,DateSerial(Year(Date()),Month(Date()),1))
GROUP BY Year(FormSentOff), Month(FormSentOff)
ORDER BY Year(FormSentOff), Month(FormSentOff)

Upvotes: 2

Related Questions