Results 1 to 10 of 10
  1. #1
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143

    Area Chart using criteria between variable dates.

    I am trying to switch my reports with graphs from excel to access with the hopes that this will (in the end) make things easier.
    I have had a few problems, but currently I am trying to get the area chart to show payroll data for the three months (6 pay periods) prior to the pay period in question.

    This is the row source for the chart.

    TRANSFORM Sum([Q-PayrollTotals].TaskWages) AS SumOfTaskWages
    SELECT [Q-PayrollTotals].StartDate AS PayPeriod
    FROM [Q-PayrollTotals]
    WHERE ((([Q-PayrollTotals].StartDate)>=[Reports]![R-PayrollChart]![PayPeriod]-95 And ([Q-PayrollTotals].StartDate)<=[Reports]![R-PayrollChart]![PayPeriod]))
    GROUP BY [Q-PayrollTotals].StartDate


    PIVOT [Q-PayrollTotals].MainTasks;

    This chart is not bound to the report [R-PayrollChart] but is displayed on it and I am trying to use the field [PayPeriod] on the report to populate the chart. I know the Where is not written correctly but I can't seem to figure out how to write it. I am trying to display any [PayPeriod] that occurs in the last 95 days, which would correspond to the last 6 pay periods.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have a project with couple dozen graphs. Yes, graphing in Access not as versatile as Excel but I did get everything working as I needed. However, none of my graphs require CROSSTAB query. Suggest you review this article http://allenbrowne.com/ser-67.html#Param
    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
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    The article was helpful, but not with my specific issue. I got the crosstab chart to work, but I haven't been able to add criteria to it.
    I think instead of putting the criteria in the chart query, it may work better put the criteria in the source query. But I don't know how to write the criteria.
    It's just between two dates, but it's between the date entered and whatever day is 95 days before that.

    I've tried variations of the WHERE I put in my first post:

    >=[Reports]![R-PayrollChart]![PayPeriod]-95 And ([Q-PayrollTotals].StartDate)<=[Reports]![R-PayrollChart]![PayPeriod]))

    Or

    Between #
    [Reports]![R-PayrollChart]![PayPeriod]-95# and #[Reports]![R-PayrollChart]![PayPeriod]#

    and a few others, but I can't seem to get it to work. I usually get a message that "
    [Reports]![R-PayrollChart]![PayPeriod] is not a vaild field" or it's an invalid date value...

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The article I reference shows how to add criteria to CROSSTAB. Did you follow the guidelines to use PARAMETERS or specify headings?

    What do you mean by 'source query'? The query object used by the CROSSTAB - Q-PayrollTotals? AFAIK, the CROSSTAB still requires use of PARAMETERS or to specify headings, doesn't matter where in the query sequence the filter criteria is placed. I NEVER put dynamic parameters in query objects.

    I analyse chart issues best when I can work with data. If you want to provide db for analysis, follow instructions at bottom of my post. If zip still too large, can upload to fileshare site and post link to file.
    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.

  5. #5
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    I guess I didn't (and mostly still don't) understand how to use the parameters and so assumed that it wouldn't apply. I took another look and did a bit more research and was able to put the parameter [Reports]![R-Payrollchart]![PayPeriod] and get the parameter to work, but now I have an area chart with one point.. so no area.

    The Column headings on the chart wont change so I don't think I need to specify headings, it's the row columns that I'm trying to change.

    And yes, by source query, I meant Q-Payrolltotals. Couldn't get the criteria to work there either so it's back to normal.

    I'll work on getting a copy of my database up here... first time so it might take a bit.

  6. #6
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Is it normally difficult to get a zipped file to be under 2MB? I have deleted everything I can while still allowing the report to function correctly, but it's still too big.

  7. #7
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143

    Attached Database

    Okay, I figured it out.
    Here is the database.
    Attached Files Attached Files

  8. #8
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Oh and for the database, the report R-PayRollCart will ask for a pay period. There is a table with them listed on there but I've been using "04/12/2017"

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    RowSource for all graphs reference Q-PayrollTotals but the query name is Q-PayrollTotal (without the s on the end) and that query does not have fields referenced in the RowSource. What happened?

    Don't need PayPeriod filter criteria in the RecordSource for each subreport because this is managed by the subreport container Master/Child Links properties.
    Unfortunately, I've never had much success getting chart Master/Child Link properties to work.

    I put a textbox in the PayPeriod Header section named tbxPayPeriod. Can make not visible if you want.

    I changed RecordSource for each chart.

    Pie chart:
    SELECT MainTasks, Sum(TaskWages) AS SumOfTaskWages FROM [Q-PayrollTasksandWages] WHERE (((startdate)=[tbxPayPeriod])) GROUP BY MainTasks, startdate;

    Area chart:
    Frustrating, this is why I avoid CROSSTABS and glad never had to build area chart. But this seems to be working.
    PARAMETERS [tbxPayPeriod] DateTime;
    TRANSFORM Sum([Q-PayrollTasksandWages].TaskWages) AS SumOfTaskWages
    SELECT [Q-PayrollTasksandWages].StartDate AS PayPeriod
    FROM [Q-PayrollTasksandWages]
    WHERE ((([Q-PayrollTasksandWages].StartDate) Between [tbxPayPeriod]-95 And [tbxPayPeriod]))
    GROUP BY [Q-PayrollTasksandWages].StartDate
    PIVOT [Q-PayrollTasksandWages].MainTasks;

    Bar chart:
    SELECT Employee, Sum(Wages) AS SumW FROM [Q-PayrollTasksandWages] WHERE (((StartDate)=[tbxPayPeriod])) GROUP BY Employee, StartDate ORDER BY Wages;
    Last edited by June7; 05-05-2017 at 05:06 AM.
    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.

  10. #10
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Sorry for the late reply. I am only on here at work and I only work a few days a week.

    I had created the two queries about a month apart and accidentally named one Q-PayRollTotal and the other Q-PayRollTotals, so I changed Q-PayRollTotals to Q-PayrollTasksandWages to differentiate them more easily, but then forgot to go and change the source query names.

    This does the trick though! At first I thought that it would always show the latest pay period regardless of what pay period was entered since there is no parent/child relationship (I tried adding one but I too have had little to no luck getting that to work) and no reference to the report or source query. That is not the case though, it does start at the right pay period and goes the right amount of time back.

    Thank you very much!

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

Similar Threads

  1. shorttxt variable issue and dates
    By Jen0dorf in forum Access
    Replies: 6
    Last Post: 07-25-2016, 09:38 AM
  2. Replies: 2
    Last Post: 04-29-2016, 03:17 PM
  3. Replies: 9
    Last Post: 06-16-2014, 09:38 AM
  4. Print Date Criteria in Chart
    By tbmac61 in forum Access
    Replies: 1
    Last Post: 08-17-2012, 04:30 PM
  5. Variable Criteria
    By JamesLens in forum Queries
    Replies: 0
    Last Post: 01-02-2009, 04:55 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