Solved

Group Access data by month

Posted on 2014-11-06
3
151 Views
Last Modified: 2015-01-04
I need to pull a query that returns information about quotes issued on a system by month. Specifically I need to see how many quotes, per month a particular rep has issued. I don't want to see a list of all the quoted dates, rather a list of all the months of the year with a number, or a zero, next to each month showing how many quotes have been issued.

Any suggestions welcomed.
0
Comment
Question by:snooflehammer
3 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 40427384
try this

select Format([datefield], "yyyy-mmm"), count(Format([datefield], "yyyy-mmm"))
from tableName
group by  Format([datefield], "yyyy-mmm")
0
 
LVL 24

Accepted Solution

by:
chaau earned 250 total points
ID: 40427517
The query provided by Rey will do the trick, however, if you really want to see the months with zero orders you will need to use this query:
select Format(dateSerial(ym.y, ym.m, 1), "yyyy-mm") as yyyymm, Format(dateSerial(ym.y, ym.m, 1), "yyyy-mmm") as yyyymmm, count(t.yy) as cnt
FROM
(SELECT DISTINCT Year([dateField]) as y, months.m 
FROM yourtable, 
(Select top 1 1 as m from yourtable UNION 
Select top 1 2 as m from yourtable UNION 
Select top 1 3 as m from yourtable UNION 
Select top 1 4 as m from yourtable UNION 
Select top 1 5 as m from yourtable UNION 
Select top 1 6 as m from yourtable UNION 
Select top 1 7 as m from yourtable UNION 
Select top 1 8 as m from yourtable UNION 
Select top 1 9 as m from yourtable UNION 
Select top 1 10 as m from yourtable UNION 
Select top 1 11 as m from yourtable UNION 
Select top 1 12 as m from yourtable) as months) as ym
LEFT JOIN (SELECT year([dateField]) as yy, month([dateField]) as mm FROM yourtable) as t
ON t.yy = ym.y AND t.mm = ym.m
group by  Format(dateSerial(ym.y, ym.m, 1), "yyyy-mm"), Format(dateSerial(ym.y, ym.m, 1), "yyyy-mmm")
ORDER BY 1

Open in new window

Will return something similar to this: query result
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40427614
In report design:

Add Group On Expression: Year(theDate)
Add Group On Expression: Month(theDate)

In Month group header:
add a field: =Year(theDate) & " - " & Month(theDate)
add a field: =Count(theDate)
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dsum Function for List Box Data 7 48
Macro to import XML in Access 2013 2 40
ms access vba split-screen form needs to be refreshed on load 7 31
Help with DoEvents 8 28
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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