Group by Month

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

Guest

Hi,

Hi have this query :-

SELECT STAFF.SNAME AS Name, TNHEADER.STORE AS Store, TNHEADER.DATE,
Sum(TNHEADER.XNTOTAL) AS [Sales Amt]
FROM STAFF INNER JOIN TNHEADER ON STAFF.STAFF = TNHEADER.STAFF
GROUP BY STAFF.SNAME, TNHEADER.STORE, TNHEADER.DATE;

How can I view the report by month instead of Date?

Thank You in advance.
Mark Magesen
 
it would be easy enough to do it at the report level. in report design view,
open the Sorting and Grouping dialog box. add the date field to the
Field/Expression list in the top half of the box. in Group Properties at the
bottom half, change Group On from Each Value to Month.

btw, if you really have a field in your table named "Date", suggest you
change it. Date is a reserved word in Access, and as such, it shouldn't be
used to name anything.

hth
 
Mark said:
Hi,

Hi have this query :-

SELECT STAFF.SNAME AS Name, TNHEADER.STORE AS Store, TNHEADER.DATE,
Sum(TNHEADER.XNTOTAL) AS [Sales Amt]
FROM STAFF INNER JOIN TNHEADER ON STAFF.STAFF = TNHEADER.STAFF
GROUP BY STAFF.SNAME, TNHEADER.STORE, TNHEADER.DATE;

How can I view the report by month instead of Date?

Thank You in advance.
Mark Magesen
Click to expand...

SELECT STAFF.SNAME AS Name, TNHEADER.STORE AS Store,
Month(TNHEADER.DATE) As Mon,
Sum(TNHEADER.XNTOTAL) AS [Sales Amt]
FROM STAFF INNER JOIN TNHEADER ON STAFF.STAFF = TNHEADER.STAFF
GROUP BY STAFF.SNAME, TNHEADER.STORE,
Month(TNHEADER.DATE)
 
If there is a column called month then use the order by clause to view by
month:
example: ORDER BY TNHEADER.MONTH
 
Speedy,

The word 'month' is a Reserved Word (i.e. has a special meaning) in
Access, and as such it would probably not be a good idea to have a
column called month.

In any case, what is the data in there? If it is the name of the month,
then you will see them in alphabetical order, like April, December,
February, ... Or if you have the numerical value of the month, you
would probably need to take the year into account as well, unless you
just have one calendar year that's all.
 
Back
Top