Report needs crosstab fields from more than one table field

  • Thread starter Thread starter T. W.
  • Start date Start date
T

T. W.

Is it possible to create a crosstab query that has more than one column
heading.

I need to generate reports that have data from a table/query field that are
the headers of the report spreadsheet. Now a crosstab query would solve
this, but it seems you can only specify one column header and I need a
minimum of two column headings (sometimes I need to use the same data as a
column header more than once, with different data for the values).

Simply put, I need more than one column header in a crosstab query (unless
there's another way to produce it).
 
....an example of what I need would be the sum of tests taken and the count
of kids per grade level per school

School is row heading
Grade level is column heading
sum of test taken per grade level is value
count of kids per grade level is value

I need the sum and the count side by side on the report.
The Grade levels are the column headers
 
There are examples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

One method of multiple values in a crosstab use this example:
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
 
Can you put your example in context with the reply I made (I made a reply to
my original e-mail with an example of what I needed)?

Thanks beforehand, and thank you for the below assistance as well.



Duane Hookom said:
There are examples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

One method of multiple values in a crosstab use this example:
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

--
Duane Hookom
MS Access MVP


T. W. said:
Is it possible to create a crosstab query that has more than one column
heading.

I need to generate reports that have data from a table/query field that are
the headers of the report spreadsheet. Now a crosstab query would solve
this, but it seems you can only specify one column header and I need a
minimum of two column headings (sometimes I need to use the same data as
a
column header more than once, with different data for the values).

Simply put, I need more than one column header in a crosstab query
(unless
there's another way to produce it).
Click to expand...
Click to expand...
 
Create a table with one field [FldName] and two records: CountKids and
SumTest. Add this table to your crosstab but don't join it to anything.
School is row heading
Grade level is column heading
The Value is Sum(IIf([FldName]="CountKids", [TestTaken], 1)


--
Duane Hookom
MS Access MVP
--

T. W. said:
Can you put your example in context with the reply I made (I made a reply
to my original e-mail with an example of what I needed)?

Thanks beforehand, and thank you for the below assistance as well.



Duane Hookom said:
There are examples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

One method of multiple values in a crosstab use this example:
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

--
Duane Hookom
MS Access MVP


T. W. said:
Is it possible to create a crosstab query that has more than one column
heading.

I need to generate reports that have data from a table/query field that are
the headers of the report spreadsheet. Now a crosstab query would solve
this, but it seems you can only specify one column header and I need a
minimum of two column headings (sometimes I need to use the same data as
a
column header more than once, with different data for the values).

Simply put, I need more than one column header in a crosstab query
(unless
there's another way to produce it).
Click to expand...
Click to expand...
Click to expand...
 
Back
Top