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
|
|||
|
|||
Crosstab Combine
Here is the SQL for my Crosstab :
TRANSFORM IIf(IsNull(Count([Sex])),0,Count([Sex])) AS DaVal SELECT Background_Table.Country FROM Background_Table, Training_Table WHERE ((([Background_Table].[First_Name] & [Background_Table].[Last_Name])=[Training_Table]. [First_Name] & [Training_Table].[Last_Name]) AND ((Background_Table.Sex)="M" Or (Background_Table.Sex)="F")) GROUP BY Background_Table.Country PIVOT [Training_Type] & [Sex]; So far I have what I want...now I want to combine different Training_Types AND get the sum of the two in replacement : SELECT Report07_SummaryCross.Country, Sum([CourseM] And [Distance LearningM] And [MentoringM]) AS Short_Male, Sum ([CourseF] And [MentoringF]) AS Short_Female, Report07_SummaryCross.DrPHF, Report07_SummaryCross.DrPHM, Sum([MPHM] And [MSPHM]) AS MSMPH_Male FROM Report07_SummaryCross GROUP BY Report07_SummaryCross.Country, Report07_SummaryCross.DrPHF, Report07_SummaryCross.DrPHM; Anyone help me, as Here I find out the Sum function doesn't work as I want it to. (negative numbers, etc.) I need a function that will calculate across multiple fields from the crosstab query. Thanks... |
#2
|
|||
|
|||
Crosstab Combine
Here is the SQL for my Crosstab :
TRANSFORM IIf(IsNull(Count([Sex])),0,Count([Sex])) AS DaVal SELECT Background_Table.Country FROM Background_Table, Training_Table WHERE ((([Background_Table].[First_Name] & [Background_Table].[Last_Name])=[Training_Table]. [First_Name] & [Training_Table].[Last_Name]) AND ((Background_Table.Sex)="M" Or (Background_Table.Sex)="F")) GROUP BY Background_Table.Country PIVOT [Training_Type] & [Sex]; So far I have what I want...now I want to combine different Training_Types AND get the sum of the two in replacement : SELECT Report07_SummaryCross.Country, Sum([CourseM] And [Distance LearningM] And [MentoringM]) AS Short_Male, Sum ([CourseF] And [MentoringF]) AS Short_Female, Report07_SummaryCross.DrPHF, Report07_SummaryCross.DrPHM, Sum([MPHM] And [MSPHM]) AS MSMPH_Male FROM Report07_SummaryCross GROUP BY Report07_SummaryCross.Country, Report07_SummaryCross.DrPHF, Report07_SummaryCross.DrPHM; Anyone help me, as Here I find out the Sum function doesn't work as I want it to. (negative numbers, etc.) I need a function that will calculate across multiple fields from the crosstab query. Thanks... -------------------- It would be easier if you just CREATE A NESTED QUERY: - Create the query with the crosstab results; - Create another query based on the first query, to tally across multiple fields. -- Eric Cárdenas Senior support professional This posting is provided "AS IS" with no warranties, and confers no rights. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Crosstab - Make table | DEI | Running & Setting Up Queries | 1 | June 21st, 2004 06:27 PM |
Complex Crosstab Query | Jeremy Noland | Running & Setting Up Queries | 2 | June 20th, 2004 10:49 PM |
Crosstab query with same VALUE in multiple columns | ChrisJ | Running & Setting Up Queries | 1 | June 20th, 2004 10:41 PM |
Crosstab w/ aggregate criteria | Leann | Running & Setting Up Queries | 4 | June 18th, 2004 07:19 PM |
Showing all subrows in crosstab query | Ragnar Midtskogen | Running & Setting Up Queries | 3 | May 26th, 2004 08:16 PM |