multiple value fields for crosstab query

[tekila]

I want to create a crosstab query from a select query with criteria. The criteria of the fields (of select query) refer to the controls on a form. I wish to have 3 value fields for the crosstab query and knowing that there can only be 1 value field, I tried to create 3 crosstab queries having each of the value fields with the same row and column headings based on the select query, and create another select query based on the 3 crosstab queries.

However, when I tried to add the crosstab query in the new select query, I received an error saying the criteria in the first select query was not recognized as a valid field name or expression.

Was my method wrong? How can I create multiple value fields for a crosstab query based on a select query?

 

[tekila]

I'm really desperate for help so I'm posting this again. Actually I've a form that generates reports based on the calendar control. If one selects a period of one week (based on the starting and ending dates on the calendar control), a weekly report would be generated.

The column heading is the day (eg. Monday) of the date, the row heading is the Product Model and the value fields are Total, Pass and Yield. On the report, there should be one column for the Product Model (on the extreme left), seven columns for the seven days of the week (the first column heading may not be Monday, it depends on the starting date selected on the form) and the last column with heading as Overall (also with the three value fields), which totals up all the value fields of the week for each product.

My main problems are:
1. Create multiple value fields xtab query based on a select query (with Date, Product Model, Total, Pass and Yield fields)

2. The criteria in the Date field of the select query is:
>=Forms!ReportGeneration!StartingDate AND <=Forms!ReportGeneration!EndingDate
This defines a period of days but how do I display the value fields for each day on the report?

3. Display the week number of the week selected, on the report (eg. 1/1/02 to 1/7/02 is week 1 and so on)

I would appreciate all help offered.