Group by Month

[Guest]

Hi there,

Can help me on how to group the query by Month. Currently it is grouped by
date.

SELECT [TNHEADER MASTER].[SALES PERSON], [TNHEADER MASTER].DATE, [TNHEADER
MASTER].STAFF, Sum([TNHEADER MASTER].XNLINES) AS SumOfXNLINES, Sum([TNHEADER
MASTER].TENDLINES) AS SumOfTENDLINES, Sum([TNHEADER MASTER].TENDTOTAL) AS
SumOfTENDTOTAL
FROM [TNHEADER MASTER]
GROUP BY [TNHEADER MASTER].[SALES PERSON], [TNHEADER MASTER].DATE, [TNHEADER
MASTER].STAFF;

Thanks in advance.

Mark Magesen

 

[Saran]

Enclose your date in month function like this

SELECT [TNHEADER MASTER].[SALES PERSON], Month([TNHEADER MASTER].DATE),
[TNHEADER
MASTER].STAFF, Sum([TNHEADER MASTER].XNLINES) AS SumOfXNLINES,
Sum([TNHEADER
MASTER].TENDLINES) AS SumOfTENDLINES, Sum([TNHEADER MASTER].TENDTOTAL)
AS
SumOfTENDTOTAL
FROM [TNHEADER MASTER]
GROUP BY [TNHEADER MASTER].[SALES PERSON], Month([TNHEADER
MASTER].DATE), [TNHEADER
MASTER].STAFF;

If you dislike numeral month like 1..12 use following query which would
produce like January...December.

SELECT [TNHEADER MASTER].[SALES PERSON], Format([TNHEADER
MASTER].DATE,"MMMM"), [TNHEADER
MASTER].STAFF, Sum([TNHEADER MASTER].XNLINES) AS SumOfXNLINES,
Sum([TNHEADER
MASTER].TENDLINES) AS SumOfTENDLINES, Sum([TNHEADER MASTER].TENDTOTAL)
AS
SumOfTENDTOTAL
FROM [TNHEADER MASTER]
GROUP BY [TNHEADER MASTER].[SALES PERSON], Format([TNHEADER
MASTER].DATE,"MMMM"), [TNHEADER
MASTER].STAFF;

Hope this help,
Saran.