Improving performance of database and correct design

[TIbbs]

I have some problem in getting to a solution for the best way to work at this.
My management wants reports in table format, viewing expenditures and sales for comparrison purpose, so I am creating crosstab queries where the reports will be based on.
My current line of thought is:
1. I have a cross tab query that takes values for 1 type of pricing (Invoice_net), I need to do for the second pricing type (Export_net).
The cross tab query would show Customer expenditures by year on year comparison, quarter on quarter and month on month.

2. I would use 2 parameter queries for the management to enter the customer they want to get data on, and another parameter for the date range.

3. Additionally I would create an individual crosstab query for each year reports, quarter reports and month reports. A field is created in the form that allows the management to select those reports using a combo box.

Problem is that would create too many crosstab queries, so my line of thought would be grouping the values (Export_Net and Invoice_Net) in one crosstab query, with Export_net and Invoice_net as Values and Headings, so I would have only 3 crosstab queries for the pricing each one assigned to run the query only by year, or by quarter or by month.
I would then use VBA to run the query and only show on the report the data selected.

Is that achievable?
Would I run into problems if the management wanted to run the query say for 10 years worth of sales? Or should I put a limit on the date range to allow a limited number of records to be returned by the query?

 

[TIbbs]

Thanks for the answer, just tried looking into it most of the week and with no luck, so I went back to run the query for every field.
But I still have a problem.

The current cross tab query that I have shows invoice_net value per year for each sales agent that have a customer e.g:

CustName|AgentName|InvoiceNet 2007|InvoiceNet 2008|
ABC | Michael | 250 | 300 |
ABC | Andrew | 100 | 50 |

The InvoiceNet, is the total value that the agent sold to the customer per year.
Next I need to add parameters to extract data by customer, by agent and by date, prior to output it into a report.
Is is too many parameters, and can I change the column heading dynamically to say: show results by quarter, month, etc.
The management is insistent they want the data in this format to compare sales across the same date patterns, fom month to month and quarter to quarter.
Other than that the only thing I can think of is creating different queries for every date range.

 

[TIbbs]

Thanks for all the suggestions, the filtering is too complicated to be undertaken by crosstab queries so I took the original sugestion and have a form with a combo box selection in Access, on click it opens the appropriate Excel spreadsheet in which a pivot table is stored, the data refreshes regularly from the respective queries in Access. I have to refine it a bit more, since it needs security added and it's been a while since I worked in Excel.

It's true what they say sometimes the best solution is the simplest one.