none
Sorting of data in a query RRS feed

  • Question

  • Hi all,

    I have a select query in MS Access 2013 as follows:

    SELECT Format([TMDate],"yyyy") AS [Year], Format([TMDate],"mmm") AS [Month], Sum(TripMaster.TMNetKg) AS SumOfTMNetKg
    FROM TripMaster
    GROUP BY Format([TMDate],"yyyy"), Format([TMDate],"mmm")
    HAVING (((Format([TMDate],"yyyy")) Between [Forms].[Reporting].[Text6] And [Forms].[Reporting].[Text7]))
    ORDER BY Format([TMDate],"yyyy");

    The query gives me the desired output, but I want the data sorted in chronological order, i.e:

    2015   Nov    1234

    2015   Dec    8523

    2016   Jan     2586

    2016   Apr     9874, etc. etc.

    Currently, the data is sorted as follows:

    2015   Dec     8523

    2015   Nov     1234

    2016   Apr      9874

    2016   Jan      2586

    This looks awkward on the report and graph.

    Is there a way of achieving this sort order in a query?

    Thanks

    Deon

    Wednesday, June 8, 2016 11:36 AM

Answers

  • Hi all,

    Thanks for all the suggestions and assistance.

    I resolved the whole issue with a crosstab query. It gave me exactly the info I needed and in the correct format.

    Thanks

    • Marked as answer by Deon SA Thursday, June 9, 2016 7:24 AM
    Thursday, June 9, 2016 7:24 AM

All replies

  • Dates are plain numbers, they sort! What is the problem?

    Best regards, George

    Wednesday, June 8, 2016 2:45 PM
  • So why not sort on the whole date field rather than the formatted year part of it?

    SELECT Format([TMDate],"yyyy") AS [Year], Format([TMDate],"mmm") AS [Month], Sum(TripMaster.TMNetKg) AS SumOfTMNetKg
    FROM TripMaster
    GROUP BY Format([TMDate],"yyyy"), Format([TMDate],"mmm")
    HAVING (((Format([TMDate],"yyyy")) Between [Forms].[Reporting].[Text6] And [Forms].[Reporting].[Text7]))
    ORDER BY [TMDate];


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, June 8, 2016 6:05 PM
  • So why not sort on the whole date field rather than the formatted year part of it?

    SELECT Format([TMDate],"yyyy") AS [Year], Format([TMDate],"mmm") AS [Month], Sum(TripMaster.TMNetKg) AS SumOfTMNetKg
    FROM TripMaster
    GROUP BY Format([TMDate],"yyyy"), Format([TMDate],"mmm")
    HAVING (((Format([TMDate],"yyyy")) Between [Forms].[Reporting].[Text6] And [Forms].[Reporting].[Text7]))
    ORDER BY [TMDate];


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    I agree with Daniel's comment with only 1 minor consideration with the sort. If you want it in Descending order you could modify accordingly...

    ORDER BY [TMDate] Desc;

    However, another consideration is why would you want all this sorted as such on the query? Will this query be the basis for a Form or Report? if so then maybe you could consider not manipulating in the query but rather in the Form's Properties or the Report's grouping and sorting. That way your query returning plain data could be used in multiple objects???


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, June 8, 2016 6:57 PM
  • Hi Deon SA,

    I think the suggestion given by Daniel Pineault1 can solve your issue.

    I would recommend you to check the suggestion given by Daniel Pineault1

    make the changes as suggested by him.

    if you think that suggestion can worked for you and solves your issue then I recommend you to mark the suggestion as an answer.

    if you think you still have a problem then let us know so that we can provide you a further help.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 9, 2016 1:40 AM
    Moderator
  • Hi all,

    Thanks for all the suggestions and assistance.

    I resolved the whole issue with a crosstab query. It gave me exactly the info I needed and in the correct format.

    Thanks

    • Marked as answer by Deon SA Thursday, June 9, 2016 7:24 AM
    Thursday, June 9, 2016 7:24 AM
  • Hi Deon SA,

    its good to hear you that you got the solution for your issue by yourself.

    thanks for updating the status regarding your issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 9, 2016 7:33 AM
    Moderator