Group, order and change format of date field 1

[NeilT123]

In my table tblSoilAnalysisResults I have a date field DateOfAnalysis which is a straight forward date. I would like to create a query which returns the results as follows:

Oct-12
Mar-12
Nov-11
Apr-11
Sep-10

and so on.

The requirements are,that there is only one entry per month-year so Oct-12 might be a grouping of 01/10/12, 10/10/12 and 15/10/12 and that the order is most recent 1st and that the format is mmm-yy.

I have been able to get the grouping and format correct but not the order and in a separate attempt the order and format correct but not the grouping.

Does anyone have any suggestions on how I might achieve the required results, format and order?

Thank you in advance.

 

[dhookom]

Here is a solution based on the Orders table in the Northwind database containing an OrderDate field.

SELECT Format([OrderDate],"mmm-yy") AS MthYr
FROM Orders
GROUP BY Format([OrderDate],"mmm-yy");

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?
SELECT Monthname(Month(theDate)) & '-' & Right(Year(theDate),2) AS theMonth
FROM ...
GROUP BY Year(theDate),Month(theDate)
ORDER BY Year(theDate) DESC,Month(theDate) DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilT123]

Thank you for the suggestions.

After a bit of tweaking as below PHV's suggestion works.

SELECT Left(MonthName(Month([DateOfAnalysis])),3) & ' ' & Right(Year([DateOfAnalysis]),2) AS theMonth
FROM (tblSLTFarm INNER JOIN tblFieldDetails ON tblSLTFarm.FarmAccountNumber = tblFieldDetails.FarmAccountNumber) 
INNER JOIN tblSoilAnalysisResults ON tblFieldDetails.FieldCode = tblSoilAnalysisResults.FieldCode
GROUP BY Year(DateOfAnalysis), Month(DateOfAnalysis)
ORDER BY Year(DateOfAnalysis) DESC , Month(DateOfAnalysis) DESC;

but I have to admit I have no idea what is going on here so could I ask whether there is any code in here that is unnecessary.

 

[PHV]

Oops, sorry, I meant:
SELECT Monthname(Month(theDate)[!],True[/!]) & '-' & Right(Year(theDate),2) AS theMonth

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilT123]

Amended, thanks

SELECT Left(MonthName(Month([DateOfAnalysis]),True),3) & ' ' & Right(Year([DateOfAnalysis]),2) AS AnalysisMonth
FROM (tblSLTFarm INNER JOIN tblFieldDetails ON tblSLTFarm.FarmAccountNumber = tblFieldDetails.FarmAccountNumber) INNER JOIN tblSoilAnalysisResults ON tblFieldDetails.FieldCode = tblSoilAnalysisResults.FieldCode
GROUP BY Year(DateOfAnalysis), Month(DateOfAnalysis)
ORDER BY Year(DateOfAnalysis) DESC , Month(DateOfAnalysis) DESC;