Advanced User Please Help on Setting Up a Count Query?

[Guest]

I am stumped. Please help me! I need help with syntax for an expression. I
have a simple table which contains one column for each date of a particular
conference that medical students are supposed to attend (2 conferences per
day, Monday - Friday). These are is text fields. I have one row established
for each student . This also is a text field. I have a numerical unique
identifier in the table as well that I use to link to a table with basic info
about each student. If the student attended a conference, I put a "Y" in
the field for that conference (text field). If they did not attend the
conference, I put an "N" there. If they were exempted from attending the
conference I left the field null. I cannot figure out how to set up the
syntax in an expression that would enable me to produce a rate of attendance
(total attended divided by total they could have attended (not exempt from).
It has been suggested that I use 2 "count" queries to get total # of "Y"s and
total # of "N"s and then add these and divide results of the "count" query of
"Y"s by combination of # of "Y"s and # of "N"s and multiply by 100 to get a
percentage. I cannot figure out how to do this for the life of me. Please
help! Our organization has no Microsoft support contract. Thank you sooooo
much!

 

[Marshall Barton]

I am stumped. Please help me! I need help with syntax for an expression. I
have a simple table which contains one column for each date of a particular
conference that medical students are supposed to attend (2 conferences per
day, Monday - Friday). These are is text fields. I have one row established
for each student . This also is a text field. I have a numerical unique
identifier in the table as well that I use to link to a table with basic info
about each student. If the student attended a conference, I put a "Y" in
the field for that conference (text field). If they did not attend the
conference, I put an "N" there. If they were exempted from attending the
conference I left the field null. I cannot figure out how to set up the
syntax in an expression that would enable me to produce a rate of attendance
(total attended divided by total they could have attended (not exempt from).
It has been suggested that I use 2 "count" queries to get total # of "Y"s and
total # of "N"s and then add these and divide results of the "count" query of
"Y"s by combination of # of "Y"s and # of "N"s and multiply by 100 to get a
percentage. I cannot figure out how to do this for the life of me.

The problem is that counting rows is easy, but counting
columns is not a database thing to do. Instead of your
spreadsheet kind of table, you need to redesign the tables
so that there is only one conference per record. To
understand more about why/how, read up on Database
Normalization.

For you particular situation, understanding many to many
relationships is also important. (one student can attend
many conferences and one conference can accomodata many
students.)

All this is essential to the problem you are trying to deal
with.

 

[John Spencer]

SELECT Count(SomeColumn="Y",1, Null) as Attended
,Count(SomeColumn="N",1,Null) as SkippedOut
, Count(SomeColumn) as PossibleAttendees
, Count(SomeColumn="Y",1,Null)/Count(SomeColumn) as PercentAttended
FROM YourTable

You will have to build something like that for each column since your table
design is faulty.

You can do multiple columns in one query.
SELECT Count(SomeColumn="Y",1, Null) as Attended
,Count(SomeColumn="N",1,Null) as SkippedOut
, Count(SomeColumn) as PossibleAttendees
, Count(SomeColumn="Y",1,Null)/Count(SomeColumn) as PercentAttended

,Count(SomeColumn2="Y",1, Null) as Attended2
,Count(SomeColumn2="N",1,Null) as SkippedOut2
, Count(SomeColumn2) as PossibleAttendees2
, Count(SomeColumn2="Y",1,Null)/Count(SomeColumn2) as PercentAttended2
FROM YourTable

Another method would be to use a union query to normalize your data and then
use the union query as a source for an aggregate query.

SELECT ID, SomeColumn, "ColumnName" as SessionName
FROM YourTable
UNION ALL

SELECT ID, SomeColumn2, "ColumnName2"
FROM YourTable
UNION ALL
SELECT ID, SomeColumn3, "ColumnName3"
FROM YourTable

Then you could use one simple query to get all the information for all the
columns
SELECT SessionName
, Abs(Sum(SomeColumnName="Y")) as Attended
, Abs(Sum(SomeColumnName="Y")) / Count(SomeColumnName) as PercentAttended
FROM TheUnionQuery
GROUP BY SessionName

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..