calculate visits per month

[eugz]

Hi All.
I need to calculate how many visits per month. What formula in a query I need to use to calculate the same value of DATE field.
Thanks.

 

[Jon K]

SELECT Format([DateField],"yyyy mmm") AS [Month], Count([TableName].[DateField]) AS CountOfDateField
FROM [TableName]
GROUP BY Format([DateField],"yyyy mm"), Format([DateField],"yyyy mmm")
ORDER BY Format([DateField],"yyyy mm");
.

 

[andrew93]

Hi
If I understand your question correctly, there is no formula required. You can do this by creating a new query, click View -> Totals, group By the date and change the 'Group By' to 'Count' for whatever field it is that you want to count.
HTH, Andrew

 

[eugz]

Thanks a lot.
It works but how make it in date (month) order. Now it only in Year order. I need in month and year order.
Thanks

 

[EMP]

If by "it works", you meant Jon's query, then may be this is what you are after:-

SELECT Format([DateField],"mmm yyyy") AS [Month], Count(TableName.DateField) AS CountOfDateField
FROM TableName
GROUP BY Format([DateField],"mmm yyyy"), Format([DateField],"yyyy mm")
ORDER BY Format([DateField],"yyyy mm");

^

 

[andrew93]

Apologies for what appears to be a really lame post compared to 'Jon K' - I replied to a post in the 'forms' forum that was subsequently locked (for being a double post) and my post ended up in this thread.......