M
Marcotte A
I have 4 tables (one for each region) of data in Access which hold
transaction information related to sales. I must create a sales report that
shows the sales totals for the previous 4 weeks for both the current and
previous year. I've created 4 pivot tables in Excel from the data in Access
to do this.
However, on the summary page, I must show the most recent week sales (which
I get easily with "=GETPIVOTDATA"), the Quarter to Date sales and the Year to
Date sales (both for current and previous year). In addition I must show QTD
and YTD numbers for the fiscal year (starting Sept. 1, 2006).
I'm wondering if there is any way to do this without importing the data via
MS Query and holding it in the workbook, or creating additional Pivot Tables
with Quarter or Year fields rather than Week. The solution needs to be
dynamic, in that I update the report each week, and do not want to have to
rerun/rewrite 4 queries for each table. Is there a way to write a query that
references cells in a workbook?
I don't have any experience with MS Query, limited experience with Access,
extensive experience with Excel and VBA, and fair experience with Crystal
Reports. Would it be better to use Crystal, and write a SQL query to return
the appropriate sums?
transaction information related to sales. I must create a sales report that
shows the sales totals for the previous 4 weeks for both the current and
previous year. I've created 4 pivot tables in Excel from the data in Access
to do this.
However, on the summary page, I must show the most recent week sales (which
I get easily with "=GETPIVOTDATA"), the Quarter to Date sales and the Year to
Date sales (both for current and previous year). In addition I must show QTD
and YTD numbers for the fiscal year (starting Sept. 1, 2006).
I'm wondering if there is any way to do this without importing the data via
MS Query and holding it in the workbook, or creating additional Pivot Tables
with Quarter or Year fields rather than Week. The solution needs to be
dynamic, in that I update the report each week, and do not want to have to
rerun/rewrite 4 queries for each table. Is there a way to write a query that
references cells in a workbook?
I don't have any experience with MS Query, limited experience with Access,
extensive experience with Excel and VBA, and fair experience with Crystal
Reports. Would it be better to use Crystal, and write a SQL query to return
the appropriate sums?