If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complex Crosstab Query
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! |
#2
|
|||
|
|||
Complex Crosstab Query
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" wrote in message ... 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! |
#3
|
|||
|
|||
Complex Crosstab Query
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 -----Original Message----- 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 |
Thread Tools | |
Display Modes | |
|
|