Similar crosstab queries on one report

R

Rohit Thomas

Hello,

I have two crosstab queries that use the same underlying
query to sum and count the totat number of adjustments
created on a monthly basis. I would like to place both
queries on one report but am at a loss on how to do this
(showing only one is not a problem). Below are the SQL
statements for the two crosstab queries. Any thoughts on
how I can get this accomplished would be greatly
appreciated.

Thanks in advance,
Rohit Thomas

Count adjustments:
TRANSFORM Count([qryBank001TellerFifty&UnderAll].[Credit
OR Debit]) AS [The Value]
SELECT [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID], Count
([qryBank001TellerFifty&UnderAll].[Credit OR Debit]) AS
[Total Of Credit OR Debit]
FROM [qryBank001TellerFifty&UnderAll]
GROUP BY [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID]
PIVOT [qryBank001TellerFifty&UnderAll].MonthName;

Sum adjustments:
TRANSFORM Sum([qryBank001TellerFifty&UnderAll].[Adj
Amount]) AS [The Value]
SELECT [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID], Sum
([qryBank001TellerFifty&UnderAll].[Adj Amount]) AS [Total
Of Adj Amount]
FROM [qryBank001TellerFifty&UnderAll]
GROUP BY [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID]
PIVOT [qryBank001TellerFifty&UnderAll].MonthName;
 
D

Duane Hookom

You could:
- create a third query that joins the two together
- use two subreports on a main report or
- use a method described below to create a crosstab with two values:
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


Rohit Thomas said:
Hello,

I have two crosstab queries that use the same underlying
query to sum and count the totat number of adjustments
created on a monthly basis. I would like to place both
queries on one report but am at a loss on how to do this
(showing only one is not a problem). Below are the SQL
statements for the two crosstab queries. Any thoughts on
how I can get this accomplished would be greatly
appreciated.

Thanks in advance,
Rohit Thomas

Count adjustments:
TRANSFORM Count([qryBank001TellerFifty&UnderAll].[Credit
OR Debit]) AS [The Value]
SELECT [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID], Count
([qryBank001TellerFifty&UnderAll].[Credit OR Debit]) AS
[Total Of Credit OR Debit]
FROM [qryBank001TellerFifty&UnderAll]
GROUP BY [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID]
PIVOT [qryBank001TellerFifty&UnderAll].MonthName;

Sum adjustments:
TRANSFORM Sum([qryBank001TellerFifty&UnderAll].[Adj
Amount]) AS [The Value]
SELECT [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID], Sum
([qryBank001TellerFifty&UnderAll].[Adj Amount]) AS [Total
Of Adj Amount]
FROM [qryBank001TellerFifty&UnderAll]
GROUP BY [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID]
PIVOT [qryBank001TellerFifty&UnderAll].MonthName;
Click to expand...
 
R

Rohit Thomas

Thanks Duane, that's the second time you helped me and
gave an excellent explanation. Thanks again for taking the
time to do so.

Sincerly,
Rohit
-----Original Message-----
You could:
- create a third query that joins the two together
- use two subreports on a main report or
- use a method described below to create a crosstab with two values:
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


Hello,

I have two crosstab queries that use the same underlying
query to sum and count the totat number of adjustments
created on a monthly basis. I would like to place both
queries on one report but am at a loss on how to do this
(showing only one is not a problem). Below are the SQL
statements for the two crosstab queries. Any thoughts on
how I can get this accomplished would be greatly
appreciated.

Thanks in advance,
Rohit Thomas

Count adjustments:
TRANSFORM Count([qryBank001TellerFifty&UnderAll].[Credit
OR Debit]) AS [The Value]
SELECT [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID], Count
([qryBank001TellerFifty&UnderAll].[Credit OR Debit]) AS
[Total Of Credit OR Debit]
FROM [qryBank001TellerFifty&UnderAll]
GROUP BY [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID]
PIVOT [qryBank001TellerFifty&UnderAll].MonthName;

Sum adjustments:
TRANSFORM Sum([qryBank001TellerFifty&UnderAll].[Adj
Amount]) AS [The Value]
SELECT [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID], Sum
([qryBank001TellerFifty&UnderAll].[Adj Amount]) AS [Total
Of Adj Amount]
FROM [qryBank001TellerFifty&UnderAll]
GROUP BY [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Teller ID]
PIVOT [qryBank001TellerFifty&UnderAll].MonthName;
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query based on combo box criteria 1
Crosstab query report 1
Joining Crosstab queries 3
One to Many Relationship 2
CrossTab Query Access Report - 1
Using select and crosstab queries on one report 1
crosstab & pivot table give different result 3
Combine Crosstab Queries? 2

Top