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

Creating a simple Alert / Success Message with ASP.NET/VB using Bootstrap