group by yy/mm from a date variable

[john lawlor]

Date variable Date_Completed copied to a string yy/mm Date_Completed2 by

Date_Completed2=format([Date_Completed],"yy/mm"])


The following query uses the ab-normal string yy/mm Date_Completed2 variable.

EXACTLY what syntax do I use format([Date_Completed],"yy/mm") to
replace
Date_Completed2 in the following query:

TRANSFORM Avg([**Maintenance Compliance By Hospital for Charts].c) AS AvgOfc
SELECT [**Maintenance Compliance By Hospital for Charts].Date_Completed2,
Avg([**Maintenance Compliance By Hospital for Charts].c) AS [Total Of c]
FROM [**Maintenance Compliance By Hospital for Charts]
GROUP BY [**Maintenance Compliance By Hospital for Charts].Date_Completed2
PIVOT [**Maintenance Compliance By Hospital for Charts].UnitID;

 

[John Spencer (MVP)]

I've aliased the extremely long table (or query) name to make this easier to
read and type.

TRANSFORM Avg(.c) AS AvgOfc
SELECT FORMAT(.Date_Completed2, "yy/mm") as Date_Completed2
, Avg(.c) AS [Total Of c]
FROM [**Maintenance Compliance By Hospital for Charts] as S
GROUP BY FORMAT(.Date_Completed2, "yy/mm")
PIVOT .UnitID;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County