AVG crosstab query with one field containing text

[Gntlhnds]

I have a crosstab query that I use to display grades in a report. All but one
of my classes uses numeric grades, which it uses a letter grade (just to show
the class was taken). In the crosstab query I've figured out how to get a
grade average for each student without that one class included, but I have
yet to figure out how to do it with it included. I get a "Datatype mismatch
in criteria expression" error. Here's my SQL (Please no lectures about
naming conventions, I inherited this database and I've tried changing them,
but gave up trying to hunt down everywhere the fields are used):

TRANSFORM Avg(tblCourseGrades.Grade) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

Any help would be greatly appreciated.

 

[Gntlhnds]

So if I change the data type of the [Grade] field in tblStudentGrades to
number, my query will work, but I lose all my text grades (of course). How
should I go about showing this class, with a grade that won't send my query
into a conniption and still average the grades for the student without
including that one class? Thanks for the help.

 

[Daryl S]

Gntlhnds -

I think pass/fail classes are not included in calculating a GPA, so exclude
them from your query. You will need numeric grades in order to calculate an
average for the remaining classes. Depending on your needs, you may want a
column for numeric grades and one for letter grades. Or you can convert each
grade to a number if the text value is numeric, and exclude those that
aren't. Using what you have, converting the Grade to a number and excluding
records where the grade cannot be converted to a number, you get this:

TRANSFORM Avg(Val(tblCourseGrades.Grade)) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
WHERE IsNumeric(tblCourseGrades.Grade)
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

--
Daryl S

So if I change the data type of the [Grade] field in tblStudentGrades to
number, my query will work, but I lose all my text grades (of course). How
should I go about showing this class, with a grade that won't send my query
into a conniption and still average the grades for the student without
including that one class? Thanks for the help.

I have a crosstab query that I use to display grades in a report. All but one
of my classes uses numeric grades, which it uses a letter grade (just to show
the class was taken). In the crosstab query I've figured out how to get a
grade average for each student without that one class included, but I have
yet to figure out how to do it with it included. I get a "Datatype mismatch
in criteria expression" error. Here's my SQL (Please no lectures about
naming conventions, I inherited this database and I've tried changing them,
but gave up trying to hunt down everywhere the fields are used):

TRANSFORM Avg(tblCourseGrades.Grade) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

Any help would be greatly appreciated.

 

[Gntlhnds]

I think this works, but when I run my report I need to include the fields
that don't have any grades entered (I currently have three classes that no
student has taken yet, therefore the value in the field is Null) and I would
prefer if that one class that has a text value rather than a numeric value
for its grade show up as Null as well so I can include it in my report.
Using your method, it doesn't show up in the list of fields available for my
report.

Gntlhnds -

I think pass/fail classes are not included in calculating a GPA, so exclude
them from your query. You will need numeric grades in order to calculate an
average for the remaining classes. Depending on your needs, you may want a
column for numeric grades and one for letter grades. Or you can convert each
grade to a number if the text value is numeric, and exclude those that
aren't. Using what you have, converting the Grade to a number and excluding
records where the grade cannot be converted to a number, you get this:

TRANSFORM Avg(Val(tblCourseGrades.Grade)) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
WHERE IsNumeric(tblCourseGrades.Grade)
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

--
Daryl S

So if I change the data type of the [Grade] field in tblStudentGrades to
number, my query will work, but I lose all my text grades (of course). How
should I go about showing this class, with a grade that won't send my query
into a conniption and still average the grades for the student without
including that one class? Thanks for the help.

I have a crosstab query that I use to display grades in a report. All but one
of my classes uses numeric grades, which it uses a letter grade (just to show
the class was taken). In the crosstab query I've figured out how to get a
grade average for each student without that one class included, but I have
yet to figure out how to do it with it included. I get a "Datatype mismatch
in criteria expression" error. Here's my SQL (Please no lectures about
naming conventions, I inherited this database and I've tried changing them,
but gave up trying to hunt down everywhere the fields are used):

TRANSFORM Avg(tblCourseGrades.Grade) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

Any help would be greatly appreciated.

 

[Daryl S]

Gntlhnds -

If you want to just include the records where the grade is null, use this
for the WHERE clause:
WHERE IsNumeric(tblCourseGrades.Grade) OR (tblCourseGrades.Grade Is Null)

If you want to include them and treat them as a zero in your average grades,
then use this:
WHERE IsNumeric(isnull(tblCourseGrades.Grade,0))


--
Daryl S

I think this works, but when I run my report I need to include the fields
that don't have any grades entered (I currently have three classes that no
student has taken yet, therefore the value in the field is Null) and I would
prefer if that one class that has a text value rather than a numeric value
for its grade show up as Null as well so I can include it in my report.
Using your method, it doesn't show up in the list of fields available for my
report.

Gntlhnds -

I think pass/fail classes are not included in calculating a GPA, so exclude
them from your query. You will need numeric grades in order to calculate an
average for the remaining classes. Depending on your needs, you may want a
column for numeric grades and one for letter grades. Or you can convert each
grade to a number if the text value is numeric, and exclude those that
aren't. Using what you have, converting the Grade to a number and excluding
records where the grade cannot be converted to a number, you get this:

TRANSFORM Avg(Val(tblCourseGrades.Grade)) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
WHERE IsNumeric(tblCourseGrades.Grade)
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

--
Daryl S

So if I change the data type of the [Grade] field in tblStudentGrades to
number, my query will work, but I lose all my text grades (of course). How
should I go about showing this class, with a grade that won't send my query
into a conniption and still average the grades for the student without
including that one class? Thanks for the help.

:

I have a crosstab query that I use to display grades in a report. All but one
of my classes uses numeric grades, which it uses a letter grade (just to show
the class was taken). In the crosstab query I've figured out how to get a
grade average for each student without that one class included, but I have
yet to figure out how to do it with it included. I get a "Datatype mismatch
in criteria expression" error. Here's my SQL (Please no lectures about
naming conventions, I inherited this database and I've tried changing them,
but gave up trying to hunt down everywhere the fields are used):

TRANSFORM Avg(tblCourseGrades.Grade) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

Any help would be greatly appreciated.

 

[Gntlhnds]

Since I do want it to treat the field as Null, I'm back to getting the "Data
type mismatch..." error (I had changed the [Grade] field back to text so I
could input the "P" grade). By getting creative with parentheses I can get
the error to go away, but my query will not output the blank columns.

And for the report I'm creating, I do need the field that has the
non-numeric grade. I might just have to get creative for the record source
for my report.

Gntlhnds -

If you want to just include the records where the grade is null, use this
for the WHERE clause:
WHERE IsNumeric(tblCourseGrades.Grade) OR (tblCourseGrades.Grade Is Null)

If you want to include them and treat them as a zero in your average grades,
then use this:
WHERE IsNumeric(isnull(tblCourseGrades.Grade,0))


--
Daryl S

I think this works, but when I run my report I need to include the fields
that don't have any grades entered (I currently have three classes that no
student has taken yet, therefore the value in the field is Null) and I would
prefer if that one class that has a text value rather than a numeric value
for its grade show up as Null as well so I can include it in my report.
Using your method, it doesn't show up in the list of fields available for my
report.

Gntlhnds -

I think pass/fail classes are not included in calculating a GPA, so exclude
them from your query. You will need numeric grades in order to calculate an
average for the remaining classes. Depending on your needs, you may want a
column for numeric grades and one for letter grades. Or you can convert each
grade to a number if the text value is numeric, and exclude those that
aren't. Using what you have, converting the Grade to a number and excluding
records where the grade cannot be converted to a number, you get this:

TRANSFORM Avg(Val(tblCourseGrades.Grade)) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
WHERE IsNumeric(tblCourseGrades.Grade)
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

--
Daryl S


:

So if I change the data type of the [Grade] field in tblStudentGrades to
number, my query will work, but I lose all my text grades (of course). How
should I go about showing this class, with a grade that won't send my query
into a conniption and still average the grades for the student without
including that one class? Thanks for the help.

:

I have a crosstab query that I use to display grades in a report. All but one
of my classes uses numeric grades, which it uses a letter grade (just to show
the class was taken). In the crosstab query I've figured out how to get a
grade average for each student without that one class included, but I have
yet to figure out how to do it with it included. I get a "Datatype mismatch
in criteria expression" error. Here's my SQL (Please no lectures about
naming conventions, I inherited this database and I've tried changing them,
but gave up trying to hunt down everywhere the fields are used):

TRANSFORM Avg(tblCourseGrades.Grade) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

Any help would be greatly appreciated.

 

[Gntlhnds]

Maybe what I could do is change the [Grade] field back to a number datatype,
input the grade for that one class as 100, and when I compute the average
grade for the student, have the formula ignore that field. How would my Avg
function be written in my SQL statement in that case?

Since I do want it to treat the field as Null, I'm back to getting the "Data
type mismatch..." error (I had changed the [Grade] field back to text so I
could input the "P" grade). By getting creative with parentheses I can get
the error to go away, but my query will not output the blank columns.

And for the report I'm creating, I do need the field that has the
non-numeric grade. I might just have to get creative for the record source
for my report.

Gntlhnds -

If you want to just include the records where the grade is null, use this
for the WHERE clause:
WHERE IsNumeric(tblCourseGrades.Grade) OR (tblCourseGrades.Grade Is Null)

If you want to include them and treat them as a zero in your average grades,
then use this:
WHERE IsNumeric(isnull(tblCourseGrades.Grade,0))


--
Daryl S

I think this works, but when I run my report I need to include the fields
that don't have any grades entered (I currently have three classes that no
student has taken yet, therefore the value in the field is Null) and I would
prefer if that one class that has a text value rather than a numeric value
for its grade show up as Null as well so I can include it in my report.
Using your method, it doesn't show up in the list of fields available for my
report.

:

Gntlhnds -

I think pass/fail classes are not included in calculating a GPA, so exclude
them from your query. You will need numeric grades in order to calculate an
average for the remaining classes. Depending on your needs, you may want a
column for numeric grades and one for letter grades. Or you can convert each
grade to a number if the text value is numeric, and exclude those that
aren't. Using what you have, converting the Grade to a number and excluding
records where the grade cannot be converted to a number, you get this:

TRANSFORM Avg(Val(tblCourseGrades.Grade)) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
WHERE IsNumeric(tblCourseGrades.Grade)
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

--
Daryl S


:

So if I change the data type of the [Grade] field in tblStudentGrades to
number, my query will work, but I lose all my text grades (of course). How
should I go about showing this class, with a grade that won't send my query
into a conniption and still average the grades for the student without
including that one class? Thanks for the help.

:

I have a crosstab query that I use to display grades in a report. All but one
of my classes uses numeric grades, which it uses a letter grade (just to show
the class was taken). In the crosstab query I've figured out how to get a
grade average for each student without that one class included, but I have
yet to figure out how to do it with it included. I get a "Datatype mismatch
in criteria expression" error. Here's my SQL (Please no lectures about
naming conventions, I inherited this database and I've tried changing them,
but gave up trying to hunt down everywhere the fields are used):

TRANSFORM Avg(tblCourseGrades.Grade) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

Any help would be greatly appreciated.

 

[Daryl S]

Gntlhnds -

I think this can be done. I would start with the plain SELECT (without the
TRANSFORM / PIVOT pieces) until you get it right, then add back the TRANSFORM
/ PIVOT pieces.

You will have to calculate the average instead of use the AVG function. I
would add in the next three items in the SELECT clause, and remove the
Avg(tblCourseGrades.Grade) AS AvgOfGrade1 to start with. I don't know how to
tell which courses have no grades, but if it is just one course, and that is
known from the CourseID, then add these three fields to the SELECT statement
(subsititing the courseID for xx):

sum(IIF(tblCourseGrades.CourseID = xx,0,[Grade]) As SumOfGrades,
sum(IIF(tblCourseGrades.CourseID = xx,0,1) As CountOfGrades,
sum(IIF(tblCourseGrades.CourseID = xx,0,[Grade])/
sum(IIF(tblCourseGrades.CourseID = xx,0,1) As AverageGrade

The first line will sum all the grades, but if the CourseID is xx, it will
only sum in a zero.
The second line counts the grades, summing a 1 for all courses except xx,
which it sums in as zero.
The third would be the calculation of the average based on the new sum and
count.

If you get stuck, post the SQL statement so we can help.

--
Daryl S

Maybe what I could do is change the [Grade] field back to a number datatype,
input the grade for that one class as 100, and when I compute the average
grade for the student, have the formula ignore that field. How would my Avg
function be written in my SQL statement in that case?

Since I do want it to treat the field as Null, I'm back to getting the "Data
type mismatch..." error (I had changed the [Grade] field back to text so I
could input the "P" grade). By getting creative with parentheses I can get
the error to go away, but my query will not output the blank columns.

And for the report I'm creating, I do need the field that has the
non-numeric grade. I might just have to get creative for the record source
for my report.

Gntlhnds -

If you want to just include the records where the grade is null, use this
for the WHERE clause:
WHERE IsNumeric(tblCourseGrades.Grade) OR (tblCourseGrades.Grade Is Null)

If you want to include them and treat them as a zero in your average grades,
then use this:
WHERE IsNumeric(isnull(tblCourseGrades.Grade,0))


--
Daryl S


:

I think this works, but when I run my report I need to include the fields
that don't have any grades entered (I currently have three classes that no
student has taken yet, therefore the value in the field is Null) and I would
prefer if that one class that has a text value rather than a numeric value
for its grade show up as Null as well so I can include it in my report.
Using your method, it doesn't show up in the list of fields available for my
report.

:

Gntlhnds -

I think pass/fail classes are not included in calculating a GPA, so exclude
them from your query. You will need numeric grades in order to calculate an
average for the remaining classes. Depending on your needs, you may want a
column for numeric grades and one for letter grades. Or you can convert each
grade to a number if the text value is numeric, and exclude those that
aren't. Using what you have, converting the Grade to a number and excluding
records where the grade cannot be converted to a number, you get this:

TRANSFORM Avg(Val(tblCourseGrades.Grade)) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
WHERE IsNumeric(tblCourseGrades.Grade)
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

--
Daryl S


:

So if I change the data type of the [Grade] field in tblStudentGrades to
number, my query will work, but I lose all my text grades (of course). How
should I go about showing this class, with a grade that won't send my query
into a conniption and still average the grades for the student without
including that one class? Thanks for the help.

:

I have a crosstab query that I use to display grades in a report. All but one
of my classes uses numeric grades, which it uses a letter grade (just to show
the class was taken). In the crosstab query I've figured out how to get a
grade average for each student without that one class included, but I have
yet to figure out how to do it with it included. I get a "Datatype mismatch
in criteria expression" error. Here's my SQL (Please no lectures about
naming conventions, I inherited this database and I've tried changing them,
but gave up trying to hunt down everywhere the fields are used):

TRANSFORM Avg(tblCourseGrades.Grade) AS AvgOfGrade
SELECT TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1
FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCourseGrades ON
tblCourses.CourseID = tblCourseGrades.CourseID) ON TblStudents.StudentID =
tblCourseGrades.StudentID
GROUP BY TblStudents.[Class #], TblStudents.Rank, TblStudents.[Last Name],
TblStudents.[First Name]
ORDER BY TblStudents.[Class #], TblStudents.[Last Name]
PIVOT tblCourses.Course;

Any help would be greatly appreciated.