Toni:
Bearing in mind what the others have said about the poor design of the table,
you can nevertheless count the teachers per column in query set up as follows
in query design view:
1. In the query designer select Totals from the view menu.
2. If you want to count the teachers per school then add the Field 1 field to
the first column in the design grid and leave its Total row as 'Group By'.
If you just want the overall counts for each subject, not grouped by school,
omit this step.
3. In the second column (or first if step 1 has been omitted) put the
following in the 'field' row:
Algebra: Sum(IIf(Len(Nz([Field A],""))>0,1,0))
This caters for this column either being Null or containing a zero-length
string if empty. The way it works is that the IIf function returns a 1 if
the field contains a
value other than a Null or zero-length string, a zero otherwise. These are
then summed, and as the sum of the ones is the same as counting them it gives
you a count of the populated fields.
4. In the same column select 'Expression' for the 'total' row in the design
grid.
5. In the next column put the following in the 'field' row:
Biology: Sum(IIf(Len(Nz([Field B],""))>0,1,0))
6. In the same column select 'Expression' for the 'total' row in the design
grid.
Repeat the two steps for each subject, changing the field name and subject as
appropriate.
This should give you what you want, but the design flaws to which the others
have dawn your attention are very valid points. A fundamental principle of
the database relational model (the Information Principle) is that data is
stored as values at row positions in tables and in no other way. What you
are doing is known as 'encoding data as column headings' and goes against
this principle.
The sign of a well designed table is generally that its tall and skinny, not
short and fat, in your case with just the three columns School, Subject and
TeacherID. Each of these columns should reference the primary key column of
Schools, Subjects and Teachers tables, each of which would have one row per
teacher, subject and school respectively. Note that for teachers a unique
numeric TeacherID should be used as the key, not the teacher's name as names
can be duplicated, in which case two rows in the Teachers table might have
the same names, but different TeacherID values.
What this 3-column table is really doing is modelling the 3-way relationship
between Schools, Subjects and Teachers. This is how a relational database
works, unlike a spreadsheet, which is what your table more resembles.
Ken Sheridan
Stafford, England
Mr. Steele;
The design came from the Microsoft consulting division. In
all honesty I cannot have a count field, unless there is something I am not
understanding.
The teacher may not teach a subject they are certified to
teach. I can set up a record with School, Subject and Teacher and then count
each one. This would work perfect for English and Math, but the rest of the
subjects would be a problem.
The desgin of the record was great until this request came
up. It looks like the only way to do it is with VBA. The other way would be
to have sixty-four queries.
Thanks for your help!
Toni
You should really fix your design. What you've got is known as a repeating
group, and makes it much more difficult to do the sort of counts you're
[quoted text clipped - 67 lines]