Crosstab Combine

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

Jeremy Noland

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...
 
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...
Click to expand...
--------------------
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.
 
Back
Top