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]

Post by 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

[Bob Quintal]

Post by 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

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")
--
Duane Hookom
MS Access MVP

Post by Bob Quintal

Post by 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

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.

[Bob Quintal]

Post by Duane Hookom
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

Post by Bob Quintal

Post by Duane Hookom
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.

[Bob Quintal]

Post by 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.

right.

[inesv]

Pouvez vous parlez en français !!!

Post by Bob Quintal

Post by 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.

right.

[John W. Vinson]

Post by inesv
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
"inesv" <***@sfr.fr> wrote in message news:C7C98AB4.6DB%***@sfr.fr...
Pouvez vous parlez en français !!!

Post by Bob Quintal

Post by 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.

right.

[Bob Quintal]

Post by sakura
Re: Crosstab Query - Group by Month and Year
!!!

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

Post by sakura

Post by Bob Quintal

Post by 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.

right.

------=_NextPart_000_003B_01CAC823.365EBF30
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Re: Crosstab Query - Group by Month and
Year</TITLE> <META content=text/html;charset=iso-8859-1
http-equiv=Content-Type> <META name=GENERATOR content="MSHTML
8.00.7600.16490"></HEAD> <BODY style="PADDING-LEFT: 10px;
PADDING-RIGHT: 10px; PADDING-TOP: 15px" id=MailContainerBody
leftMargin=0 topMargin=0 CanvasTabStop="true" name="Compose
message area"> <DIV>&nbsp;</DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px;
PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px" dir=ltr>
<DIV>"inesv" &lt;<A
<A
.fr</A>...</DIV> <P align=center><FONT color=#800080><FONT
size=6><FONT face="Helvetica Neue UltraLight"><SPAN
style="FONT-SIZE: 24px"><B>Pouvez vous parlez en français
&nbsp;!!! </B></SPAN></FONT></FONT></FONT> <P><FONT
12px"><BR><BR>Le 13/03/10 17:02, dans
your data set is huge. My suggestion will format Jan as 01
right.<BR></FONT></SPAN></FONT></P></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_003B_01CAC823.365EBF30--

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

"ðÒÅÉÍÕÝÅÓÔ×Á É ÎÅÄÏÓÔÁÔËÉ ×ÙÓÛÉÈ ÕÞÅÂÎÙÈ ÚÁ×ÅÄÅÎÉÊ Ç.íÉÎÓËÁ"
ñ ÄÕÍÁÀ, ÞÔÏ ÌÕÞÛÉÊ ÓÁÊÔ Õ âçõ!!!!!!!!!!

Post by sakura
Re: Crosstab Query - Group by Month and Year
!!!

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

Post by sakura

Post by Bob Quintal

Post by 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.

right.

------=_NextPart_000_003B_01CAC823.365EBF30
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Re: Crosstab Query - Group by Month and
Year</TITLE> <META content=text/html;charset=iso-8859-1
http-equiv=Content-Type> <META name=GENERATOR content="MSHTML
8.00.7600.16490"></HEAD> <BODY style="PADDING-LEFT: 10px;
PADDING-RIGHT: 10px; PADDING-TOP: 15px" id=MailContainerBody
leftMargin=0 topMargin=0 CanvasTabStop="true" name="Compose
message area"> <DIV>&nbsp;</DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px;
PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px" dir=ltr>
<DIV>"inesv" &lt;<A
<A
.fr</A>...</DIV> <P align=center><FONT color=#800080><FONT
size=6><FONT face="Helvetica Neue UltraLight"><SPAN
style="FONT-SIZE: 24px"><B>Pouvez vous parlez en français
&nbsp;!!! </B></SPAN></FONT></FONT></FONT> <P><FONT
12px"><BR><BR>Le 13/03/10 17:02, dans
your data set is huge. My suggestion will format Jan as 01
right.<BR></FONT></SPAN></FONT></P></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_003B_01CAC823.365EBF30--

--
Bob Quintal
PA is y I've altered my email address.

[De Jager]

Post by 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