Results 1 to 4 of 4
  1. #1
    scienceguy is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    4

    Sorting Bar Chart from Greatest to Least

    Hello,

    I have a table, which contains values of the frequency of words in a string. My goal is to create a bar chart which shows the frequency from greatest to least. I have tied the chart to a query, where the query is sorted by the count. However, my bar chart does not sort accordingly. Here are my screenshots:

    TABLE:
    Click image for larger version. 

Name:	table2.PNG 
Views:	13 
Size:	4.9 KB 
ID:	42369

    QUERY:
    Click image for larger version. 

Name:	query.PNG 
Views:	14 
Size:	4.7 KB 
ID:	42367

    CHART:


    Click image for larger version. 

Name:	chart.PNG 
Views:	13 
Size:	5.7 KB 
ID:	42368

    In the "Transformed Row Source" property, the SQL is:

    SELECT qryKeywordFrequency.[Keyword], Sum(qryKeywordFrequency.[KeywordCount]) AS SumOfKeywordCount
    FROM qryKeywordFrequency
    GROUP BY qryKeywordFrequency.[Keyword]
    ORDER BY qryKeywordFrequency.[Keyword];

    However, because "Transformed Row Source" is read-only, I cannot change the ORDER BY portion of the statement. How do I sort the word count from greatest to least?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Y-axis data is text therefore alphabetic sort applies.

    Sample table shows data is already summarized so why use grouping query?

    Is this Modern Charts? In old MSGraph chart I would modify chart RowSource SQL:

    SELECT KeywordCount, Format(KeywordCount, "00") & Keyword AS Cat FROM table;

    But why is numeric order so important? That's a main reason for graphic representation of data - easier comparison of numeric values regardless of order. And having the alphabetic sort by keyword makes it easier to find a particular item - "How well did oranges do - oh wow they surpassed everyone!"
    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.

  3. #3
    scienceguy is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    Y-axis data is text therefore alphabetic sort applies.

    Sample table shows data is already summarized so why use grouping query?

    Is this Modern Charts? In old MSGraph chart I would modify chart RowSource SQL:

    SELECT KeywordCount, Format(KeywordCount, "00") & Keyword AS Cat FROM table;

    But why is numeric order so important? That's a main reason for graphic representation of data - easier comparison of numeric values regardless of order. And having the alphabetic sort by keyword makes it easier to find a particular item - "How well did oranges do - oh wow they surpassed everyone!"

    I didn't apply the grouping. Access did that automatically. It's a modern chart. We want the data visualized from greatest to least. We aren't interested in alphabetizing it. That actually makes our job much harder.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, can't help with Modern Charts. I use Access 2010 and would build graph with RowSource as I described.
    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.

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

Similar Threads

  1. Replies: 9
    Last Post: 01-05-2016, 01:09 PM
  2. Replies: 3
    Last Post: 01-16-2013, 11:56 PM
  3. Only see Greatest Date in query
    By burrina in forum Access
    Replies: 1
    Last Post: 10-25-2012, 02:01 PM
  4. looking for greatest between 5 columns?
    By syphlix in forum Queries
    Replies: 5
    Last Post: 05-03-2011, 10:34 AM
  5. Replies: 1
    Last Post: 03-05-2010, 12:27 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