Results 1 to 4 of 4
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    sorting months

    Hi Guys



    I have a query that works well and produces the results i'm looking for

    Code:
    TRANSFORM Sum(tblAccountTransactions.TotalPayment) AS [SumOfTotal Payment]
    SELECT Format([EntryDate],"mmm") AS [Month]
    FROM tblAccountTransactions
    WHERE (((tblAccountTransactions.TransType) Not Like " ") AND (((Year([EntryDate])*12+Month([EntryDate])-1)) Is Not Null) AND ((tblAccountTransactions.Category) Is Not Null And (tblAccountTransactions.Category) Not Like 86 And (tblAccountTransactions.Category) Not Like 85 And (tblAccountTransactions.Category) Not Like 90) AND ((tblAccountTransactions.EntryTitle) Not Like "setup") AND ((tblAccountTransactions.TotalPayment)>0))
    GROUP BY Format([EntryDate],"mmm")
    PIVOT tblAccountTransactions.TransType;
    this produces the following results

    Month Money In Money Out
    Feb
    20.00
    Mar
    60.00
    May 100.00
    Oct 10.00 20.00

    What I cant seem to get my head around is changing the sort so that the months are shown in the following order

    month name month number
    April 4
    may 5
    June 6
    July 7
    August 8
    September 9
    October 10
    November 11
    December 12
    Jan 1
    Feb. 2
    march 3
    any help would be fab

    steve

  2. #2
    Join Date
    Apr 2017
    Posts
    1,294
    Code:
    TRANSFORM Sum(tblAccountTransactions.TotalPayment) AS [SumOfTotal Payment]
    SELECT FORMAT([EntryDate],"yyyymm") AS [MonthNo], Format([EntryDate],"mmm") AS [MonthTxt]
    FROM tblAccountTransactions
    WHERE (((tblAccountTransactions.TransType) Not Like " ") AND (((Year([EntryDate])*12+Month([EntryDate])-1)) Is Not Null) AND ((tblAccountTransactions.Category) Is Not Null And (tblAccountTransactions.Category) Not Like 86 And (tblAccountTransactions.Category) Not Like 85 And (tblAccountTransactions.Category) Not Like 90) AND ((tblAccountTransactions.EntryTitle) Not Like "setup") AND ((tblAccountTransactions.TotalPayment)>0))
    GROUP BY FORMAT([EntryDate],"yyyymm"), Format([EntryDate],"mmm")
    PIVOT tblAccountTransactions.TransType;
    , and add an ORDER BY FORMAT([EntryDate],"yyyymm") clause (an unfamiliar syntax for me, so you have yourself to find out where to put it).

    PS. I changed query column name [Month] to [MonthTxt], as Month is a reserved word - better to be consistent in avoiding using it freely.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,436
    try sorting on this formula

    ((Monthnum-3) mod 10)-((Monthnum<=3)*12)

  4. #4
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Guys

    Many thaks for the help,
    PS. I changed query column name [Month] to [MonthTxt], as Month is a reserved word - better to be consistent in avoiding using it freely.
    good catch have done this

    hi Ajax

    the sort worked brill, many thanks guys

    Steve

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Age in .... months.
    By aaronjk99 in forum Access
    Replies: 5
    Last Post: 05-29-2017, 05:43 AM
  2. Replies: 6
    Last Post: 08-26-2016, 04:21 PM
  3. Need help with sorting with months of year
    By gacapp in forum Reports
    Replies: 13
    Last Post: 07-23-2015, 11:58 AM
  4. Replies: 3
    Last Post: 02-23-2014, 02:06 PM
  5. Replies: 11
    Last Post: 01-12-2012, 07:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums