Crosstab Query - Group by Month and Year

[Nate]

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

 

[eggie]

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

 

[Bob Quintal]

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)

 

[Duane Hookom]

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

 

[Bob Quintal]

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

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

 

[Duane Hookom]

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

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]

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.

 

[De Jager]

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

 

[inesv]

Pouvez vous parlez en français !!!


Le 13/03/10 17:02, dans [email protected], « Bob

 

[John W. Vinson]

Pouvez vous parlez en français !!!

Non.

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

 

[sakura]

Re: Crosstab Query - Group by Month and Year
Pouvez vous parlez en français !!!

 

[Bob Quintal]

Re: Crosstab Query - Group by Month and Year
Pouvez vous parlez en français
!!!

Yes I can but not here. This is an International group discussing the
Microsoft Access database program..

 

[áÎÁÓÔÁÓÉÑ ëÏÒÎÅÊÞÕË]

"ëÏÒÎÅÊÞÕË" (e-mail address removed)
ÓÏÏÂÝÉÌÁ ÓÌÅÄÕÀÝÅÅ: