Group By Month In A Query

[Junior]

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

 

[Junior]

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 2
Feb-2007 2 1

I posted by accident before I completed

See above.

I would appreciate help with this.

GZ

 

[Guest]

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].

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 2
Feb-2007 2 1

I posted by accident before I completed

See above.

I would appreciate help with this.

GZ

 

[John Spencer]

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

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

 

[Junior]

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

--
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].

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- Hide quoted text -

- Show quoted text -

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