Crosstab Query display all data

[RA]

Instructions:
You have a table of people, and a related table of data?

If so, double-click the line joining the 2 tables in the
upper pane of the
query design window. Access pops up a dialog offering 3
options. Choose the
one that says, "All records from the People table, and ..."



I checked all three options and the query results are the
same.

You are correct, I have two tables, one with person's name
and office location and another with month, total hours
trained and name. Now I am pulling a report with time
spent in training by month (crosstab). I have some people
who do not recieve training every month so they do not
appear on the report until they do. SO, how do I get the
report to spit out their names and show zero.


RA

 

[Allen Browne]

Not clear what the issue is.

Switch the query to SQL View (View menu), and post the SQL statement. (About
to quit now, but will check tomorrow.)

 

[RA]

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)=
[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");

 

[Allen Browne]

You have criteria on the Year field, which comes from the related table.
Where there is no data for a specialist, this field will be Null.

In the Criteria under Year, try:
Is Null Or [Forms]![Report - Form]![combo32]

For an explanation of why this is needed, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

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

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

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)=
[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");

-----Original Message-----
Not clear what the issue is.

Switch the query to SQL View (View menu), and post the SQL statement. (About
to quit now, but will check tomorrow.)