Query - Date group sorted in alphabetical order not date order

[pmp1]

thread702-1199776 is close but does not answer my question.
I have created a query, grouping visit dates by months (and have also include cost of all visits per month; number [sum] of visits per month; average visit cost; min visit cost; max visit cost, which all works fine) However, the dates grouped read "April 2007, April 2008, August 2007 etc", in alphabetical order.
How can I change the order to cronological?
The underlying table has the VisitDates field as a Date/Time field.
My required outcome is to have a report produced from the query which displays the following:

Month Visits Income Average Min Max
Jan 2007 57 $2,907 $51 $30 $90
Feb 2007 63 $3,186 $54 $0 $60
etc until the current month i.e. Apr 2008

Thankyou

 

[PHV]

What is your actual SQL code ?

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

 

[traingamer]

It's pretty obvious that you are sorting by a string value (or a date formatted as a string) rather than a date value...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[pmp1]

PHV,
The SQL is:

SELECT DISTINCTROW Format$(Visits.[Visit Date],'mmmm yyyy') AS [Visit Date By Month], Sum(Visits.[Amount Paid]) AS [Sum Of Amount Paid], Avg(Visits.[Amount Paid]) AS [Avg Of Amount Paid], Min(Visits.[Amount Paid]) AS [Min Of Amount Paid], Max(Visits.[Amount Paid]) AS [Max Of Amount Paid], Count(*) AS [Count Of Visits]
FROM Visits
GROUP BY Format$(Visits.[Visit Date],'mmmm yyyy'), Year(Visits.[Visit Date])*12+DatePart('m',Visits.[Visit Date])-1
ORDER BY Format$(Visits.[Visit Date],'mmmm yyyy');

TrainGamer, yes the date field in my query appears to text format which is my problem. How do I format the Grouped Dates (monthly) to be formated as months and years not simply text?

Kind regards
Peter

 

[traingamer]

try:

SELECT DISTINCTROW Format$(Visits.[Visit Date],'mmmm yyyy') AS [Visit Date By Month], Sum(Visits.[Amount Paid]) AS [Sum Of Amount Paid], Avg(Visits.[Amount Paid]) AS [Avg Of Amount Paid], Min(Visits.[Amount Paid]) AS [Min Of Amount Paid], Max(Visits.[Amount Paid]) AS [Max Of Amount Paid], Count(*) AS [Count Of Visits]
FROM Visits
GROUP BY Format$(Visits.[Visit Date],'mmmm yyyy'), Year(Visits.[Visit Date])*12+DatePart('m',Visits.[Visit Date])-1
ORDER BY Format$(Visits.[Visit Date],[b][COLOR=blue]'yyyymm'[/color][/b]);

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[BigRed1212]

Instead of:

ORDER BY Format$(Visits.[Visit Date],'mmmm yyyy');

can't you do?:

ORDER BY Visit Date

I defer to Traingamer or PHV if that isn't right.

 

[SkipVought]

Hi,

Problem is that the Format function returns a STRING; so you get the STRING colating sequence of the mmmm yyyy representation of the date. Actually Real Dates are just NUMBERS that can be DISPLAYED in various formats. You just have to order by the Actual Date VALUE.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[PHV]

SELECT Format$([Visit Date],'mmmm yyyy') AS [Visit Date By Month]
, Sum([Amount Paid]) AS [Sum Of Amount Paid]
, Avg([Amount Paid]) AS [Avg Of Amount Paid]
, Min([Amount Paid]) AS [Min Of Amount Paid]
, Max([Amount Paid]) AS [Max Of Amount Paid]
, Count(*) AS [Count Of Visits]
FROM Visits
GROUP BY Format$([Visit Date],'mmmm yyyy'), Format$([Visit Date],'yyyymm')
ORDER BY Format$([Visit Date],'yyyymm');

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

 

[dhookom]

The sorting in the query makes no difference. The only reliable method for sorting a report is to use the Sorting and Grouping dialog in the report. Therefore, you must create a column or columns in your query that can be used. I would probably add the columns
Month([Visit Date]) and Year([Visit Date])
to the query so you have reliable values to sort by.

Duane
Hook'D on Access
MS Access MVP

 

[pmp1]

Thankyou all, in particular the SQL from PH which works perfectly.

Regards

Peter

 

[dhookom]

I would expect PH's query to work exactly how you want the records ordered. I would be concerned about the actual sorting in the report since there is no value in the report's record source field list that can be used to sort within the report.

Duane
Hook'D on Access
MS Access MVP