Complex Crosstab Query

  • Thread starter Thread starter Jeremy Noland
  • Start date Start date
J

Jeremy Noland

Here is the SQL code :

TRANSFORM Count([Background_Table].[Sex]) AS CountOfSex
SELECT [Background_Table].[Sex], [Training_Table].
[Training_Type]
FROM Background_Table, Training_Table
WHERE ((([Background_Table].[Sex])="M" Or
([Background_Table].[Sex])="F") And (([Training_Table].
[First_Name] & [Training_Table].[Last_Name])=
[Background_Table].[First_Name] & [Background_Table].
[Last_Name]))
GROUP BY [Background_Table].[Sex], [Training_Table].
[Training_Type]
ORDER BY [Training_Table].[Training_Type],
[Background_Table].[Country]
PIVOT [Background_Table].[Country];

Training_Type is a group:
MSPH, MPH, DrPH, Post Docs, Lab-Training, Distance
Learning, Mentoring, and Course
Sex is of course either Male or Female ('M' or 'F').
First of all, the Tables do not have any Lab-Training
Types as of yet : but here is what I need -
Combine MSPH and MPH Count M
Combine MSPH and MPH Count F
....
Combine Lab-Training, Distance Learning, Mentoring, and
Course Count M and another for F (all of these as Short-
Term)

And Then need it to come out in a nice Excel type Report
that looks somewhat like this :

COUNTRY MS/MPH DrPH Short-Term
Male Female Male Female Male Female
Bangladesh 1 0 1 1 4 5
China 0 0 0 0 2 1
India 3 1 1 0 8 2
....

HELP!
 
It looks like you want a multiple value crosstab. If the following still
leaves you short of your goal, come on back.
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 Hookom
MS Access MVP


Jeremy Noland said:
Here is the SQL code :

TRANSFORM Count([Background_Table].[Sex]) AS CountOfSex
SELECT [Background_Table].[Sex], [Training_Table].
[Training_Type]
FROM Background_Table, Training_Table
WHERE ((([Background_Table].[Sex])="M" Or
([Background_Table].[Sex])="F") And (([Training_Table].
[First_Name] & [Training_Table].[Last_Name])=
[Background_Table].[First_Name] & [Background_Table].
[Last_Name]))
GROUP BY [Background_Table].[Sex], [Training_Table].
[Training_Type]
ORDER BY [Training_Table].[Training_Type],
[Background_Table].[Country]
PIVOT [Background_Table].[Country];

Training_Type is a group:
MSPH, MPH, DrPH, Post Docs, Lab-Training, Distance
Learning, Mentoring, and Course
Sex is of course either Male or Female ('M' or 'F').
First of all, the Tables do not have any Lab-Training
Types as of yet : but here is what I need -
Combine MSPH and MPH Count M
Combine MSPH and MPH Count F
...
Combine Lab-Training, Distance Learning, Mentoring, and
Course Count M and another for F (all of these as Short-
Term)

And Then need it to come out in a nice Excel type Report
that looks somewhat like this :

COUNTRY MS/MPH DrPH Short-Term
Male Female Male Female Male Female
Bangladesh 1 0 1 1 4 5
China 0 0 0 0 2 1
India 3 1 1 0 8 2
...

HELP!
Click to expand...
 
I only get a column heading : Product Name with nothing
as a row heading , and no records! I copied the SQL code
and did it this way, as you said.

Still stuck,
Jeremy
 
Back
Top