Counting records in a table

[Jeffrey Marks]

I have a table that has 5 different test scores (reading, writing,
math, SS, and science) per records in the table. The student may take
these tests multiple times (until passed). So a test score may have a
score (1-600) in the field or it may be null.

I'd like to total the number of time each student takes a given test
(e.g. how many times did he/she take the math test). When I'm done I
also want to see how many tests were taken, but if I have the total
count of how many times each test was taken, I can easily sum those to
get the overall total.

Is the best way to approach this an IIF statement, stating that if the
score is not one add 1 to the total? Or is there a more efficient way
to code this?

Thanks as always

Jeff

 

[John W. Vinson]

I have a table that has 5 different test scores (reading, writing,
math, SS, and science) per records in the table. The student may take
these tests multiple times (until passed). So a test score may have a
score (1-600) in the field or it may be null.

I'd like to total the number of time each student takes a given test
(e.g. how many times did he/she take the math test). When I'm done I
also want to see how many tests were taken, but if I have the total
count of how many times each test was taken, I can easily sum those to
get the overall total.

Is the best way to approach this an IIF statement, stating that if the
score is not one add 1 to the total? Or is there a more efficient way
to code this?

Thanks as always

Jeff

If you have one field for each test, your table isn't properly designed! A
better structure would have one record per test result - fields for the
student ID, the test ID, perhaps the date taken and the score.

With your current design how do you handle the case where a student passes
reading on the first try, but has to take math three times? Do you have two
records with null Reading scores?

If you want to count the number of non-NULL results for each test, you can use
a totals query:

SELECT StudentID, Sum(IIF(IsNull([Reading]), 0, 1)) AS CountOfReading,
Sum(IIF(IsNull([Writing]), 0, 1)) AS CountOfWriting, <etc>

Am I understanding the table structure correctly? If not could you post some
sample data?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Jeff Boyce]

As John points out, the likely table structure (one colum per test) is ... a
spreadsheet! In a well-normalized relational database, the table would be
designed as John offers.

So what, you ask? So MS Access is designed to work best with
well-normalized data. If you try to feed it 'sheet data, both you and
Access have to work overtime to come up with work arounds.

It all starts with the data!

Regareds

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

All you need to do is COUNT the scores for each field. Count counts the
presence of a value. Nulls are NOT counted.

In the query design view
== Add your table
== Add the 5 fields
== Select View: Totals from the menu (2003 and earlier)
== Change Group by to Count under the 5 fields

SQL view would look like

SELECT Count(Reading) as RCount
, Count(Writing) as WCount
, Count(Math) as MCount
, Count(SS) as SSCount
, Count(Science) as SCount
FROM [YourTableName]



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

 

[David W. Fenton]

All you need to do is COUNT the scores for each field. Count
counts the presence of a value. Nulls are NOT counted.

Does COUNT() use distinct values, or all of them?

 

[John Spencer]

If you want a count of distinct values then you can use Allen Browne's ECount
function or you can run a query to get distinct values and then use that to
get the count.

Count in Access Jet/ACE counts the presence of a value. So if ten records are
returned and they all have the same value in the field you are counting the
count is ten.

See
ECount() - an extended DCount()
at:
http://allenbrowne.com/ser-66.html

SQL server Transact SQL has the ability to do a distinct count. As I recall
the syntax is
Count(Distinct Field) as CountUniqueValues


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