Access issue: Query a Crosstab Query

[gerryg]

Hi,
Im currently encountering an issue with access and was wondering if anyone had any ideas
My Database is built on linked tables to an excel file which changes weekly. The excel file contains details of customer orders.
Im trying to build a report to detail what supplies i need to meet demand etc.
The reports required output is (Order_Ingredient, Supplier, Monday, Tuesday, Wednesday, Thursday, Friday, Total)
Order_Ingredient can contain different fields from the main orders table(i.e Drink, Snack etc)
The Orders table i am running querys off has the structure (OrderID, Date, Order_Name, Drink, Snack,).
I know i can run crosstab query's on the orders table to get the quantity for days of the week for each order_ingredient.
These come out with the structure (Order_Ingredient, 12/05, 13/05, 14/05, 16/05/ 17/05). I then run querys to add these details to a table which i run the report off.
The issue i am having is when the excel file in the back end changes (i.e. to next weeks), the date names obviously change in the crosstab queries,
which makes the queries on the crosstab queries obsolete.
If anyone has any recommentdations the would be great.

 

[Norie]

Can't you use the crosstab query as the data source for the report?

 

[gerryg]

Can't you use the crosstab query as the data source for the report?

Thanks for the reply. I thnk the main issue with the above is that there are about 10 crosstab queries required for different fields so creating a report with 10 sub reports presents its own formatting issues which is like to avoid if possible

 

[Norie]

Why do you have all these crosstab queries?

Are you just using them to manipulate the data so it's in a reportable format?

 

[gerryg]

Why do you have all these crosstab queries?

Are you just using them to manipulate the data so it's in a reportable format?

well i need to collect data which tells me how much i need of each order_ingredient broken up into daily and weekly amounts and i guess a crosstab query seems to be the best way of collecting this info. So in answer to your question, yes.

 

[Norie]

Crosstabs are generally used at the end to get the data into the format you want.

To get the actual data you can probably use 'normal' queries.

 

[gerryg]

Crosstabs are generally used at the end to get the data into the format you want.

To get the actual data you can probably use 'normal' queries.

If i can do it using normal queries then all the better, im just not sure how to be honest. I need the field contents of a field (i.e. Date), to be used as row headings in a query. I thought crosstab queries was the only way of doing this.