Crosstab Query Questions

[Chip]

Hey everyone,

I've created a crosstab query. My Row heading is CourseName. My
Column Heading is County. I have 10 countes in which I'm interested
in. And 6 different courses. I am running a monthly report. Not all
courses run in each county each month. So in my Value field in my
Crosstab Query, I have it set to

Expr1: CLng(Nz(Avg([tblCourses]![StartDate]-[tblCourses]![Date
Approved]),0))

And I want the Average of these numbers so I'm using the AVG function
in the total. When I run the query, it catches the data I want and it
calculate the average. However, not al 10 counties are represented,
only those who have had courses conducted show. For example my result
is

CountyA CountyC
Course1
Course2

Course 1 may run in County A but not Course 2. So course 2 would show
a 0. Thats good and its what I want. However, CountyB does not have
any courses run. It just leaves CountyB out. I want CountyB to be
there...

Here is my SQL

PARAMETERS [Forms]![frmEOM]![StartDate] DateTime, [Forms]![frmEOM]!
[EndDate] DateTime;
TRANSFORM CLng(Nz(Avg([tblCourses]![StartDate]-[tblCourses]![Date
Approved]),0)) AS Expr1
SELECT COUNTIES.COUNTYNAME
FROM qryAvgNumDaysApptoStart, COUNTIES INNER JOIN (tblSites INNER JOIN
(tblCourseNum INNER JOIN tblCourses ON tblCourseNum.ID =
tblCourses.coursecode) ON tblSites.ID = tblCourses.TrainingSite) ON
COUNTIES.ID = tblSites.SiteCounty
WHERE (((tblCourses.[Date Approved]) Between [Forms]![frmEOM]!
[StartDate] And [Forms]![frmEOM]![EndDate]))
GROUP BY COUNTIES.COUNTYNAME
PIVOT tblCourseNum.coursename In ("EMS Instructor","Emergency First
Responder","Emergency Medical Technician","EMT Paramedic","Basic
Vehicle Rescue","Special Vehicle Rescue (89)","Basic Rescue
Practices","EMT-Bridge","Basic Vehicle Rescue (87)");

Question 1 - How do I get all of the counties to show.
Question 2 - I dont want my reader to confuse the default 0 in the Nz
statement to a 0 that is actually calculated.

Any help is great appreciated. And of course, if anyone woudl like
the 4 feet of show I have, I can send it to ya.. LOL

chip

 

[Tom van Stiphout]

On Tue, 9 Feb 2010 20:00:30 -0800 (PST), Chip

Q1: the query has a ColumnHeadings property for that.

Q2: Remove the Nz? Null is a perfectly acceptable value.

-Tom.
Microsoft Access MVP

 

[Duane Hookom]

You stated County is your column heading and your example shows it. However,
your SQL view shows County as a row heading. Your CourseName is the Column
Heading and you seem to understand how to include all columns.

To get more row headings, you would need to create a LEFT or RIGHT JOIN on a
table that contains all or the row values.

 

[Chip]

Hi Tom,

Thanks, I got the ColumnHeadings straight. I know I can manuually
enter those values to include all of the columns I could want. It
must have been hypothermia kicking in last, but its actually my Row
Headings, it only shows those Rows that have data to report. So if a
particular combinatino of County and Course do not exist (id IsNull,
accross the range) it doesnt do anything. In fact it just eliminates
that row..

What I'm measuring is the length of time it taks for us to approve a
class. And so 0 is a valid number, and in this case, it would be a
good thing.. So to calculate a 0 by average is good. But if there is
nothing to report I want to replace teh 0 with something that tells
the reader, that we didnt have data to report, rather than giving the
the false sense of how good we are.

Last night I was thinking about it. I was thinking that an excel
spreadsheet that gets its data from the database, and then import that
spreadsheet into the database might work. Any ideas?

 

[Chip]

Hi Duane,

Yes your correct, the Row heading is the County. And thats where I'm
having trouble. Can I do a similair thin with the Row Headings, as I
have done with the Column Headings.. I think the LEFT or RIGHT join
might work. Make a table that contains those rows is easy, but I'm
not sure how I would do the LEFT or RIGHT join..

 

[John Spencer]

You need to use a LEFT JOIN on Counties to get every county to appear even if
there is no data. Unfortunately, you are applying criteria on the right side
of the join and that will nullify the left join.

I also don't understand why you have qryAvgNumDaysApptoStart included in the
query.

Query one.
PARAMETERS [Forms]![frmEOM]![StartDate] DateTime
, [Forms]![frmEOM]![EndDate] DateTime;
SELECT COUNTIES.COUNTYNAME
, [tblCourses].[StartDate]
, [tblCourses].[Date Approved]
, tblCourseNum.coursename
FROM COUNTIES INNER JOIN
(tblSites INNER JOIN
(tblCourseNum INNER JOIN
tblCourses ON tblCourseNum.ID =tblCourses.coursecode)
ON tblSites.ID = tblCourses.TrainingSite)
ON COUNTIES.ID = tblSites.SiteCounty
WHERE tblCourses.[Date Approved] Between [Forms]![frmEOM]![StartDate] And
[Forms]![frmEOM]![EndDate]

Now use that and the counties table as the source for the crosstab

TRANSFORM CLng(Nz(Avg([StartDate]-[Date Approved]),0)) AS Expr1
SELECT COUNTIES.COUNTYNAME
FROM qryAvgNumDaysApptoStart, COUNTIES LEFT JOIN QueryOne
ON Counties.CountyName = QueryOne.CountyName
GROUP BY COUNTIES.COUNTYNAME
PIVOT coursename In ("EMS Instructor"
,"Emergency First Responder","Emergency Medical Technician"
,"EMT Paramedic","Basic Vehicle Rescue","Special Vehicle Rescue (89)"
,"Basic Rescue Practices","EMT-Bridge","Basic Vehicle Rescue (87)");

Again, I don't know the purpose of qryAvgNumDaysApptoStart since it none of
the fields from that query appear to be used in any manner in your query. The
only effect of including that in the query would be to return multiple records
that then get collapsed into single records. SO I would at least try removing
that from the query and see if it has any real effect on the outcome.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hey everyone,

I've created a crosstab query. My Row heading is CourseName. My
Column Heading is County. I have 10 countes in which I'm interested
in. And 6 different courses. I am running a monthly report. Not all
courses run in each county each month. So in my Value field in my
Crosstab Query, I have it set to

Expr1: CLng(Nz(Avg([tblCourses]![StartDate]-[tblCourses]![Date
Approved]),0))

And I want the Average of these numbers so I'm using the AVG function
in the total. When I run the query, it catches the data I want and it
calculate the average. However, not al 10 counties are represented,
only those who have had courses conducted show. For example my result
is

CountyA CountyC
Course1
Course2

Course 1 may run in County A but not Course 2. So course 2 would show
a 0. Thats good and its what I want. However, CountyB does not have
any courses run. It just leaves CountyB out. I want CountyB to be
there...

Here is my SQL

PARAMETERS [Forms]![frmEOM]![StartDate] DateTime, [Forms]![frmEOM]!
[EndDate] DateTime;
TRANSFORM CLng(Nz(Avg([tblCourses]![StartDate]-[tblCourses]![Date
Approved]),0)) AS Expr1
SELECT COUNTIES.COUNTYNAME
FROM qryAvgNumDaysApptoStart, COUNTIES INNER JOIN (tblSites INNER JOIN
(tblCourseNum INNER JOIN tblCourses ON tblCourseNum.ID =
tblCourses.coursecode) ON tblSites.ID = tblCourses.TrainingSite) ON
COUNTIES.ID = tblSites.SiteCounty
WHERE (((tblCourses.[Date Approved]) Between [Forms]![frmEOM]!
[StartDate] And [Forms]![frmEOM]![EndDate]))
GROUP BY COUNTIES.COUNTYNAME
PIVOT tblCourseNum.coursename In ("EMS Instructor","Emergency First
Responder","Emergency Medical Technician","EMT Paramedic","Basic
Vehicle Rescue","Special Vehicle Rescue (89)","Basic Rescue
Practices","EMT-Bridge","Basic Vehicle Rescue (87)");

Question 1 - How do I get all of the counties to show.
Question 2 - I dont want my reader to confuse the default 0 in the Nz
statement to a 0 that is actually calculated.

Any help is great appreciated. And of course, if anyone woudl like
the 4 feet of show I have, I can send it to ya.. LOL

chip