Results 1 to 7 of 7

Adding standard values in query / chart

  1. #1
    dcf1999 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    5

    Adding standard values in query / chart

    Hello!

    I don't know how the correct terminology for what I'm asking but basically I have a crosstab query that pulls information from a table in order to make a chart. One example is that I have access pull the number of calls per week to track the weekly trend. When calculated over the span of a year, it works great and shows Sun - Saturday. But if the user filters it for say the month of august, we may not have any calls on Sunday, Tuesday and Wednesday during that month. I still want it to show those days in the chart (or query if needed) but show 0 calls. Is this possible?

    I have 3 different "trend" reports:
    • Calls per hour (sorted from 00 to 23)
    • Calls per month (sorted from Jan - Dec)
    • Calls per week

    All three drop off values on the Y axis if there is none (i.e. calls per hour pulled for Jan and no calls were between the 0800 hour so it doesn't show on chart.

    I attached some pictures.

    Thanks!
    Click image for larger version. 

Name:	pic 2.jpg 
Views:	13 
Size:	25.3 KB 
ID:	40415Click image for larger version. 

Name:	pic 3.jpg 
Views:	13 
Size:	95.4 KB 
ID:	40416Click image for larger version. 

Name:	pic 1.jpg 
Views:	13 
Size:	40.3 KB 
ID:	40417

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,654
    You will need to define your fields and ensure that you have zeros when there is no other data. See specify column headings here
    http://allenbrowne.com/ser-67.html
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,613
    Only way I know to force the zeros is to save the CROSSTAB as query object with column headers specified. Then the chart RowSource will be a SELECT query pulling from the CROSSTAB. Use Nz() function to force a 0 value for the fields that are Null.

    SELECT SubType, Nz([Mon],0) AS Monday, Nz([Tue],0) AS Tuesday, … FROM queryname;

    Otherwise, use CROSSTAB as RowSource and all the days will show in the table but not with zeros, just empty (Null) cells where there is no data. And there will be space reserved on the chart above each table column. The curve will probably be discontinuous.
    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
    dcf1999 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    5
    Quote Originally Posted by Micron View Post
    You will need to define your fields and ensure that you have zeros when there is no other data. See specify column headings here
    http://allenbrowne.com/ser-67.html
    So I did this per your website and it just adds columns. I need set defined rows (Sun - Saturday). Any way to do this?

    I tried re-designing the crosstab query so the days of weekare on the columns, the data is on the rows, and it works. However, I cannotfigure out how to “total” each column as it gives me an error you can only haveone column heading.

  5. #5
    dcf1999 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    5
    Here are pictures of the re-designed queries. I need to total each column now
    Attached Thumbnails Attached Thumbnails pic.jpg   pic 2.jpg  

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,654
    You need to total where?
    You can
    - add a control to a report group footer or form footer and DSum over a field
    - turn on Totals in a query - Ribbon>Records Group>Totals button
    - create a select query based on the crosstab (I think this was mentioned) and DSum over a field. This will give you a field total on every record however. I don't think you can just add a calculated field to a crosstab and expect it to work.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,613
    I analyze graph issue best when I can work with data. 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.

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

Similar Threads

  1. Replies: 13
    Last Post: 10-29-2012, 08:06 AM
  2. Replies: 4
    Last Post: 05-14-2012, 10:15 AM
  3. Adding duplicate values in a query
    By mooseisloose in forum Queries
    Replies: 3
    Last Post: 04-14-2011, 12:12 PM
  4. Adding tabulated values in a query
    By Govyn in forum Queries
    Replies: 3
    Last Post: 06-28-2010, 12:51 PM
  5. Adding tabulated values in a query
    By Govyn in forum Access
    Replies: 0
    Last Post: 06-28-2010, 08:56 AM

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