Group by Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Back
Top