Calculating GPA.

[Zushiba]

I've got a table that looks like this

id,Name,question1,question2,question3,question4,question5,question6,question7,question8,question9,question10,question11,question12

The possible ranges of data in any one of the questions is 4,3,2,1,0 and 9
(9 being a missing or Not Applicable response)
The 9 is what's screwing me up.
I need to count the amount of returned data in each question then devide by
the count of returned answeres ignoring any 9s that show up.

I've tried many ways so far and nothing I try seems to work. In a more
complete version of SQL I could probably do it but Access's (2003 if you're
wondering) version is so limited I can't seem to do it without writing 5 or 6
queries per question.

The output is to go into a report that people who don't know a thing about
access can run and print.
the eventual output I'm looking for would show question1: 4.00 question 2:
3.96 question 3: 2.69 etc etc.


I've gotten far enough using REPLACE (To replace the 9 with a 0) to SUM all
of the data properly in 1 query but I'm stuck on finding a way to COUNT how
many 4,3,2,1,0's were returned while ignoring the 9 so I can get my average.
Is there an easy way to do this?

 

[John Spencer (MVP)]

Instead of replacing 9 with zero, replace 9 with null. Nulls are not counted
by the count function

Since I'm not sure what you are attempting to calculate for each question, I
find it difficult to answer further.

SELECT Avg(Question1) as Q1Avg
, Avg(Question2) as Q2Avg
FROM YourTable

Or if you want to avoid replacing the 9 with null.

Select Avg(IIF(Question1=9,Null,Question1) as Q1Avg



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

 

[John W. Vinson]

I've got a table that looks like this

id,Name,question1,question2,question3,question4,question5,question6,question7,question8,question9,question10,question11,question12

Then you don't have a table - you have a spreadsheet. This design is *simply
wrong* for a relational database!

A better design would have *three tables*:

Students
StudentID
LastName
FirstName

Questions
QuestionNo
Question <text>

Answers
StudentID <who answered the question>
QuestionNo <which question>
Answer <your 4/3/2/1/0/9 value>

"Fields are expensive, records are cheap".

The possible ranges of data in any one of the questions is 4,3,2,1,0 and 9
(9 being a missing or Not Applicable response)
The 9 is what's screwing me up.
I need to count the amount of returned data in each question then devide by
the count of returned answeres ignoring any 9s that show up.

I've tried many ways so far and nothing I try seems to work. In a more
complete version of SQL I could probably do it but Access's (2003 if you're
wondering) version is so limited I can't seem to do it without writing 5 or 6
queries per question.

Your problem is not due to Access' limitations (you can do just about anything
in Access that you can do in SQL/Server or Oracle) but your incorrect table
design.

What you can do is use an expression such as

IIF([Question5] = 9, 0, [Question5])

to replace the 9's by 0's in your averaging.

 

[Zushiba]

I've tried to replace 9's with Null but the REPLACE function doesn't seem to
allow it.
I should have explained the data better. Each question is a range between
A,B,C,D,E,F and Missing

Lets say I have 1 A and 2 C's
A=4, B=3, C=2, D=1, F=0, Missing=9

So one A = 4 and 2 C's = 2+2 or 4. So to get a GPA I average those numbers.
(4+2+2)/3
(6+2)/3
(8)/3
8/3
which gives me a GPA of 2.66

Select Avg(IIF(Question1=9,Null,Question1) as Q1Avg
may be all I needed.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The description of your problem is hard to understand and your table
design is not in Normal Form.

It sounds like you want the percent of the specific answers to each
question, excluding the answer "9" from all answers.


Access (Jet) SQL will do the job for you very easily if you design your
table correctly - your table design should be like this:

Fields:
ID
Name
Question - the number of the question
Answer - the answer to the question

The data would look like this (I'm not sure how ur using ID [one ID per
record or one ID per name? - no matter]):

ID Name Question Answer
==================================
25 Joe 1 9
30 Joe 2 1
45 Shirley 3 0
.... etc. ...


Then you can run a query like this to get the count of each answer per
question):

SELECT question, answer, COUNT(*) As AnswerCount
FROM survey
WHERE answer <> 9
GROUP BY question, answer


and a query like this to get your percents:

SELECT question, answer, COUNT(*) /
(SELECT COUNT(*) FROM survey
WHERE question = S.question AND answer <> 9) As pct
FROM survey As S
WHERE answer <> 9
GROUP BY question, answer

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSW+x8oechKqOuFEgEQLJCgCg82fMlei/eYGhJoIayAwVLw4ndVAAoNUN
BpdkCBWtdTqjkxMJ8VYl8EtB
=Fe8E
-----END PGP SIGNATURE-----

 

[Zushiba]

I agree, though the names of the people are linked to another table. I didn't
design the thing I just have to support it by virtue of my position.

I've got a table that looks like this

id,Name,question1,question2,question3,question4,question5,question6,question7,question8,question9,question10,question11,question12

Then you don't have a table - you have a spreadsheet. This design is *simply
wrong* for a relational database!

A better design would have *three tables*:

Students
StudentID
LastName
FirstName

Questions
QuestionNo
Question <text>

Answers
StudentID <who answered the question>
QuestionNo <which question>
Answer <your 4/3/2/1/0/9 value>

"Fields are expensive, records are cheap".

The possible ranges of data in any one of the questions is 4,3,2,1,0 and 9
(9 being a missing or Not Applicable response)
The 9 is what's screwing me up.
I need to count the amount of returned data in each question then devide by
the count of returned answeres ignoring any 9s that show up.

I've tried many ways so far and nothing I try seems to work. In a more
complete version of SQL I could probably do it but Access's (2003 if you're
wondering) version is so limited I can't seem to do it without writing 5 or 6
queries per question.

Your problem is not due to Access' limitations (you can do just about anything
in Access that you can do in SQL/Server or Oracle) but your incorrect table
design.

What you can do is use an expression such as

IIF([Question5] = 9, 0, [Question5])

to replace the 9's by 0's in your averaging.
--

John W. Vinson [MVP]

The output is to go into a report that people who don't know a thing about
access can run and print.
the eventual output I'm looking for would show question1: 4.00 question 2:
3.96 question 3: 2.69 etc etc.


I've gotten far enough using REPLACE (To replace the 9 with a 0) to SUM all
of the data properly in 1 query but I'm stuck on finding a way to COUNT how
many 4,3,2,1,0's were returned while ignoring the 9 so I can get my average.
Is there an easy way to do this?

 

[Douglas J. Steele]

You can still implement John's suggestion for your calculation.

Create a UNION query along the lines of

SELECT id, [Name], 1 As QuestionNb, question1 As QuestionValue
FROM MyTable
UNION
SELECT id, [Name], 2, question2
FROM MyTable
UNION
SELECT id, [Name], 3, question3
FROM MyTable
....
UNION
SELECT id, [Name], 12, question12
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I agree, though the names of the people are linked to another table. I
didn't
design the thing I just have to support it by virtue of my position.

I've got a table that looks like this

id,Name,question1,question2,question3,question4,question5,question6,question7,question8,question9,question10,question11,question12

Then you don't have a table - you have a spreadsheet. This design is
*simply
wrong* for a relational database!

A better design would have *three tables*:

Students
StudentID
LastName
FirstName

Questions
QuestionNo
Question <text>

Answers
StudentID <who answered the question>
QuestionNo <which question>
Answer <your 4/3/2/1/0/9 value>

"Fields are expensive, records are cheap".

The possible ranges of data in any one of the questions is 4,3,2,1,0 and
9
(9 being a missing or Not Applicable response)
The 9 is what's screwing me up.
I need to count the amount of returned data in each question then devide
by
the count of returned answeres ignoring any 9s that show up.

I've tried many ways so far and nothing I try seems to work. In a more
complete version of SQL I could probably do it but Access's (2003 if
you're
wondering) version is so limited I can't seem to do it without writing 5
or 6
queries per question.

Your problem is not due to Access' limitations (you can do just about
anything
in Access that you can do in SQL/Server or Oracle) but your incorrect
table
design.

What you can do is use an expression such as

IIF([Question5] = 9, 0, [Question5])

to replace the 9's by 0's in your averaging.
--

John W. Vinson [MVP]

The output is to go into a report that people who don't know a thing
about
access can run and print.
the eventual output I'm looking for would show question1: 4.00 question
2:
3.96 question 3: 2.69 etc etc.


I've gotten far enough using REPLACE (To replace the 9 with a 0) to SUM
all
of the data properly in 1 query but I'm stuck on finding a way to COUNT
how
many 4,3,2,1,0's were returned while ignoring the 9 so I can get my
average.
Is there an easy way to do this?

 

[Zushiba]

The problem with replacing 9's with 0's is that a 0's value is still 1. So if
I have the following dataset
4,3,2,3,4,9 for question 1 in 6 records and I replace that 9 with a zero
then AVG() that it still devides by 6 instead of 5 like I would need it too.
The difference between the two calculations is

9 being converted to a Null and therefor not counted towards the total
number of returned records
(4+3+2+3+4)/5
(7+2+3+4)/5
(9+3+4)/5
(12+4)/5
(16)/5
16/5
3.2

vs
9 being converted to a 0 and taking a place in the record count with AVG()
(4+3+2+3+4+0)/6
(7+2+3+4+0)/6
(9+3+4+0)/6
(12+4+0)/6
(16+0)/6
(16)/6
16/6
2.6666666666666666666666666667

I've got a lot of very good information in this thread and I thank all of
you, you've all taught me something.
John Spencer's solution of
Select Avg(IIF(Question1=9,Null,Question1) as Q1Avg
actually worked quite well and I was able to complete the project. I spot
checked the calculations and the Null being removed from the AVG() functions
calculation worked as advertised. I had to round it to 2 decimal places but
but it worked great.

You can still implement John's suggestion for your calculation.

Create a UNION query along the lines of

SELECT id, [Name], 1 As QuestionNb, question1 As QuestionValue
FROM MyTable
UNION
SELECT id, [Name], 2, question2
FROM MyTable
UNION
SELECT id, [Name], 3, question3
FROM MyTable
....
UNION
SELECT id, [Name], 12, question12
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I agree, though the names of the people are linked to another table. I
didn't
design the thing I just have to support it by virtue of my position.

On Thu, 15 Jan 2009 10:56:01 -0800, Zushiba

I've got a table that looks like this

id,Name,question1,question2,question3,question4,question5,question6,question7,question8,question9,question10,question11,question12

Then you don't have a table - you have a spreadsheet. This design is
*simply
wrong* for a relational database!

A better design would have *three tables*:

Students
StudentID
LastName
FirstName

Questions
QuestionNo
Question <text>

Answers
StudentID <who answered the question>
QuestionNo <which question>
Answer <your 4/3/2/1/0/9 value>

"Fields are expensive, records are cheap".

The possible ranges of data in any one of the questions is 4,3,2,1,0 and
9
(9 being a missing or Not Applicable response)
The 9 is what's screwing me up.
I need to count the amount of returned data in each question then devide
by
the count of returned answeres ignoring any 9s that show up.

I've tried many ways so far and nothing I try seems to work. In a more
complete version of SQL I could probably do it but Access's (2003 if
you're
wondering) version is so limited I can't seem to do it without writing 5
or 6
queries per question.

Your problem is not due to Access' limitations (you can do just about
anything
in Access that you can do in SQL/Server or Oracle) but your incorrect
table
design.

What you can do is use an expression such as

IIF([Question5] = 9, 0, [Question5])

to replace the 9's by 0's in your averaging.
--

John W. Vinson [MVP]

The output is to go into a report that people who don't know a thing
about
access can run and print.
the eventual output I'm looking for would show question1: 4.00 question
2:
3.96 question 3: 2.69 etc etc.


I've gotten far enough using REPLACE (To replace the 9 with a 0) to SUM
all
of the data properly in 1 query but I'm stuck on finding a way to COUNT
how
many 4,3,2,1,0's were returned while ignoring the 9 so I can get my
average.
Is there an easy way to do this?