[Last Call] Learn how to a build a cloud-first strategyRegister Now


count a field for each month for 3 year

Posted on 2012-09-14
Medium Priority
Last Modified: 2012-09-14
Hi guys,

FROM stats
GROUP BY record_date.MONTH

It is possible to modify the query so that it count id for each month returning the results for each month of each year, Jan 2009, Jan 2010,  Jan 2011.
Thank you
Question by:marian68
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 668 total points
ID: 38398740
Depends ... is there a column we can use to grab the year?

SELECT month_column, year_column, COUNT(whatever)
FROM your_table
GROUP BY month_column, year_column
ORDER BY month_column, year_column

btw, is record_date a table?  If so, how do you relate it to stats?
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 38398744
Have you tried:

FROM stats
GROUP BY record_date.MONTH, record_date.Year
LVL 25

Assisted Solution

lwadwell earned 664 total points
ID: 38398840
If it is possible that there are no rows for some months ... and what the query to return 0 for that month - you may need a reference table for each month/year you want reported and join to it.  i.e.

    refMonth    refYear
    ---------------    ------------
    01               2010
    02               2010
    ... etc
    09               2012

Then query like:
SELECT refMonth, refYear, count(*)
FROM tblRefMonths rm
LEFT JOIN your_table yt ON rm.refMonth = MONTH(yt.DateField) AND rm.refYear = YEAR(yt.DateField)
GROUP BY refMonth, refYear

Open in new window

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Closing Comment

ID: 38398950
Thank you guys,
It is possible to count records at intervals of 2 weeks.
LVL 66

Expert Comment

by:Jim Horn
ID: 38398978
>It is possible to count records at intervals of 2 weeks.
Sure, but that will be a little tougher, and you have to start by defining what 'intervals of 2 weeks' means.

Author Comment

ID: 38398988
I think I have to come with another question.
Thank you

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question