Crosstab Query display all data

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

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
 
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.)
 
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");
 
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.

RA said:
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.)
Click to expand...
Click to expand...
 
Back
Top