Group by Month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark Magesen - 27 Apr 2006 08:00 GMT

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

Reply to this Message

Saran - 27 Apr 2006 08:26 GMT

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.

Reply to this Message