Results 1 to 4 of 4
  1. #1
    cartwright is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3

    Multiple Series in Access Chart

    I'm struggling with creating a report chart in Access. I'm working with the following query:
    Code:
    SELECT tblProfileGrowth.Date, tblProfileGrowth.Followers, tblProfileGrowth.Impressions, tblProfileGrowth.Reach, tblProfileInteractions.[Profile Views], tblProfileInteractions.[Website Clicks]FROM tblProfileGrowth, tblProfileInteractions WHERE tblProfileGrowth.Date = tblProfileInteractions.Date
    ORDER BY tblProfileGrowth.Date DESC;
    I want to show several of the fields in a chart, specifically Followers, Impressions, and Reach, with the Date as the x-axis. I can show any one of the fields (using AVG as the aggregator), but when I attempt to include a second series on the Y axis, I get an error: "Your query does not include the specified expression [fieldname] as part of an aggregate function."

    I can look at the chart property sheet, Transformed Row Source, and see
    Code:
    SELECT Format([Date], "mmm 'yy"), [Followers], Avg([Reach]) AS [AvgOfReach] FROM [QryProfileEngagement] GROUP BY Format([Date], "mmm 'yy"), Year([Date])*12 + Month([Date])-1 ORDER BY Year([Date])*12 + Month([Date])-1
    But it isn't editable. What am I doing wrong? I suspect this should be an easy fix, but I'm really struggling.

    In short, how do I select multiple checkboxes for the Y-axis?

  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
    I analyze chart issues best with data. Provide a table in post or if you want to provide 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.

  3. #3
    cartwright is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    3
    First table:
    Code:
    tblProfileGrowth
    
    ID Date Followers Impressions Reach
    160 20-Feb-20 114 58
    159 19-Feb-20 5,396 1,991 995
    154 18-Feb-20 5,387 1,819 977
    155 17-Feb-20 5,387 2,115 1,214
    156 16-Feb-20 5,386 1,349 797
    157 15-Feb-20 5,376 2,024 1,335
    158 14-Feb-20 5,370 1,636 923
    153 13-Feb-20 5,357
    152 12-Feb-20 5,349 2,408 1,175
    151 11-Feb-20 5,336 1,846 1,275
    146 10-Feb-20 5,332 1,642 1,093
    147 9-Feb-20 5,331 3,266 1,895
    148 8-Feb-20 5,330 2,195 1,173
    149 7-Feb-20 5,314 3,948 1,294
    Second table:
    Code:
    tblProfileInteractions
    
    ID Date Profile Views Website Clicks LinkinBio Clicks LinkinBio Pageviews
    129 2020-02-20 9 0
    128 2020-02-19 121 0
    123 2020-02-18 82 0
    124 2020-02-17 179 2
    125 2020-02-16 159 0
    126 2020-02-15 162 0
    127 2020-02-14 194 1
    122 2020-02-13 134 1
    121 2020-02-12 134 1
    120 2020-02-11 99 0
    115 2020-02-10 120 2
    116 2020-02-09 310 2
    117 2020-02-08 183 1
    118 2020-02-07 356 1
    119 2020-02-06 129 1
    QryProfileEngagement to join them:
    Code:
    SELECT tblProfileGrowth.Date, tblProfileGrowth.Followers, tblProfileGrowth.Impressions, tblProfileGrowth.Reach, tblProfileInteractions.[Profile Views], tblProfileInteractions.[Website Clicks]
    FROM tblProfileGrowth, tblProfileInteractions
    WHERE tblProfileGrowth.Date = tblProfileInteractions.Date
    ORDER BY tblProfileGrowth.Date DESC;
    Resulting query:
    Code:
    Date Followers Impressions Reach Profile Views Website Clicks
    20-Feb-20 114 58 9 0
    19-Feb-20 5,396 1,991 995 121 0
    18-Feb-20 5,387 1,819 977 82 0
    17-Feb-20 5,387 2,115 1,214 179 2
    16-Feb-20 5,386 1,349 797 159 0
    15-Feb-20 5,376 2,024 1,335 162 0
    14-Feb-20 5,370 1,636 923 194 1
    13-Feb-20 5,357 134 1
    12-Feb-20 5,349 2,408 1,175 134 1
    11-Feb-20 5,336 1,846 1,275 99 0
    10-Feb-20 5,332 1,642 1,093 120 2
    9-Feb-20 5,331 3,266 1,895 310 2
    8-Feb-20 5,330 2,195 1,173 183 1
    7-Feb-20 5,314 3,948 1,294 356 1
    6-Feb-20 5,283 2,542 1,541 129 1
    5-Feb-20 5,271 2,093 1,295 208 0
    4-Feb-20 5,255 2,942 1,882 191 2
    3-Feb-20 5,253 2,319 1,236 248 0
    2-Feb-20 5,245 2,202 1,282 234 2
    1-Feb-20 5,235 2,271 1,023 232 4
    I've truncated the data for space limits, but you get the idea. What I'd like is a chart that shows multiple of the fields in QryProfileEngagement. So far, I can only show Date on one axis (x) and followers (or one field) on y. Any way to have a single chart that shows, e.g., Followers, Impressions, Reach, and Profile Views all at the same time?

  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
    Advise not to use spaces nor punctuation/special characters in naming convention. Also, Date is a reserved word and should not use reserved words as names for anything.

    An XYScatter chart will show dates on X-axis and multiple series on Y-axis with RowSource:

    SELECT [Date], Followers, Impressions, Reach, ProfileViews, WebsiteClicks FROM QryProfileEngagement;

    The chart must be set for ByColumn.

    This is with Access 2010 MSGraph chart.
    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. Access 2007 Chart - Series Titles
    By kdbailey in forum Access
    Replies: 4
    Last Post: 02-08-2017, 11:04 AM
  2. Replies: 3
    Last Post: 08-18-2016, 06:56 AM
  3. Chart with Multiple Series from Query
    By crimedog in forum Access
    Replies: 1
    Last Post: 09-01-2015, 12:38 PM
  4. Replies: 4
    Last Post: 07-29-2012, 04:38 PM
  5. Replies: 4
    Last Post: 03-04-2012, 08:59 PM

Tags for this Thread

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