Complex Crosstab Query

[Jeremy Noland]

I've asked for help on a crosstab (currently on page 9).
Basically I have two separate tables (Training_Table, and
Background_Table) :

From the two tables I need to count the number of
[Training_Table].[Training_Type] which is a list of MPH,
MSPH, DrPH, Post Docs, Distance Learning, Lab-Training,
Course, or Mentoring, and group this way :

Distance Learning, Lab-Training, Mentoring, Course as one
single type (Short Term)

MPH and MSPH as one single type

Count the number of Females and Males (which I have the
data stored in : [Background_Table].[Sex] as 'M' or 'F'
and THEN group by Country of Origin ( [Background_Table].
[Country]. It should look like this (in a report):

MS/MPH DrPH Post Docs Short Term
M F M F M F M F
Country:
Bangladesh 0 0 1 0 10 4 8 17
China 1 0 0 0 0 3 12 9
India 0 0 0 4 2 0 0 0

And so on...Can anyone help...I've tried SQL code, and
Inner Joins, and cannot get this to work out combined by
types, etc.

Thanks in advance,
Jeremy

 

[Guest]

Ok...and I have a Crosstab now with the respective
Training_Types I previously discussed, and Country as a
group by rowHeading. Why will the following SQL not work
(b/c there are null values in some of the fields):

SELECT Report07_SummaryCross.Country, Sum([CourseM] And
[Distance LearningM] And [MentoringM]) AS Short_Male
FROM Report07_SummaryCross
GROUP BY Report07_SummaryCross.Country;

Everything is Blank, but It works if ALL three fields have
a value. I thought count initialized to 0 anyway.

Thanks in advance again.

 

[Jeremy Noland]

Okay. Now I have zeros across in my crosstab (using IIF
and IsNull)! Looks like this:

Country CourseF CourseM Distance LearningM ...
Bangladesh 1 1 0
Barbados 0 0 0
China 0 2 0

Now I have a Select Query that I want to Sum together the
types I previously stated needed to be grouped together
(MSPH and MPH, Distance Learning - Lab Training -
Mentoring - and Course).
Here is the SQL:

SELECT Report07_SummaryCross.Country, Sum([CourseM] And
[Distance LearningM] And [MentoringM]) AS Short_Male
FROM Report07_SummaryCross
GROUP BY Report07_SummaryCross.Country;

All 0z, What do I need to use between the different Fields?