Generate chart from crosstab queries

[tekila]

I've a form with a calendar control that allows user to select the Beginning Date and Ending Date, so as to view the relevant records of that period. I've to create crosstab queries based on the records, on a weekly basis, meaning I'll sum up the values for each week (cos I'm creating a weekly chart). Then format the date fields in the crosstabs to "ww" and define the query parameters as the Beginning and Ending Dates of the form.

The crosstabs are able to generate the desired records when I enter valid dates to the prompts but how can I create a weekly chart based on crosstab queries and letting the y-axis of the chart to reflect the week numbers, which are dependent on the dates select in the form?

 

[tekila]

ok, looks like i've to clarify my problem. the idea is how can i generate a chart based on the startdate and enddate criteria from a form? the chart should display a (bar)graph with records of weekly basis, meaning the week numbers are shown on the y-axis and the quantity (of the records say the no. of products sold) on the x-axis.

i'm desperate for help so any sort of advice would be welcomed.

 

[mrf1xa]

I'm not sure if I understand your post correctly, but I read it to mean you have a query which works fine when you enter dates manually, but you want it instead to refer to dates on a form? Have you tried setting the date criteria as Between Forms![nameofyourform]![startdate] And Forms![nameofyourform]![enddate]?

I have come across cases where this will not work with a crosstab query. If this is the case, you could try running your crosstab off a select query with the date criteria in, or even using a maketable query to generate a recordset between the required dates and then base your crosstab on this.

Hope this helps

Nigel

 

[tekila]

If this is the case, you could try running your crosstab off a select query with the date criteria in, or even using a maketable query to generate a recordset between the required dates and then base your crosstab on this.

Can you elaborate the two methods.

I want to create a chart of type 'Line-Column on 2 axes'. The column graph will display the quantity of product tested (Qty Tested) and the line graph shows the yield (Qty Passed/Qty Tested)in percentage, for the range of weeks selected.

I've a form that allows user to select the start date, end date (to determine the range of weeks reflected in the report) and the Product Family. As I said, the week numbers will be reflected on the x-axis, Qty Tested on the y-axis and Yield on the second y-axis. Qty Passed, Qty failed, Date and Product Family are fields in the underlying table.

I created a crosstab query based on the table with these fields: Product Family as row heading, Week: Format([Date],"ww&quot as column heading and Qty Tested: Sum(Qty Passed + Qty Failed) as an expression and it's a value. I didn't set the date criteria: Between Forms![nameofyourform]![startdate] And Forms![nameofyourform]![enddate] here cos I thought of setting it in the select query which is based on the crosstab query. Then I realized the Week field of the crosstab has become week numbers and it's impossible for me to set the date criteria. If I have set the date criteria in the crosstab query, how about yield field since crosstab doesn't accept two values?