Conditional Count in Access?

[Guest]

How can I set up a conditional count in an Access query to calculate how many
times a specific value occurs in each record for several fields (eg. I would
like to know how many times the value "A" occurs for John Smith in the fields
5/26, 5/30, 6/1, 6/5, and 6/30)?

 

[ChrisM]

Hi,

I don't really understand the problem, for example:
Are '5/26', '5/30' etc. Field Names?
Do they only have a single letter in them or could the A that we're looking
for be part of a larger string?
Could 'John Smith' appear in the table more than once?
....

Would it be possible for you to give a sample of the sort of data that is in
the table?

Cheers,

ChrisM.

 

[Guest]

Yes, they are field names. They contain either "A" "P" "L" or "M/U". And
John Smith appears only once.
This is a query to track attendance. The fields are: Name, 5/26, 5/30, 6/1,
6/5, 6/30, etc.
The 'Name' field contains names of students ('John Smith')
The rest of the fields are dates of classes and contain either a "A" "P" "L"
or "M/U" for each student (for Absent, Present, Late, Make-Up)
I would like to calculate how often each student was absent.

 

[ChrisM]

OK,
So the fields '5/26' etc. represent dates(?)
So do you add a new field to the table for each relevant date? Or is there a
pre-set number of these fields.
How many are there/do you expect there to be eventually?

If I've understood the above correctly, this is really not an ideal design,
and any queries you write will probably have to be modified each time you
add a new date column(field) to the table.

Have you considered a slightly different design like:

Student Date Attendance
John Smith 5/26 A
John Smith 5/30 P
....

This will makes things easier though it depends how far down the road you've
gone with your current design.

Cheers,

ChrisM

 

[Guest]

ChrisM,
Once the query is set up i will not have to add any more fields. But, there
are 80 class dates, and 30 students. So with your suggested design, I would
have 2400 records. Also, my tables have some complicated relationships that
would be messed up.
Any ideas?
But, assuming I was able to successfully use your design, how would I phrase
the expression to calculate number of absences, latenesses, etc.

 

[Pat Hartman\(MVP\)]

Once you properly normalize the table (and I strongly suggest that you do),
the query will be simple:

Select Student, Attendance, Count(*) As AttandanceCount
From YourTable
Group By Student, Attendance;

This will produce a file similar to:

John, A, 3
John, P, 2
Mary, A, 1
Mary, L, 1
Mary, M/U, 1
Dick, L, 2

An easy alternative if you want each attendance type listed across the top
is to use the crosstab query wizard to build the query and then you'll end
up with

Student, A, P, L, [M/U]
John, 3,2,0,0
Mary, 1,0,1,0
Dick, 0,0,2,0

Your table design reflects what you would have done had you built the
application with Excel. But, tables with this design are handled poorly by
relational databases and you are going to HATE Access and never know why.
No matter how far your design has progressed, I suggest backing up and
fixing the table definitions. 2400 records is nothing to a relational
database. Many Access databases operate easily with tables containing
millions of rows. In fact, you can reduce the number of rows substantially
if you store only exception records. That means that a student does NOT
have a row in the table when he was present. He only has a row when there
is some exception activity to report. This might be a little tricky for you
because it will involve working with a table of dates in many of your
queries when you need all students to appear even though they have no
exceptional activity.

If you don't think you understand how to work with the sparse table, you can
build a query that appends a row for each person in the class for a
particular date. Place this code in a button on a form and run it
immediately before you want to enter the exceptions for that day. Don't add
these records ahead of time because you don't know what your class roster
will be for every future day. It can easily change as the semester
progresses. Then the subform should show only the records for a particular
date and you can modify the ones that have exceptions.

 

[Guest]

Thanks, you have both been very helpful

Once you properly normalize the table (and I strongly suggest that you do),
the query will be simple:

Select Student, Attendance, Count(*) As AttandanceCount
From YourTable
Group By Student, Attendance;

This will produce a file similar to:

John, A, 3
John, P, 2
Mary, A, 1
Mary, L, 1
Mary, M/U, 1
Dick, L, 2

An easy alternative if you want each attendance type listed across the top
is to use the crosstab query wizard to build the query and then you'll end
up with

Student, A, P, L, [M/U]
John, 3,2,0,0
Mary, 1,0,1,0
Dick, 0,0,2,0

Your table design reflects what you would have done had you built the
application with Excel. But, tables with this design are handled poorly by
relational databases and you are going to HATE Access and never know why.
No matter how far your design has progressed, I suggest backing up and
fixing the table definitions. 2400 records is nothing to a relational
database. Many Access databases operate easily with tables containing
millions of rows. In fact, you can reduce the number of rows substantially
if you store only exception records. That means that a student does NOT
have a row in the table when he was present. He only has a row when there
is some exception activity to report. This might be a little tricky for you
because it will involve working with a table of dates in many of your
queries when you need all students to appear even though they have no
exceptional activity.

If you don't think you understand how to work with the sparse table, you can
build a query that appends a row for each person in the class for a
particular date. Place this code in a button on a form and run it
immediately before you want to enter the exceptions for that day. Don't add
these records ahead of time because you don't know what your class roster
will be for every future day. It can easily change as the semester
progresses. Then the subform should show only the records for a particular
date and you can modify the ones that have exceptions.

ChrisM,
Once the query is set up i will not have to add any more fields. But,
there
are 80 class dates, and 30 students. So with your suggested design, I
would
have 2400 records. Also, my tables have some complicated relationships
that
would be messed up.
Any ideas?
But, assuming I was able to successfully use your design, how would I
phrase
the expression to calculate number of absences, latenesses, etc.

 

[ChrisM]

Glad to be of service.
If you need any further assistance with re-structuring (normaliSing) your
database, let us know, and I'm sure me, or someone else will be able to
help.

If you're new to access, personally, I would recommend you added a row to
the table for each student for each class date, whether they attend or not.
Dealing with a 'sparse' table will only give you problems, until you are
more confident with the way relational databases work.

For what its' worth, the query to do what you originally asked with a table
with 80 date fields will be horrific to write and maintain,
Not even sure of the best way to do it, but it could be somthing like:

SELECT StudentName,
((iif([5/23]="A",1,0)+(iif([5/27]="A",1,0)+(iif([5/30]="A",1,0)...for each
of the 80 date fields...) as Absent, ((iif([5/23]="P",1,0) +... for each
date field...) as Present

Nasty! If there is ANY way you can redesign, please (for your own sanity) do
it...

Cheers,

Chris.

Thanks, you have both been very helpful

Once you properly normalize the table (and I strongly suggest that you
do),
the query will be simple:

Select Student, Attendance, Count(*) As AttandanceCount
From YourTable
Group By Student, Attendance;

This will produce a file similar to:

John, A, 3
John, P, 2
Mary, A, 1
Mary, L, 1
Mary, M/U, 1
Dick, L, 2

An easy alternative if you want each attendance type listed across the
top
is to use the crosstab query wizard to build the query and then you'll
end
up with

Student, A, P, L, [M/U]
John, 3,2,0,0
Mary, 1,0,1,0
Dick, 0,0,2,0

Your table design reflects what you would have done had you built the
application with Excel. But, tables with this design are handled poorly
by
relational databases and you are going to HATE Access and never know why.
No matter how far your design has progressed, I suggest backing up and
fixing the table definitions. 2400 records is nothing to a relational
database. Many Access databases operate easily with tables containing
millions of rows. In fact, you can reduce the number of rows
substantially
if you store only exception records. That means that a student does NOT
have a row in the table when he was present. He only has a row when
there
is some exception activity to report. This might be a little tricky for
you
because it will involve working with a table of dates in many of your
queries when you need all students to appear even though they have no
exceptional activity.

If you don't think you understand how to work with the sparse table, you
can
build a query that appends a row for each person in the class for a
particular date. Place this code in a button on a form and run it
immediately before you want to enter the exceptions for that day. Don't
add
these records ahead of time because you don't know what your class roster
will be for every future day. It can easily change as the semester
progresses. Then the subform should show only the records for a
particular
date and you can modify the ones that have exceptions.

ChrisM,
Once the query is set up i will not have to add any more fields. But,
there
are 80 class dates, and 30 students. So with your suggested design, I
would
have 2400 records. Also, my tables have some complicated relationships
that
would be messed up.
Any ideas?
But, assuming I was able to successfully use your design, how would I
phrase
the expression to calculate number of absences, latenesses, etc.


:

OK,
So the fields '5/26' etc. represent dates(?)
So do you add a new field to the table for each relevant date? Or is
there a
pre-set number of these fields.
How many are there/do you expect there to be eventually?

If I've understood the above correctly, this is really not an ideal
design,
and any queries you write will probably have to be modified each time
you
add a new date column(field) to the table.

Have you considered a slightly different design like:

Student Date Attendance
John Smith 5/26 A
John Smith 5/30 P
....

This will makes things easier though it depends how far down the road
you've
gone with your current design.

Cheers,

ChrisM

Yes, they are field names. They contain either "A" "P" "L" or
"M/U".
And
John Smith appears only once.
This is a query to track attendance. The fields are: Name, 5/26,
5/30,
6/1,
6/5, 6/30, etc.
The 'Name' field contains names of students ('John Smith')
The rest of the fields are dates of classes and contain either a "A"
"P"
"L"
or "M/U" for each student (for Absent, Present, Late, Make-Up)
I would like to calculate how often each student was absent.

:

Hi,

I don't really understand the problem, for example:
Are '5/26', '5/30' etc. Field Names?
Do they only have a single letter in them or could the A that we're
looking
for be part of a larger string?
Could 'John Smith' appear in the table more than once?
....

Would it be possible for you to give a sample of the sort of data
that
is
in
the table?

Cheers,

ChrisM.

How can I set up a conditional count in an Access query to
calculate
how
many
times a specific value occurs in each record for several fields
(eg.
I
would
like to know how many times the value "A" occurs for John Smith
in
the
fields
5/26, 5/30, 6/1, 6/5, and 6/30)?