Mutiple Field Crosstab

[Kevin Labore]

Hi

I have successfully created many crosstab queries. I need to build a query
so I can build a report from that query to give me different results than I
have down before and I dont know how to go about it. I have built several
queries where I did a crosstab querry for a single result (ie. Sales, #
Customers, # items sold , etc). The new crosstab result I am attempting to
create needs something a little different. The prior crosstab I build did a
crosstab by week & year (in Col) and then on the value(Sales, Cust, Items
etc). I want to build a similar crosstab but so show all the items (Sales,
Customers, Items, etc) and group by a Seasonal Code (Valentines, Easter,
Summer, Christmas, etc) I can group just sales or just customers etc by the
seasonal code but I want a crosstab where I can build a report from that
will display all the data for each season (with the col. in year).
I would also like to group by week(as Row) (as I have done in other
crosstabs) so I would be able to generate a report that would look something
like below

Easter Season
Week 12 2004 2003
Sales $1000 $800
Cust 115 98
Items 211 195

Week 13 2004 2003
Sales $1200 $900
Cust 125 111
Items 251 222

Total For All Weeks(sum)
would sum above weeks


I can generate a crosstab query and report to display just sales, or just
customers by season by I want to do mutiple fields...
I cant seem to do mutiple field values with a crosstab query --- I am sure
there must be a work around for this type of situation
Can someone tell me how to do this? Also an example would probably be
helpful

Thanks

Kevin

 

[[MVP] S.Clark]

The result you desire can happen in a report. You would group by the Season
value. The hard part is getitng the Season value into the crosstabbed data.

So, generate the crosstab, and append to a temp table. That table should
have a Season column as well as all the other needed field. Now the hard
part is writing to each record what season it is. You can use queries, one
per season, or VBA to perform the Update to each record.

Then the report is based on the this table, but you're not out of the woods
yet. The report would have to have some coding behind it to handle the
different years. So, you'd somehow have to check the data in the table,
then update the labels in the report.

It's not hard to do all of this, but I would estimate 8-16 hours of dev &
testing time.

 

[Kevin Labore]

Hi thanks for the response I was hoping it was something a little simpler
but it doesnt sound too bad. Something I have not done before.
A straight cross tab and report doesnt take long @ all about 5 mins (not
including report formating) and maybe up to an hour for report formating.

The other idea I had was to write a report to combine several crosstab
queries ---- that might be alittle quicker and easier for me I think I would
need 8 Crosstabs or so.

Thanks

Kevin

....

 

[Duane Hookom]

You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database to create both a Quantity and
Quantity * Price value for each column.
-Create a new table tblXtabColumns with a single field [FldName]
-add two records to this table
"Quantity"
"QtyXPrice"
-create a crosstab with the table [Products], [Orders], [Order Details], and
[tblXtabColumns]
-join the first three tables as expected but don't join tblXtabColumns to
any other table
-your Column Heading expression is
Expr1:[FldName] & Month([OrderDate])
-your Value expression is
DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])
Sum
-I set OrderDate for the first three months of 1998
The full SQL is:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

You will get two columns per month QtyXPriceN and QuantityN.