Querying on MONTH alone!

[foosaiwoo]

Hi there,

I am getting really wound up with this. It should be so simple!

I need to create a query on a field (actualdateofvisit) and group it by the month to give me a count of all those visits (hence the catchy field name) which occurred each month since the beginning of the project. In other words I want to see:

Month Count of Visit
Jan 43
Feb 54
Mar 78

and so on.

I have tried everything I can think of but cannot work out how to do this? I need to do it in a query NOT a report as I want to port it to Crystal reports to display there.

I am think of something like:

field: Actual Date of vist
table: visits
total: Group By
Criteria: "Month"

Can anyone help me?

Thanks and Regards
Mark

 

[FoFa]

You can use MONTH([YourDate]) to return just the month, and you can use the same format in the GROUP BY clause. But I caution you, year also should be in the check YEAR([YourDate]) because if it crosses years, how can you tell the diff. between 06/2005 and 06/2006 as an example?

 

[CraigDolphin]

instead of using actualdateofvisit as your field use :

DatePart("m",visits![actualdateofvisit])
as your field and Group By as your total.

Never mind, FoFa posted as I sent.

 

[foosaiwoo]

Fantastic, worked like a dream thanks guys.

Couple of follow ups though if you don't mind?

Firstly how would I allow for the year as FoFo suggested? and secondly is there a way to show the 1,2,3,4, as Jan, Feb, Mar etc instead?

Thanks again
Mark

 

[CraigDolphin]

one way to account for year might be to concatenate the month and year in your field.
e.g., MyMonthField: DatePart("yyyy",[actualdateofvisit] & "-" & Datepart("m",[actualdateofvisit]) )

then group by that.

As for converting the month value, I would probably write a public function with a select case statement to return the correct string for the month value that you supply to the function.
if your function was called GetMyMonthString your field would looksomehting like

MyMonthField: DatePart("yyyy",[actualdateofvisit]) & "-" & GetMyMonthString(DatePart("m",[actualdateofvisit]))

and your function would look somehting like

Public Function GetMyMonthString(mymonth as integer) as String
Select case mymonth
case 1
GetMyMonthString = "January"
case 2
GetMyMonthString = "February"

etc 
etc
etc

End Select
End function

 

[roelandkim]

I usually just use one of the below expressions in a subquery, then run a totals query on the sub query to get a count by year/month, as an example the date 9/1/2002 would return:

ActualVisit:Format([actualdateofvisit],"yyyy-mm")
2002-02

ActualVisit:Format([actualdateofvisit],"yyyy-mmm")
2002-Sep

ActualVisit:Format([actualdateofvisit],"yyyy-mmmm")
2002-September

It might not be the best way, but it's easy and it works.