Pivot table in report

  • Thread starter Thread starter Claire
  • Start date Start date
C

Claire

Hi everybody:

Is there anyway to use a pivot table query for a report? I need to see two
value fields so I can not use a crosstab. Or, if the pivot can not be used
as a datasource is there a way to format it similar to Excel without having
to export to Excel? A pivot is not an attractive thing to print in
Access.......

Thanks
 
You can create a crosstab with more than one value. The following is an
example using Northwind.

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.
 
Claire;
I am not sure if I understand the question. A Pivot table is not a
query. It is a way to view your data. Since you did not mention which
ver of Access you were using there are a couple different methods. In
A2k it uses Excel to perform the action anyway. In A2003 you can link
or import an Excel Pivot Table and create your Report based on the
table. The Excel Pivot Table are very easy to navigate and provide a
quick easy format. HTH
 
Veeerrry interesting. Thanks very much Duane, I will check this out and give
it a try.

Claire

You can create a crosstab with more than one value. The following is an
example using Northwind.

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.
 
Your example is exactly what I needed. It took a little tweaking but ......
no worries.

I need this query to do one more thing (maybe I'm pushing my luck)

I have used a form to set a year parameter, the query needs to see the year
and retrieve the input data for the corresponding year. This works just fine
with all of my other queries and crosstabs except this one.

Here is my SQL:

PARAMETERS [Forms]![frmAdministrativeReportsMenu]![txtReportYear] DateTime;
TRANSFORM
Count(IIf([FldName]="dtmLastInspectionDate",[dtmLastInspectionDate])) AS
DaVal
SELECT tblDispositionTable.bytWorkArea, tblWorkArea.strAreaOfficer,
tblDispositionTable.strType
FROM tblXtabColumns, tblWorkArea INNER JOIN tblDispositionTable ON
tblWorkArea.bytWorkArea = tblDispositionTable.bytWorkArea
WHERE (((tblDispositionTable.strType) Like "drs" Or
(tblDispositionTable.strType)="eze" Or (tblDispositionTable.strType)="loc"
Or (tblDispositionTable.strType)="mld" Or
(tblDispositionTable.strType)="mll" Or (tblDispositionTable.strType)="msl"
Or (tblDispositionTable.strType)="pil" Or
(tblDispositionTable.strType)="pla" Or (tblDispositionTable.strType)="smc"
Or (tblDispositionTable.strType)="sme" Or
(tblDispositionTable.strType)="sml" Or (tblDispositionTable.strType)="rec")
AND ((tblDispositionTable.dtmInputDate) Like "*" &
[Forms]![frmAdministrativeReportsMenu]![txtReportYear]))
GROUP BY tblDispositionTable.bytWorkArea, tblWorkArea.strAreaOfficer,
tblDispositionTable.strType
PIVOT tblXtabColumns.fldName In
("dtmLastInspectionDate","dtmPreviousInspectionDate","year");

If I remove the parameter and column heading info then I receive the error
message:

"The Microsoft Jet database engine does not recognize
[Forms]![frmAdministrativeReportsMenu![txtReportYear] as a valid field name
or expression"

I have tried basing my query on an underlying query that already has this
criteria in place, but no luck, same message.

Is there another way to have this particular query use the criteria?

Thanks a bunch!
Claire

Veeerrry interesting. Thanks very much Duane, I will check this out and give
it a try.

Claire

You can create a crosstab with more than one value. The following is an
example using Northwind.

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.
 
I posted this reply to the original thread, but it probably got
buried.......

Your example is exactly what I needed. It took a little tweaking but ......
no worries.

I need this query to do one more thing (maybe I'm pushing my luck)

I have used a form to set a year parameter, the query needs to see the year
and retrieve the input data for the corresponding year. This works just fine
with all of my other queries and crosstabs except this one.

Here is my SQL:

PARAMETERS [Forms]![frmAdministrativeReportsMenu]![txtReportYear] DateTime;
TRANSFORM
Count(IIf([FldName]="dtmLastInspectionDate",[dtmLastInspectionDate])) AS
DaVal
SELECT tblDispositionTable.bytWorkArea, tblWorkArea.strAreaOfficer,
tblDispositionTable.strType
FROM tblXtabColumns, tblWorkArea INNER JOIN tblDispositionTable ON
tblWorkArea.bytWorkArea = tblDispositionTable.bytWorkArea
WHERE (((tblDispositionTable.strType) Like "drs" Or
(tblDispositionTable.strType)="eze" Or (tblDispositionTable.strType)="loc"
Or (tblDispositionTable.strType)="mld" Or
(tblDispositionTable.strType)="mll" Or (tblDispositionTable.strType)="msl"
Or (tblDispositionTable.strType)="pil" Or
(tblDispositionTable.strType)="pla" Or (tblDispositionTable.strType)="smc"
Or (tblDispositionTable.strType)="sme" Or
(tblDispositionTable.strType)="sml" Or (tblDispositionTable.strType)="rec")
AND ((tblDispositionTable.dtmInputDate) Like "*" &
[Forms]![frmAdministrativeReportsMenu]![txtReportYear]))
GROUP BY tblDispositionTable.bytWorkArea, tblWorkArea.strAreaOfficer,
tblDispositionTable.strType
PIVOT tblXtabColumns.fldName In
("dtmLastInspectionDate","dtmPreviousInspectionDate","year");

If I remove the parameter and column heading info then I receive the error
message:

"The Microsoft Jet database engine does not recognize
[Forms]![frmAdministrativeReportsMenu![txtReportYear] as a valid field name
or expression"

I have tried basing my query on an underlying query that already has this
criteria in place, but no luck, same message.

Is there another way to have this particular query use the criteria?

Thanks a bunch!
Claire

Veeerrry interesting. Thanks very much Duane, I will check this out and give
it a try.

Claire

You can create a crosstab with more than one value. The following is an
example using Northwind.

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.
 
Back
Top