Group by month and year

[primagic]

I am trying to group a record in months and years.

i.e.

Records are over two years so I want to display

Jan 2010
Feb 2010
Mar 2010
Jan 2011
Feb 2011
Mar 2011

in my query I have columns which are pulling month number and year but when I try to sort, its either sorting all the months which is fine but jan 2010 and jan 2011 records obviously show same month number.

i want to know how i first sort records by getting the earliest year, sort them in months, then the next year and sort them in months

Thanks

 

[PWise]

what is the problem

Order By year,month

 

[primagic]

This is my query

SELECT tblDebtCollection.debtClientName, tblDebtCollection.debtAccountNumber, tblDebtCollection.debtAccountName, tblDebtCollection.debtClientContact, tblDebtCollection.debtFeeEarner, tblDebtCollection.debtFileStatus, tblDebtCollection.debtDemandLetter, tblDebtCollection.debtLastLegalAction, tblDebtCollection.debtLLADate, Format([debtDemandLetter],"mmm/yy") AS MonthText, Format([debtDemandLetter],"yy") AS [Year], Format([debtDemandLetter],"m") AS MonthNumber, tblDebtCollection.debtCollectionID
FROM tblDebtCollection
WHERE (((tblDebtCollection.debtClientName)=[Forms]![frmDashboard]![cboBanks]) AND ((tblDebtCollection.debtDemandLetter) Between [Forms]![frmDashboard]![txtDebtFromDate] And [Forms]![frmDashboard]![txtDebtToDate]))
ORDER BY Format([debtDemandLetter],"yy"), Format([debtDemandLetter],"m");
[/code}

If I run that between the dates 01/01/2010 - 30/06/2011, i get October 2010 records first, then Nov 2010, Dec 2010, Mar 2010, Apr, May, Jun, July, Aug 2010 then Feb 2011, Mar 2011

 

[SkipVought]

ORDER BY Format([debtDemandLetter],"yyyymm");

assuming that [debtDemandLetter] is a real date.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[primagic]

Your code works fine when viewing results in a query but they are still being ordered in the old sort.

Do i need to sort the report independently?

I also then want to group on year and month

So group all records that fall in March 2010, April 2010.... Feb 2011

 

[traingamer]

Your code works fine when viewing results in a query but they are still being ordered in the old sort.

Then you are probably sorting and grouping in the report, itself. Have a look at that.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[primagic]

All grouping and sorting is removed from the report.

 

[dhookom]

The sorting from a query is never reliable in your report. Always set the sorting and grouping in your report as needed.

I would sort by
Year(debtDemandLetter)
and then
Month(debtDemandLetter)

Using the format typically causes issues if you don't know what you are doing. Skip knows what he is doing so his format will work.

Duane
Hook'D on Access
MS Access MVP