Crosstab Query

[manu]

A CrossTab Query needs minimum 3 fields [Column Header, Row Header & 3rd field is used for calculations: sum, add etc...]

Can a CrossTab Query have multiple Rows & Columns that use the same field to calculate?

All help is appreciated....

Regards,

 

[Robert_Mc]

Hi Manu

I think I've tried this once or twice & always ge a message telling me I cannot have more than one value. To get round it I create a number of crosstabs & then 'rack em up' using a select query.

Hope this helps

Rob

 

[manu]

Thanks Robert.....

Can you elaborate and provide details on how you "Racked em up"


Regards,

 

[Robert_Mc]

Hi again Manu

It's pretty straightforward really. I create a number of crosstabs valued by sum, min, count, etc; for example,

Customer|Order No|Sales qty(sum)
Customer|Order No|Sales qty(avg), etc

These are based on a table containing lots of orders & are pivotted in the crosstab on Date.

Then create a reference table or use the original table as reference in a select query; create joins (type depends on what you weant to see) on customer to all the crosstab queries & then create the new query something like this:

Customer|Sales Qty|Average Sales

Use the customer field from the reference table & use it to group by. Use the calculated fields from the crosstabs & rename them as you like. This should give you what you want..I think.

Lastly, you can add other tables, etc to this query, which I have found very useful.

Regards

Rob