Grouping Data by Month/Year Formatting issue

[AC5FF]

I've got a huge list of information I need to sum into months and then chart.
Here's a sample of the data:
It goes on for 5+ years

ReportDate	Count Identified	Count Sent	Count Responded	Count Flapper	Count NTF	Count Cleared
09-Oct-17	9	2				
06-Oct-17	6	2				
05-Oct-17	7	2				2
04-Oct-17	9	4	1			4
03-Oct-17	5	2				2
02-Oct-17	13	7	5	2		7
28-Sep-17	8	3	1			2
27-Sep-17	12	7	2			6
26-Sep-17	8	6	3			3
22-Sep-17	7	2				2
20-Sep-17	5	3	2			3
18-Sep-17	8	3	1			2
14-Sep-17	9	6	3			4
13-Sep-17	9	4				4
12-Sep-17	13	7	1			5
11-Sep-17	7	3				3
08-Sep-17	10	3	1			3
07-Sep-17	7	4	3	1		3
06-Sep-17	10	4	2	2		4
05-Sep-17	7	3				2
01-Sep-17	8	5	1	1		
28-Aug-17	8	3	2			2
25-Aug-17	8	7	3			2
17-Aug-17	4	3				
16-Aug-17	5	3				
14-Aug-17	7	5	2			5
11-Aug-17	12	9	1			
07-Aug-17	12	6				6
04-Aug-17	4	1				
28-Jul-17	8	5				2
25-Jul-17	6	2				1
24-Jul-17	5	3	2	1		3
20-Jul-17	7	1				1
19-Jul-17	8	2				1
18-Jul-17	7	3				3
17-Jul-17	5	4				4
11-Jul-17	7	5	3		2	2
10-Jul-17	4	2				1
07-Jul-17	9	2	1	1		2
06-Jul-17	5	1

I've figured out how to group into M/Y based onusing:
RMY: Format([ReportDate],"mmm yyyy")

Which ends up giving me:

RMY	NumberHigh	NumberSent	NumberResponded	NumberNTF	NumberFlapper	NumberCleared
Jul 2017	86	41	7	3	2	28
Jun 2017	76	35	7	0	3	28
Mar 2017	61	38	13	1	6	30
May 2017	83	37	9	0	2	31
Nov 2016	52	27	4	2	1	24
Oct 2016	45	22	7	1	2	20
Oct 2017	49	19	6	0	2	15
Sep 2017	139	63	20	0	4	46

The problem is the RMY ends up being a 'text' field and will not sort based on dates. I've been unable to figure out how to keep the grouped format in a 'date' format.

FYI: The data from Access is moved to Excel before graphing.

Ideas?

 

[MarkK]

Group by month and year as separate numeric fields. Use the VBA.Month() and VBA.Year() functions.
Mark

 

[AC5FF]

not a VBA sorta guy! LOL
and seperating month/year is going to make the graphing portion even more difficult.

 

[plog]

1. Using Month and Year does not make you a VBA guy. It just proves you are literate:

https://www.techonthenet.com/access/functions/index.php

2. Who said anything about seperating your data by month/year. Mark's advice was to GROUP BY month and year. The resulting columns from the query can remained unchanged from what you initially posted.

 

[MarkK]

The Format() function you use here...

RMY: [COLOR="Purple"]Format([/COLOR][ReportDate],"mmm yyyy"[COLOR="purple"])[/COLOR]

...is a VBA function. You might also be able to use DateSerial() or DatePart() if you need to group by month and year and still be working with a date.
hth
Mark

 

[MarkK]

Consider SQL like...

SELECT DateSerial(Year(ReportDate), Month(ReportDate), 1) As MonthYearAsDate, Count(*) As RowCount
FROM YourTable
GROUP BY Year(ReportDate), Month(ReportDate)

Mark

 

[AC5FF]

Mark

Thanks.. I may have mis understood your original reply. I'm going back over to my datasets to see if I can make this work!

 

[AC5FF]

Update;
Thanks Again Mark. Took a little playing around but I did get this to work. Used both of your suggestions.
I added a field to my final query:

Sorting: DateSerial((Right([Graph-water counts high monthly].[month],4)),(DatePart("m",[Graph-water counts high monthly].[month])),1)

That took care of it and it works like a charm. That'll save me some headache down the road as I am building quite a few of these.

Thanks again!

 

[MarkK]

Also thanks to plog! Glad you got it figured, and good luck with your project.