Multiple value crosstab

[kristy]

In Access 97, I am trying to get multiple values across. (If version
matters, I will be upgrading to Access 2002 in July04.)

Parent Child1 Child1DT Child1ID Child2 Child2DT Child2ID... out to 12
AAA CDE 1/1/04 1111 XYZ 5/1/04 4444
ABC FGH 2/1/04 2222
AZZ IJK 1/1/05 3333 LMN 6/1/04 5555

Currently using 4 children but now there can be 12. My query left
joins out to 4 children queries and works fine. When I upped it to 12
left joined queries, I get error "cannot open any more databases".
Even if I wasn't getting an error, this method is not dynamic, even
though there shouldn't be more than 12. (But that's what was said
when it was 4!)

Excel pivot tables can do this but when text is aggregated (min or
max), it displays a zero. Whereas in Access, when text is aggregated
(min or max), it displays the text.

Should I use code? I saw a "multiple-fact crosstab report" in
Solutions.mdb but the multi values are rows not columns. Any
suggestions would be appreciated!!
TIA,
Kristy

 

[Duane Hookom]

There are some dynamic crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. You can also create
multi-value crosstabs following these instructions:

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.