Group By Month In A Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Junior - 15 Nov 2007 02:48 GMT

Seems so simple - yet having all kinds of trouble.
I have a table with [Date], [Text1] and [Text2].

Raw data looks like this:

Date Text1 Text2
1/1/2007 5:34pm aaa
1/5/2007 6:23pm bbb 1
1/15/2007 3:12pm ccc 1
2/10/2007 1:12pm ddd
2/25/2007 3:54pm eee 1

[Text2] may contain null values.

I want to group by month and count the records. So:

Date Text1 Text2
Jan-2007 3
Feb-2007 2

Reply to this Message

Junior - 15 Nov 2007 02:50 GMT

> Seems so simple - yet having all kinds of trouble.
> I have a table with [Date], [Text1] and [Text2].
[quoted text clipped - 15 lines]
> Jan-2007 3 2
> Feb-2007 2 1

I posted by accident before I completed

See above.

I would appreciate help with this.

GZ

Reply to this Message

Brian - 15 Nov 2007 04:10 GMT

First, you will want to use something besides "Date" as your field name. It
is a reserved word in Access. Instead, use something like myDate.

Now, grouping by month:

Group by combined month & year, or all the July records (from multiple
years) will be combined in one group. Then, for sorting purpose, make sure
the year appears first.

Just enter this as a field in your query, and group/sort by it.

YearMonth: Year(myDate) * 100 + Month(myDate)

Don't show this field in the output; just use it for sorting grouping; it
will look like this: 200705 (for May 2007) or 200612 (for December 2006)

> > Seems so simple - yet having all kinds of trouble.
> > I have a table with [Date], [Text1] and [Text2].
[quoted text clipped - 23 lines]
>
> GZ

Reply to this Message

John Spencer - 15 Nov 2007 13:10 GMT

SELECT Format(TableName.Date, "mmm\-yyyy")
, Count(Text1) as Text1Count
, Count(Text2) as Text2Count
FROM TableName
GROUP BY Format(TableName.Date,"yyyymm"), Format(TableName.Date,
"mmm\-yyyy")
ORDER BY Format(TableName.Date,"yyyymm")

In the query grid
-- Add your date field two times, add text1 and text2
-- Modify first date field so it reads Format(Date, "yyyymm")
-- Modify Second date field so it reads Format(Date, "mmm\-yyyy")
-- Select View Totals from menu
-- Change GROUP BY to COUNT under text1 and text2
-- Uncheck Show field under the first date
-- Set Sort by to ascending undet the first date

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Seems so simple - yet having all kinds of trouble.
> I have a table with [Date], [Text1] and [Text2].
[quoted text clipped - 15 lines]
> Jan-2007 3
> Feb-2007 2

Reply to this Message

Junior - 15 Nov 2007 14:11 GMT

> SELECT Format(TableName.Date, "mmm\-yyyy")
> , Count(Text1) as Text1Count
[quoted text clipped - 41 lines]
>
> - Show quoted text -

Thanks very much - worked like a charm!!! I knew it was something
simple.

Reply to this Message