Re: Count Function Horrors

#1

Dear Eric.

Try the following: (I have not used your field names as you will
need to substitute the appropriate fields. You will need to use the
following by changing the ExamID for the Department if you want
Department scores)

Use a Query

ExamID RawScore Score0 Score1 Score2

Note:
ExamID represents how you track who owns the score.
RawScore is the mark given.

Score0 to 2 would be coded
Score0:IIF(RawScore<1,1,IIF(ISNULL(RawScore),1,0))
Score1:IIF(RawScore=1,1,0)
Score2:IIF(RawScore=2,1,0)

Using Totals you can Sum Score0, Score1, Score2 to obtain your
information.

The above shows Null score the same as 0. If you want to show
separately ste up a ScoreN and change Score0 coding.

Peter

Eric W wrote:

Quote:

I have a database that keeps track of graduate students' scores for
exams they take here as part of their graduate program. The receive
either a 0, 1 or 2 for their score. When querying to count how many
0s, 1s and 2s each faculty has given (in separate queries, then put
together in a report), it grabs only the information that is positive
(not null) In other words, if a faculty has never given a 2 for a
grade, it won't count them. Any suggestions? Below is the sql
statement I have.

SELECT [Advisor/AuthorList].AuthorFName,
[Advisor/AuthorList].AuthorLName, Nz (Count(*),0) AS CountOfScore
FROM ([Advisor/AuthorList] RIGHT JOIN Exams ON
[Advisor/AuthorList].AuthorNum = Exams.ExamAuthor) LEFT JOIN Scores ON
Exams.ExamNum = Scores.ExamNum
GROUP BY [Advisor/AuthorList].AuthorFName,
[Advisor/AuthorList].AuthorLName, Scores.Score
HAVING (((Scores.Score)=0 Or (Scores.Score) Is Null))
ORDER BY [Advisor/AuthorList].AuthorLName;

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Count Function Horrors

comp.database.ms-access comp.database.ms-access

Re: Count Function Horrors - 07-19-2004 , 07:24 AM