Results 1 to 11 of 11

How to sort monthly bar chart formatted "mmm" in numerical order

  1. #1
    smatchymo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    10

    How to sort monthly bar chart formatted "mmm" in numerical order

    I have a totals query that I have linked to a bar chart in a report. What is the best way to have it display the "mmm" abbreviation without sorting it in alphabetical order? My query has a TransactionMonth field and a TransactionAmount field. The TransactionMonth field pulls from a date field using MonthName(Month([TransactionDate]),1). I could just use Month instead of MonthName, but that doesn't look as nice...



    When researching I saw mention of ORDER BY, but I'm unfamiliar with SQL and wasn't able to quite figure that out.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,082
    order by month, display the monthname

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,478
    Calculate month number field and set SORT row under that field in design grid.

    If you want to provide your db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    smatchymo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    10
    Thanks Ajax and June, but I'm still lost unfortunately. Ajax are you talking SQL? I'm completely unfamiliar with SQL.

    June, I'm not quite sure how that applies to the chart on the report. I get how that will properly order it in the query, but the chart will still be alphabetical because I want to display the field with the month name.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,082
    Ajax are you talking SQL? I'm completely unfamiliar with SQL.
    But you are familiar with queries (I have a totals query). The query design grid is just a way of creating and editing sql. ORDER BY is sql is the same as Sort in the query design grid. When in query design you will see a SQL button bottom right of the form, or the SQL option to the left of the ribbon. clicking on either of these will take you to the sql view for your query

  6. #6
    smatchymo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    10
    I get what you were saying now. I don't have issues getting the query to order how I want, my issue is with the bar chart in a report. I can turn a query into a pivot chart and it will order fine, but trying to do it in a report trips me up.

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,082
    are you asking how to use the chart control on a report? if so, see this link https://support.office.com/en-us/art...6-4ecb737ea7f7. it's for 2019 which is a bit more flexible than 2010 but should give you an idea

  8. #8
    smatchymo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    10
    Quote Originally Posted by Ajax View Post
    are you asking how to use the chart control on a report? if so, see this link https://support.office.com/en-us/art...6-4ecb737ea7f7. it's for 2019 which is a bit more flexible than 2010 but should give you an idea
    I guess I'm not sure where in the chart control I'm able to get it to do what I'm asking. I just thought this would be a common enough activity that there would be an obvious solution. As far as I can tell there is no way for me to do what I want using my totals query. However, I can get it to do something close to what I want by going to the source data, or my query for all transactions. When doing that I use the TransactionDate and then have my chart sum the data by month. My issue is that this also displays the year along with the month. For this particular chart, I'm only showing YTD spending, so the year is redundant. Is there a way to format that to only show "mmm"? If not, I'm back to my original question of how to customize the way my chart sorts the bars.

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,082
    beyond providing the link and the suggestion I and others have made about sorting your data, not sure what else I can suggest. It may be your data is just in a design that won't enable you do do what you want

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,478
    You need to provide sample data and the query statement. That's why I suggested you just provide the db for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    smatchymo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    10
    Quote Originally Posted by Ajax View Post
    beyond providing the link and the suggestion I and others have made about sorting your data, not sure what else I can suggest. It may be your data is just in a design that won't enable you do do what you want
    I've solved my issue, but I don't think you were understanding what I was asking. I have a table with TransactionDates and TransactionAmounts among other fields. I had a totals query pulling from that table. I wanted to have a chart in a report reference that query to show monthly sums. That does not work, because in order to properly group the query you have to convert the TransactionDate to the month name or number. When you do this, the field loses it's date properties, so the chart wouldn't be able to sort it in chronological order.

    As I mentioned, I changed what I was doing, and I had the chart pull directly from a different query which had my raw transactional data with all of the individual dates. I had the chart calculate the monthly sums now instead of a totals query. Doing this properly sorted everything, however, it displayed the labels for the bars in the chart as "Jan '19" instead of "Jan". As I mentioned, having the year was redundant for my needs. The issue was no longer sorting. The solution to that was changing the formatting in the "Row Source" property field.

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

Similar Threads

  1. Replies: 5
    Last Post: 04-08-2017, 07:50 AM
  2. sort order of the date is "slightly" off...
    By markjkubicki in forum Queries
    Replies: 14
    Last Post: 06-16-2015, 12:36 PM
  3. Change Sort Order of Combo Box "On-The-Fly"
    By JoeM in forum Programming
    Replies: 6
    Last Post: 09-26-2013, 06:50 PM
  4. Replies: 1
    Last Post: 12-28-2012, 02:54 PM
  5. Replies: 11
    Last Post: 03-15-2012, 01:36 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
  •  
Tech Forums: Microsoft Office Forums