Order By ? (1 Viewer)

[Lissa]

Hi everyone -

I have a table that contains entries with submit dates formated as mm/dd/yyyy. I wanted to get a count of entries submited by month so I formatted the date so entries would be grouped by month like APR 2010, FEB 2010, MAR 2010 etc.
This query produces the results I want - I just can't think of a way to order the months correctly. I could use 'mm yyyy' but I still would like the results in the 'mmm yyyy' format.
Does anyone have any suggestions?

SELECT Format([Date],'mmm yyyy') AS SubmitDate, tblNonConformingMaterial.Project, Count(tblNonConformingMaterial.NCRNumber) AS CountOfNMRs
FROM tblNonConformingMaterial
GROUP BY Format([Date],'mmm yyyy'), tblNonConformingMaterial.Project
HAVING (((Format([Date],'mmm yyyy')) Like "*2010*"))
ORDER BY Format([Date],'mmm yyyy'), tblNonConformingMaterial.Project;

Thanks!

 

[boblarson]

Change your ORDER BY Clause to this:

ORDER BY [Date], tblNonConformingMaterial.Project;

And by the way, the use of DATE as a field name is a bad, bad idea. It is an Access Reserved Word and can cause things to go wacky (for lack of a better term).

 

[Lissa]

Hi Bob
Yup you're right about using DATE as a field name. I inherited this database from someone who left and I was being lazy about changing
it. But I changed it to SubmitDate.

I changed the Order By statement to
SELECT Format([SubmitDate],'mmm yyyy') AS DateSubmitted, tblNonConformingMaterial.Project, Count(tblNonConformingMaterial.NCRNumber) AS CountOfNMRs
FROM tblNonConformingMaterial
GROUP BY Format([SubmitDate],'mmm yyyy'), tblNonConformingMaterial.Project
HAVING (((Format([SubmitDate],'mmm yyyy')) Like "*2010*"))
ORDER BY [SubmitDate], tblNonConformingMaterial.Project;

but that gives me an error: You tried to execute a query that does not include the specified expression [SubmitDate] as part of an aggregate function.
I wish it would be as simple as changing the Order By statement...

 

[boblarson]

SELECT Format([SubmitDate],'mmm yyyy') AS DateSubmitted, tblNonConformingMaterial.Project, Count(tblNonConformingMaterial.NCRNumber) AS CountOfNMRs, SubmitDate
FROM tblNonConformingMaterial
GROUP BY Format([SubmitDate],'mmm yyyy'), tblNonConformingMaterial.Project, SubmitDate
HAVING (((Format([SubmitDate],'mmm yyyy')) Like "*2010*"))
ORDER BY [SubmitDate], tblNonConformingMaterial.Project;

 

[Lissa]

Okay that will work - thanks!!