Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2020
    Location
    Tennessee
    Posts
    2

    Query for Graphing


    I'm not even sure I set up my tables correctly to obtain the results I'm seeking. What I can say is that I'm having difficulty with creating a graph from the data my query is giving me. On a monthly basis, I receive service reports for several of the students I serve. I setup a table for each year and the columns basically represent the months in the year. They are yes/no and link to a student table. If I receive a report for the student, I check yes for that month. My query gives me columns of totals for yes responses for each month.

    I'm trying to create a graph that will provide a visual image of the number of reports I receive each month. Can someone please point me in the right direction. Thanks, Gregg

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Should not have a table for each year, should be one table with another field to identify year.

    Provide data for analysis. If you want to provide db, 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
    Join Date
    Jun 2020
    Location
    Tennessee
    Posts
    2
    I'm interested in hearing your reasoning behind your comment that I should not have a separate service report table for each year because I built it that way with a couple things in mind. First, the caseloads change every year and I was thinking that separating those tables by year would allow me to see the maximum number of case for each year. Second, I built forms to populate the data which I use as subforms that I pop in and out of the main form I use to edit case info. Having separate tables makes creating those subforms very easy. Of course, I'm sure there's some advantage I'm not thinking of to having all that data in one table. Please share your thoughts. I've attached a version of the db that only includes tables, queries, and forms related to service report tracking. If you look at the queries, you'll see the data I'd like to graph. Thanks, Gregg
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A process that routinely modifies database structure is usually a bad design. When you add a new table (or field), have to modify tables, queries, forms, reports, code. Better to add records, not change structure.

    Use filtered queries/forms/reports to restrict what data is retrieved from table.

    Don't need IIf(). Example:

    SELECT "TN" AS TN, Sum(Abs([2020 Service Reports (725)]![01-2020])) AS [01-2020], Sum(Abs([2020 Service Reports (725)]![02-2020])) AS [02-2020]
    FROM [2020 Service Reports (725)];

    That query can be chart RowSource.

    Now if you want multiple years on one chart, that will require a UNION query combining tables into one dataset which should have been to begin with as a single table.


    Advise not to use spaces nor punctuation/special characters in naming convention.
    Last edited by June7; 06-22-2020 at 04:35 PM.
    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. Need HELP Graphing Query from SOL Visual Basic
    By akneppers in forum Queries
    Replies: 5
    Last Post: 09-12-2017, 01:28 PM
  2. Graphing
    By rpeare in forum Forms
    Replies: 10
    Last Post: 12-10-2013, 04:15 PM
  3. Graphing a value between two dates
    By amcalabrese in forum Access
    Replies: 5
    Last Post: 04-19-2011, 05:18 AM
  4. Pie Graphing problem
    By newtoAccess in forum Reports
    Replies: 6
    Last Post: 12-13-2010, 09:36 AM
  5. Replies: 0
    Last Post: 07-22-2010, 07:43 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
  •  
Other Forums: Microsoft Office Forums