Crosstab Query - Group by Month and Year

I've created a crosstab query that counts the number of calls per month. The
call field only contains a date. I've used that field as the column header
and chose to group it by month, but can't get it to seperate the months by
year. For instance all of March is grouped together whether it's March, 2009
or March, 2010. I'm pretty new to Access so I'm not familiar with SQL or
VBA. Any assistance would be greatly appreciated. Thanks,

Nate
Access 2003

"Nate" schreef in bericht
...
I've created a crosstab query that counts the number of calls per month.
The
call field only contains a date. I've used that field as the column
header
and chose to group it by month, but can't get it to seperate the months by
year. For instance all of March is grouped together whether it's March,
2009
or March, 2010. I'm pretty new to Access so I'm not familiar with SQL or
VBA. Any assistance would be greatly appreciated. Thanks,

Nate
Access 2003

=?Utf-8?B?TmF0ZQ==?= wrote in
:

I've created a crosstab query that counts the number of calls per
month. The call field only contains a date. I've used that field
as the column header and chose to group it by month, but can't get
it to seperate the months by year. For instance all of March is
grouped together whether it's March, 2009 or March, 2010. I'm
pretty new to Access so I'm not familiar with SQL or VBA. Any
assistance would be greatly appreciated. Thanks,

Nate
Access 2003

In the query design view, change the call field to a calculated field
based on Year(call) &"-" & Month(call)



--
Bob Quintal

PA is y I've altered my email address.

I would use:
Format([Call],"yyyy-mm")

--
Duane Hookom
MS Access MVP

"Bob Quintal" wrote in message
...
=?Utf-8?B?TmF0ZQ==?= wrote in
:

I've created a crosstab query that counts the number of calls per
month. The call field only contains a date. I've used that field
as the column header and chose to group it by month, but can't get
it to seperate the months by year. For instance all of March is
grouped together whether it's March, 2009 or March, 2010. I'm
pretty new to Access so I'm not familiar with SQL or VBA. Any
assistance would be greatly appreciated. Thanks,

Nate
Access 2003

In the query design view, change the call field to a calculated field
based on Year(call) &"-" & Month(call)



--
Bob Quintal

PA is y I've altered my email address.

"Duane Hookom" wrote in news:6E839EBE-8B31-
:

I would use:
Format([Call],"yyyy-mm")

That works too, but somewhere I got the impression that
year([call] & "-" & month([call]) was marginally faster.


--
Bob Quintal

PA is y I've altered my email address.

It might be marginally faster but I doubt you could tell unless your data
set is huge. My suggestion will format Jan as 01 with the leading 0. This
will sort the columns from left to right as you would expect to see them
2009-01, 2009-02,...2009-12 rather than 2009-1, 2009-10, 2009-11,... 2009-9.

--
Duane Hookom
MS Access MVP


"Bob Quintal" wrote in message
...
"Duane Hookom" wrote in news:6E839EBE-8B31-
:

I would use:
Format([Call],"yyyy-mm")

That works too, but somewhere I got the impression that
year([call] & "-" & month([call]) was marginally faster.


--
Bob Quintal

PA is y I've altered my email address.

"Duane Hookom" wrote in
:

It might be marginally faster but I doubt you could tell unless
your data set is huge. My suggestion will format Jan as 01 with
the leading 0. This will sort the columns from left to right as
you would expect to see them 2009-01, 2009-02,...2009-12 rather
than 2009-1, 2009-10, 2009-11,... 2009-9.


right.

--
Bob Quintal

PA is y I've altered my email address.

"Nate" wrote in message
...
I've created a crosstab query that counts the number of calls per month.
The
call field only contains a date. I've used that field as the column
header
and chose to group it by month, but can't get it to seperate the months by
year. For instance all of March is grouped together whether it's March,
2009
or March, 2010. I'm pretty new to Access so I'm not familiar with SQL or
VBA. Any assistance would be greatly appreciated. Thanks,

Nate
Access 2003

Pouvez vous parlez en français !!!


Le 13/03/10 17:02, dans , «*Bob
Quintal*» a écrit*:

"Duane Hookom" wrote in
:

It might be marginally faster but I doubt you could tell unless
your data set is huge. My suggestion will format Jan as 01 with
the leading 0. This will sort the columns from left to right as
you would expect to see them 2009-01, 2009-02,...2009-12 rather
than 2009-1, 2009-10, 2009-11,... 2009-9.


right.

On Fri, 19 Mar 2010 20:10:28 +0100, inesv wrote:

Pouvez vous parlez en français !!!

Non.

http://msdn.microsoft.com/fr-fr/office/default.aspx

--

John W. Vinson [MVP]