MS Access, Group Totals by Week/Year

Table:

TAB_MOVEMENTS
DATE_RECORDED
DIRECTION

A basic table to track how many items moved in a particular direction

DIRECTION = U=up, D=down, E=error

Write a basic query for MS Access to group by week in the format WW/YY (week,year) with a total for each direction.

SELECT FORMAT( FORMAT (DATE_RECORDED, 'WW'),'00') + '/' + FORMAT(DATE_RECORDED,'YY') as DATEEXPR1, SUM( IIF( [DIRECTION] = 'U',1,0)) as TOTALU, SUM( IIF( [DIRECTION] = 'D',1,0)) as TOTALD, SUM( IIF( [DIRECTION] = 'E',1,0)) as TOTALE
FROM TAB_MOVEMENTS
WHERE [DATE_RECORDED] >= #01/01/2009# AND [DATE_RECORDED] <= #31/12/2009#
GROUP BY FORMAT( FORMAT (DATE_RECORDED, 'WW'),'00') + '/' + FORMAT(DATE_RECORDED,'YY')

This should return something similar to

DATEEXPR1TOTALUTOTALDTOTALE
09/091012
10/095233
11/09727

Comments

Popular posts from this blog

Basic Send Message to MQ with Java and IBM MQ JMS

Basic Receive Message to MQ with Java and IBM MQ JMS

Configure Database Connection using MyBatis