Crosstab Query - Group by Month and Year

  • Thread starter Thread starter Nate
  • Start date Start date
N

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
 
Nate said:
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
Click to expand...
 
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
Click to expand...

In the query design view, change the call field to a calculated field
based on Year(call) &"-" & Month(call)
 
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 said:
I would use:
Format([Call],"yyyy-mm")
Click to expand...
That works too, but somewhere I got the impression that
year([call] & "-" & month([call]) was marginally faster.
Click to expand...
 
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.
Click to expand...

right.
 
Nate said:
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
Click to expand...
 
Re: Crosstab Query - Group by Month and Year
Pouvez vous parlez en français
!!!
Click to expand...
Yes I can but not here. This is an International group discussing the
Microsoft Access database program..
 
"ëÏÒÎÅÊÞÕË" (e-mail address removed)
ÓÏÏÂÝÉÌÁ ÓÌÅÄÕÀÝÅÅ:
 
Back
Top