Grouping Data by Month/Year Formatting issue (1 Viewer)

AC5FF · Oct 10, 2017

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

Code:

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:

Code:

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 · Oct 10, 2017

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

AC5FF · Oct 10, 2017

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

plog · Oct 10, 2017

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 · Oct 10, 2017

The Format() function you use here...

Code:

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 · Oct 10, 2017

Consider SQL like...

Code:

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

Mark

AC5FF · Oct 10, 2017

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 · Oct 10, 2017

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:

Code:

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 · Oct 10, 2017

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

Grouping Data by Month/Year Formatting issue (1 Viewer)

AC5FF

Registered User.

MarkK

bit cruncher

AC5FF

Registered User.

plog

Banishment Pending

MarkK

bit cruncher

MarkK

bit cruncher

AC5FF

Registered User.

AC5FF

Registered User.

MarkK

bit cruncher

Similar threads

Users who are viewing this thread