Grouping by month and year in crosstab, without losing date value

[Rachel Garrett]

I would like to be able to group dates by month in my crosstab query,
without losing the date values. I am going to need to export this
query to Excel to do additional data processing and graphs. So I need
the month labels to actually represent dates, not strings of text.

What I have right now makes the data *look* like I want it to look,
but of course it will only alphabetize, not put in date order. In the
past, I used TO_DATE in Oracle SQL to get things the way I wanted, but
it doesn't seem to exist in Access SQL. Is there a workaround for
this?

TRANSFORM Max(Query_for_All_Assessments.[Assessed Score]) AS
[MaxOfAssessed Score]
SELECT Query_for_All_Assessments.[Question name],
Max(Query_for_All_Assessments.[Assessed Score]) AS [Total Of Assessed
Score]
FROM Query_for_All_Assessments
GROUP BY Query_for_All_Assessments.[Question name]
ORDER BY Format([Assessment date],"mmm yyyy")
PIVOT Format([Assessment date],"mmm yyyy");


Thank you!
Rachel Garrett

 

[KARL DEWEY]

Try this --
TRANSFORM Max(Query_for_All_Assessments.[Assessed Score]) AS [MaxOfAssessed
Score]
SELECT Query_for_All_Assessments.[Question name],
Max(Query_for_All_Assessments.[Assessed Score]) AS [Max Of Assessed Score]
FROM Query_for_All_Assessments
GROUP BY Query_for_All_Assessments.[Question name]
ORDER BY Format([Assessment date],"yyyy/mm/""01""")
PIVOT Format([Assessment date],"yyyy/mm/""01""");

 

[Rachel Garrett]

Thanks, Karl. This works great! I was hoping to make the "header"
field be a date instead of a string, but that is a trivial step to
take once it's in Excel.

Thanks,
Rachel