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.