Crosstab Query display all data

  • Thread starter Thread starter RA
  • Start date Start date
R

RA

Allen,

I guess I'm just confused. The latest suggestion doesn't
help either.

My tables are:

Month: Just for ordering purposes, contains Month Name and
order ie Jan 1, Feb 2 etc...

Specialist: Center name (work location) and Specialist name

Training data: Month, Year, Specialist, Training hours

All fields are populated in all tables. The only "null"
situation would be when no data is entered in
the "Training Data" table for a specialist who recieved no
traing for a month. EX I may input Joe Smoe for Jan, 2
hours: but no data input for Harold for Jan. Now I want a
report by month that says Joe has 2 hours in Jan and 0 for
Harold.

Any other suggestions?

Thanks for you help!

RA
 
Did you try:
TRANSFORM Sum([Training Data - Table].[Training Hours]) AS [SumOfTraining
Hours]
SELECT [Specialist - Table].Specialist, Sum([Training Data -
Table].[Training Hours]) AS [Total Of Training Hours]

FROM [Specialist - Table] LEFT JOIN [Training Data - Table] ON [Specialist -
Table].Specialist = [Training Data - Table].Specialist

WHERE (([Specialist - Table].Center = [Forms]![Report - Form]![combo26]) AND
(([Training Data - Table].Year Is Null) OR ([Training Data - Table].Year =
[Forms]![Report - Form]![combo32])))

GROUP BY [Specialist - Table].Specialist

PIVOT [Training Data - Table].Month In
("January","February","March","April","May","June","July","
August","September","October","November","December");


What result?
 
I'm not quite sure what was changed, but it worked,

Thanks

RA
-----Original Message-----
Did you try:
TRANSFORM Sum([Training Data - Table].[Training Hours]) AS [SumOfTraining
Hours]
SELECT [Specialist - Table].Specialist, Sum([Training Data -
Table].[Training Hours]) AS [Total Of Training Hours]

FROM [Specialist - Table] LEFT JOIN [Training Data - Table] ON [Specialist -
Table].Specialist = [Training Data - Table].Specialist

WHERE (([Specialist - Table].Center = [Forms]![Report - Form]![combo26]) AND
(([Training Data - Table].Year Is Null) OR ([Training Data - Table].Year =
[Forms]![Report - Form]![combo32])))

GROUP BY [Specialist - Table].Specialist

PIVOT [Training Data - Table].Month In
("January","February","March","April","May","June","July","
August","September","October","November","December");


What result?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I guess I'm just confused. The latest suggestion doesn't
help either.

My tables are:

Month: Just for ordering purposes, contains Month Name and
order ie Jan 1, Feb 2 etc...

Specialist: Center name (work location) and Specialist name

Training data: Month, Year, Specialist, Training hours

All fields are populated in all tables. The only "null"
situation would be when no data is entered in
the "Training Data" table for a specialist who recieved no
traing for a month. EX I may input Joe Smoe for Jan, 2
hours: but no data input for Harold for Jan. Now I want a
report by month that says Joe has 2 hours in Jan and 0 for
Harold.

Any other suggestions?

Thanks for you help!

RA
Click to expand...


.
Click to expand...
 
Back
Top