Linking a crosstab query to Excel

[jaw323]

I created 2 crosstab queries in Access. Now I want to open Excel and link to them so that I can perform a calculation on them to get a crosstab of averages from the two queries. The problem is that I can link normal Excel queries, but the ones I created that were crosstabs do not show up for me to chose from. Is it impossible to link these to excel? What might be another way i might be able to average the data from the two cross tabs? Can I do this in Access with another crosstab query and forget about Excel?

 

[dhookom]

There are ways to create crosstabs with multiple values. Without knowing how you are averaging or what your tables or queries look like I can only suggest the following solution for multivalue crosstabs:
You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database.
-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.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jaw323]

Wow, thanks. Just what I was looking for. Just need to tweak it a little and I'll be set.