How to get number of records each month

[mrbboy]

I have a table for storing client complaints. The structure of the table is:

tbl_Complaints
ComplaintID <autonumber>
InitiatedBy <text>
DateInitiated <date>
Complaint <Memo>

Every month, I need to make a report on the number of complaints received per month. How do I qeury that from the table?

 

[matrixindicator]

The query is a group by and a count on the field Complaint and add a column in your query where you get the month out of the DateInitiated : format([DateInitiated];"mm")

 

[Remou]

How about:

SELECT Format([DateInitiated],"yyyy mm") AS MonthInitiated, Count(*) AS Complaints
FROM tbl_Complaints
GROUP BY Format([DateInitiated],"yyyy mm");

 

[danvlas]

If you set a date/time parameter [YourReferenceDate], you can give any date and the query will return the number of complaints for the month of that date:

Select Count(*) As NoOfComplaints
From tbl_Complaints
Where Month(DateInitiated) = Month([YourReferenceDate])
And Year(DateInitiated)=Year([YourReferenceDate])

Optionally, you can display the year and month

Select Count(*) As NoOfComplaints, Year(DateInitiated) as ComplaintYear, Month(DateInitiated) as ComplaintMonth
From tbl_Complaints
Where Month(DateInitiated) = Month([YourReferenceDate])
And Year(DateInitiated)=Year([YourReferenceDate])
Group By Year(DateInitiated), Month(DateInitiated)

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TheAceMan1]

How are ya mrbboy . . .

[blue]danvlas[/blue] has you in tow. I just don't know why [blue]Now() or Date()[/blue] wasn't substituted for [blue]Month([YourReferenceDate]) & Year([YourReferenceDate])[/blue], giving:

[blue]Where Month(DateInitiated) = Month([purple][b]Now()[/b][/purple])
And Year(DateInitiated)=Year([purple][b]Now()[/b][/purple])[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[mrbboy]

Remou,

I tried your code but I received the message "Undefined function 'Format'in expression.

AceMan,

I tried the code using Date() in the where condition but that didn't return any records.

SELECT Count(*) AS NoOfComplaints, Year(DateInitiated) AS ComplaintYear, Month(DateInitiated) AS ComplaintMonth
FROM tbl_Complaint
WHERE (((Month([DateInitiated]))=Month(Now())) AND ((Year([DateInitiated]))=Year(Now())))
GROUP BY Year(DateInitiated), Month(DateInitiated);

 

[Remou]

If format is returning undefined function, it is bet to check your references:

http://www.tek-tips.com/viewthread.cfm?qid=1430779&page=1

 

[TheAceMan1]

mrbboy . . .

Also, post back the SQL as you have it now! . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[danvlas]

I tried the code using Date() in the where condition but that didn't return any records."

Maybe you don't have any complaints for December 2007...
Good month...

Try the parameter thing, that will let you obtain information for any month and year, not only the current one.

HTH








Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Remou]

danvlas
If format is giving 'undefined function', it is almost certain that there is a problem with Date as well because both are from the same library, yesno?

 

[danvlas]

Logically, yes.

However, I haven't suggested Date or Now anywhere. Instead, I suggested using a parameter.

And, strangely enough, the OP has stated:

"I tried the code using Date() in the where condition but that didn't return any records."

which shows that the query was executed after all.

Dan



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html