Count if Populated

[Antonette]

The district wants me to use the query part of MS Access. I know how to do
it in VBA but they want me to do this in a simple query.

Our district is going to have to offer 32 courses. I need to count if a
teacher is qualified to teach a course from the entire region.

The file is in sequence by high school (64 high schools).

Example:

Algebra
Biology
Russan
Zoology

If a teach is present I need to add 1 to a counter for each school.

If 60 have an educator qualified to teach Algebra then the answer is 60.
If only 14 have a teacher listed under the field name Zoology, then the
answer is 14.

How can I do this in a simple query?

Thanks in Advance

Toni
"Have your daughters play Volleyball or softball, it keeps me employed!"

SBS 2003 R2 in every school.

MS Office 2003 is installed.

 

[kc-mass]

show the data structure of the table(s) that the data is in

 

[John Spencer]

You need to tell us a bit more about the structure of your data.

Is the file you are referring to a text file or is this an Access database
with one table or multiple tables?

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

 

[Antonette]

It is an Access Database.

Sixty-Eight Fields in each record. Field 1 = School Name

Field A = Algebra Teachers
Field B = Biology Teachers
Field C = Russian Teachers
Field D = Zoology teachers and so on.

I just need a count of how many teachers in each field.

If a field has too few teachers or no teachers those are the ones we must
hire.

Thanks Again

Toni

 

[Douglas J. Steele]

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
trying to do.

Your table should have three fields: one for the school name, one for the
subject and one for the number of teachers of that subject.

 

[Antonette]

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

 

[John Spencer]

Incorrect table design.

What is stored in FieldA (and all the other subject fields)? Is it a number?
IF it is a number field, then do you store 0 for no teachers or do you
leave it blank (null)?

I'll assume that this is a number field that stores 0 for no teachers. You
will need 68 expressions to get a count.

Field: AlgebraCount: Abs(Sum([FieldA]<>0))

That will return a count of FieldA where the value is not zero.

If you want a count where the value is zero, the change <> to =.

Repeat the expression for each of the subject fields.

A better design would be
School
Subject
TeacherCount

Then your query would be a lot simpler to build. And if you wanted a list by
school and subject where you needed a teacher then the query would be:

SELECT School, Subject
FROM YourTable
WHERE TeacherCount = 0

If you wanted a count of schools by subject with zero teachers
SELECT Subject, Count(Schools) as NeedTeacher
FROM YourTable
WHERE TeacherCount = 0
GROUP BY Subject

Life is a lot simpler with the correct table design.



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

 

[John W. Vinson]

It is an Access Database.

Sixty-Eight Fields in each record. Field 1 = School Name

Field A = Algebra Teachers
Field B = Biology Teachers
Field C = Russian Teachers
Field D = Zoology teachers and so on.

I just need a count of how many teachers in each field.

As Douglas and John say, this is an incorrect table design. The Microsoft
Counsulting person who did this should be ashamed of themselves!

What is the datatype of these fields? a count, a checkbox, a name, a <shudder>
multivalue field with a list of names, or what?

It's possible to get a count - with more difficulty with this flawed design -
but just HOW you would do so depends on the nature of the data in the field.

 

[Antonette]

John;

Every field is the name of a course. FieldA = Algebra, within that field is
the name of the teacher(s) There is no numerical data in any field, only
names of teachers.

If there is no teacher's name for the subject at that school for that
specific course, then there are no certified teachers to teach that course.

Thanks for getting back to me.

Toni

Incorrect table design.

What is stored in FieldA (and all the other subject fields)? Is it a number?
IF it is a number field, then do you store 0 for no teachers or do you
leave it blank (null)?

I'll assume that this is a number field that stores 0 for no teachers. You
will need 68 expressions to get a count.

Field: AlgebraCount: Abs(Sum([FieldA]<>0))

That will return a count of FieldA where the value is not zero.

If you want a count where the value is zero, the change <> to =.

Repeat the expression for each of the subject fields.

A better design would be
School
Subject
TeacherCount

Then your query would be a lot simpler to build. And if you wanted a list by
school and subject where you needed a teacher then the query would be:

SELECT School, Subject
FROM YourTable
WHERE TeacherCount = 0

If you wanted a count of schools by subject with zero teachers
SELECT Subject, Count(Schools) as NeedTeacher
FROM YourTable
WHERE TeacherCount = 0
GROUP BY Subject

Life is a lot simpler with the correct table design.



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

It is an Access Database.

Sixty-Eight Fields in each record. Field 1 = School Name

Field A = Algebra Teachers
Field B = Biology Teachers
Field C = Russian Teachers
Field D = Zoology teachers and so on.

I just need a count of how many teachers in each field.

If a field has too few teachers or no teachers those are the ones we must
hire.

Thanks Again

Toni

 

[Antonette]

As Douglas and John say, this is an incorrect table design. The Microsoft
Counsulting person who did this should be ashamed of themselves!

What is the datatype of these fields? a count, a checkbox, a name, a <shudder>
multivalue field with a list of names, or what?

It's possible to get a count - with more difficulty with this flawed design -
but just HOW you would do so depends on the nature of the data in the field.

The data type is all teacher's names only. 27 characters lomg for each field.

Each column is a course. 64 columns of courses.

Thanks for your help.

Toni

 

[John W. Vinson]

The data type is all teacher's names only. 27 characters lomg for each field.

So my former colleague Venkataraghavan Srinivasaran would be disqualified I
presume...

Each column is a course. 64 columns of courses.

And if you ever add another course you'll... what? Redesign your table, all
your forms, all your reports, all your calculations? OUCH!

That's a decent spreadsheet design.
It is NOT a correct database design.

 

[John Spencer]

Ok, then your expression would look like

Field: AlgebraCount: Abs(Sum([Algebra] is Not Null))

or if you want to count the blanks

Field: AlgebraCount: Abs(Sum([Algebra] is Null))

If the field could contain either nulls or a zero length string.
Field: AlgebraCount: Abs(Sum([Algebra] is not Null AND [Algebra] <> ""))

and to count the blanks
Field: AlgebraCount: Abs(Sum([Algebra] is Null or [Algebra] = ""))

Again, you are going to have to build this expression for EACH field. You
don't need multiple queries, but you do need to build 32 expressions in one query.

I'm sorry you got incorrect advice on the structure. As I and others have
noted there is a much better structure for the data in Access and relational
databases. The structure you have is a good structure for a spreadsheet.

And if all you have is is the limited amount you have described, then I think
a spreadsheet would work just as well, if not better, for handling the limited
amount of data.

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

John;

Every field is the name of a course. FieldA = Algebra, within that field is
the name of the teacher(s) There is no numerical data in any field, only
names of teachers.

If there is no teacher's name for the subject at that school for that
specific course, then there are no certified teachers to teach that course.

Thanks for getting back to me.

Toni

Incorrect table design.

What is stored in FieldA (and all the other subject fields)? Is it a number?
IF it is a number field, then do you store 0 for no teachers or do you
leave it blank (null)?

I'll assume that this is a number field that stores 0 for no teachers. You
will need 68 expressions to get a count.

Field: AlgebraCount: Abs(Sum([FieldA]<>0))

That will return a count of FieldA where the value is not zero.

If you want a count where the value is zero, the change <> to =.

Repeat the expression for each of the subject fields.

 

[Antonette]

John;

There are seven open columns now. Under Federal D.O.E. law we cannot
exceed 64. It was 72 and the D.O.E. cut it down and they may cut it down
again. The chances of it increasing is one in a million.

Again I did not design the software for the application. However in
all honesty understanding the application I cannot think of another way of
doing it. They also want to be able to use it in Oracle. Also now that IBM
has been getting more business you never know what can happen there. We have
had massive issues with the servers operating systems constantly hanging up.

I tried a table School Name, Course, Teacher and a Tag Field if
they were the department chair and it ran so slow it upset the chairs of the
departments.

I personally feel it is a VB application. We cannot do everything
in Access.

I really do than you guys for your assistance, you have been great.

Toni

 

[John W. Vinson]

John;

There are seven open columns now. Under Federal D.O.E. law we cannot
exceed 64. It was 72 and the D.O.E. cut it down and they may cut it down
again. The chances of it increasing is one in a million.

Nonetheless, a wide-flat design like this is simply not appropriate for use in
Access - the table design is the REASON you're having trouble getting the
counts you want. The counts are *very easy* in a properly normalized database
(see below); they're much harder with a spreadsheet, simply because Access is
designed to work with normalized data and not with spreadsheets.

Again I did not design the software for the application. However in
all honesty understanding the application I cannot think of another way of
doing it. They also want to be able to use it in Oracle. Also now that IBM
has been getting more business you never know what can happen there. We have
had massive issues with the servers operating systems constantly hanging up.

If you want to use it in Oracle - or SQL/server, or DB2, or any other
relational database - you will be better off with a normalized design. This
would have at least four tables:

Schools
SchoolID <primary key>
SchoolName
City
State
Address
<other info about the school itself>

Courses
CourseID <primary key>
CourseName
<other info about the course as a thing in itself>

Faculty
FacultyID <primary key>
LastName
FirstName
DepartmentChair <yes/no>
<other biographical info>

CourseAssignment
AssignmentID <autonumber primary key>
FacultyID <who's teaching this course>
CourseID <what are they teaching>

I tried a table School Name, Course, Teacher and a Tag Field if
they were the department chair and it ran so slow it upset the chairs of the
departments.

Even if you're talking about 64 courses at thousands of schools with tens of
thousands of faculty, a properly indexed and implemented table structure like
that above should provide responses in seconds. Proper indexing and
implementation is a learned skill and may not have worked "out of the box", so
don't give up on it too soon!

I personally feel it is a VB application. We cannot do everything
in Access.

It may well be, but if so, it's for reasons other than what have been
discussed here.

I really do than you guys for your assistance, you have been great.

Thanks, hope we can continue to be of help.

 

[Marilyn Rosenbusch]

I understand your issue with the file not being properly normailzied, but this can happen when your are being forced to work with a text file from an old system. This is very common. I have had to import a non-normalize test file into Access, but then need to normalize it with a pivot type function.

Doesn't Access have a SQl function that allows you to pivot the repeating groups into one column and merge the repeating records. There is a UNPivot in Oracle, but I would like to know how to do this in Access.

The district wants me to use the query part of MS Access. I know how to do
it in VBA but they want me to do this in a simple query.

Our district is going to have to offer 32 courses. I need to count if a
teacher is qualified to teach a course from the entire region.

The file is in sequence by high school (64 high schools).

Example:

Algebra
Biology
Russan
Zoology

If a teach is present I need to add 1 to a counter for each school.

If 60 have an educator qualified to teach Algebra then the answer is 60.
If only 14 have a teacher listed under the field name Zoology, then the
answer is 14.

How can I do this in a simple query?

Thanks in Advance

Toni
"Have your daughters play Volleyball or softball, it keeps me employed!"

SBS 2003 R2 in every school.

MS Office 2003 is installed.

On Saturday, October 03, 2009 1:20 PM kc-mass wrote:
show the data structure of the table(s) that the data is in

On Sunday, October 04, 2009 3:34 PM John Spencer wrote:
Incorrect table design.

What is stored in FieldA (and all the other subject fields)? Is it a number?
IF it is a number field, then do you store 0 for no teachers or do you
leave it blank (null)?

I will assume that this is a number field that stores 0 for no teachers. You
will need 68 expressions to get a count.

Field: AlgebraCount: Abs(Sum([FieldA]<>0))

That will return a count of FieldA where the value is not zero.

If you want a count where the value is zero, the change <> to =.

Repeat the expression for each of the subject fields.

A better design would be
School
Subject
TeacherCount

Then your query would be a lot simpler to build. And if you wanted a list by
school and subject where you needed a teacher then the query would be:

SELECT School, Subject
FROM YourTable
WHERE TeacherCount = 0

If you wanted a count of schools by subject with zero teachers
SELECT Subject, Count(Schools) as NeedTeacher
FROM YourTable
WHERE TeacherCount = 0
GROUP BY Subject

Life is a lot simpler with the correct table design.



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

Antonette wrote:

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

Antonette wrote:

On Monday, October 05, 2009 8:37 AM John Spencer wrote:
Ok, then your expression would look like

Field: AlgebraCount: Abs(Sum([Algebra] is Not Null))

or if you want to count the blanks

Field: AlgebraCount: Abs(Sum([Algebra] is Null))

If the field could contain either nulls or a zero length string.
Field: AlgebraCount: Abs(Sum([Algebra] is not Null AND [Algebra] <> ""))

and to count the blanks
Field: AlgebraCount: Abs(Sum([Algebra] is Null or [Algebra] = ""))

Again, you are going to have to build this expression for EACH field. You
do not need multiple queries, but you do need to build 32 expressions in one query.

I am sorry you got incorrect advice on the structure. As I and others have
noted there is a much better structure for the data in Access and relational
databases. The structure you have is a good structure for a spreadsheet.

And if all you have is is the limited amount you have described, then I think
a spreadsheet would work just as well, if not better, for handling the limited
amount of data.

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

Antonette wrote:

 

[John Spencer]

There is no such SQL function in Access.

You can create a VBA function that does this or use a series of append queries
to accomplish this.

I have a VBA function that I experimented with and it seems to work. Watch
out for word wrap causing problems.

'Turn non-normalized data (repeating fields) into a normalized table

Public Function fMakeNormalizedTable(strSource, strDestination _
, intCountIdColumns _
, Optional intStartField = 0, Optional intStopField = 0 _
, Optional intGroupSize = 1 _
, Optional tfIncludeNulls As Boolean = False)
'===============================================================================
' Procedure : fMakeNormalizedTable
' DateTime : 5/11/2006 07:39
' Author : John Spencer
' Purpose : Take a non-normalized table with repeating columns and normalize
' the table. Source table structure is expected to be one to n identifier
' columns
' followed by many repeating columns. For example
' FirstName LastName WorkPhone HomePhone MobilePhone FaxPhone AlternatePhone
' Destination table should already exist and should have a structure similar
' to
' the source table. The structure would be something like
' the Identifier fields, a field to hold the source's field name, and a field ' to
' hold the data in the repeating fields. For example
' FirstName LastName PhoneType PhoneNumber

'------------------------------------------------------------------------------
' strSource = Name of table with data
' strDestination = Name of destination table
' intCountIdColumns = number of identifier columns
' intStopField = Last Column to be used in building populating destination
' table
' intStartField = First repeating column
' intGroupSize = Allows for regular group size (x columns in each group)
' # Gum Sold, Flavor
' # Chiclets, ChicletFlavor; #Wrigley, WrigleyFlavor
' tfIncludeNulls = If True then make records for fields where the value is
' null
'===============================================================================

Dim dbAny As DAO.Database
Dim strSqlBase As String, strSql As String, strSQLTarget As String
Dim strBuildTableSQL As String
Dim intLoop As Integer
Dim strFieldName As String
Dim rstAny As DAO.Recordset
Dim intLoop2 As Integer
Dim strAdd As String

Static iErrCount As Integer

On Error GoTo ERROR_fMakeNormalizedTable
'---------------------------------------------------------------
' Future Code Enhancements:
' -- Add ability to skip keyfield column in destination table
' -- add ability to start at any column in source table
'---------------------------------------------------------------
Set dbAny = CurrentDb()


'------------------------------------------------------------------------------
' Determine number of times to loop

'------------------------------------------------------------------------------
iErrCount = 1 'set ierrCount to force stop
If intStopField = 0 Or intStopField >
dbAny.TableDefs(strSource).Fields.Count Then
intStopField = dbAny.TableDefs(strSource).Fields.Count - 1
Else
intStopField = intStopField - 1
End If

If intStartField > intStopField Then
MsgBox "Stop! Start field is after stop field.", , "Please fix"
Exit Function
End If

If intStartField = 0 Or intStartField < intCountIdColumns Then
intStartField = intCountIdColumns
Else
intStartField = intStartField - 1
End If

'Check numbers to make sure they work
If intGroupSize <> 1 Then
If (1 + intStopField - intStartField) Mod intGroupSize <> 0 Then
'adjust intstopfield down
intStopField = intStopField - _
(1 + intStopField - intStartField) Mod intGroupSize <> 0
End If
End If


'------------------------------------------------------------------------------
' Get field names in destination Table and build insert statement

'------------------------------------------------------------------------------
iErrCount = 0 'initialize errCount
With dbAny.TableDefs(strDestination) 'if this errors then attempt to
'build table

For intLoop = 0 To .Fields.Count - 1
strSQLTarget = strSQLTarget & ", [" & .Fields(intLoop).name & "]"
Next intLoop
End With 'dbAny.TableDefs(strDestination)

strSQLTarget = Mid(strSQLTarget, 3) 'Strip off beginning ", "
strSQLTarget = "INSERT INTO [" & strDestination & "] (" & _
strSQLTarget & ") "


'Build SELECT clause for SELECT query portion of Insert query

'Add Identifier fields
With dbAny.TableDefs(strSource)
If .Fields.Count < intCountIdColumns + 1 Then
MsgBox "Not enough fields in destination table", , "Sorry"
Exit Function
End If

strAdd = vbNullString
For intLoop = 0 To intCountIdColumns - 1
strAdd = strAdd & ", [" & .Fields(intLoop).name & "]"
Next intLoop

strSqlBase = "SELECT " & Mid(strAdd, 3) 'Strip off beginning ", "

'Populate the table
For intLoop = intStartField To intStopField Step intGroupSize
strSql = vbNullString
strAdd = vbNullString
For intLoop2 = 0 To intGroupSize - 1
strFieldName = .Fields(intLoop + intLoop2).name
strAdd = strAdd & ", """ & strFieldName & """, " & _
"[" & strFieldName & "] "

Next intLoop2

strSql = strAdd & " FROM [" & strSource & "] "
strAdd = vbNullString

If tfIncludeNulls = False Then
'Build where clause if nulls are to be excluded
For intLoop2 = 0 To intGroupSize - 1
strFieldName = .Fields(intLoop + intLoop2).name
strAdd = strAdd & "[" & strFieldName & "] is not Null OR "
Next intLoop2
strSql = strSql & " WHERE " & Left(strAdd, Len(strAdd) - 4) 'Strip
'off last Or
End If


strSql = strSQLTarget & " " & strSqlBase & " " & strSql

dbAny.Execute strSql, dbFailOnError

Next intLoop

End With

EXIT_fMakeNormalizedTable:
On Error GoTo 0
Exit Function

ERROR_fMakeNormalizedTable:
If Err.Number = 3265 And iErrCount = 0 Then
iErrCount = iErrCount + 1
'------------------------------------------------------------------------------
' Build the destination table based on the source table
'------------------------------------------------------------------------------
'Identifier fields
With dbAny.TableDefs(strSource)
For intLoop = 0 To intCountIdColumns - 1
strBuildTableSQL = strBuildTableSQL & ", " & _
.Fields(intLoop).name & " " & _
fGetFieldTypeName(.Fields(intLoop).Type)
Next intLoop

'Repeating value fields
For intLoop = intStartField To intStartField + intGroupSize - 1
strBuildTableSQL = strBuildTableSQL & ", " & _
.Fields(intLoop).name & " Text(64)"

strBuildTableSQL = strBuildTableSQL & ", " & _
.Fields(intLoop).name & "Value " & _
fGetFieldTypeName(.Fields(intStartField).Type)
Next intLoop

strBuildTableSQL = Mid(strBuildTableSQL, 3)

strBuildTableSQL = "Create Table " & strDestination & _
"( " & strBuildTableSQL & ")"
dbAny.Execute strBuildTableSQL, dbFailOnError

End With

dbAny.TableDefs.Refresh
Resume
Else
MsgBox "Error " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
" in procedure fMakeNormalizedTable"
Err.Clear
End If
Stop: Resume 'Debug purposes only. Remove from final code
End Function



Private Function fGetFieldTypeName(fldAnyType) As String
'returns string field type
Dim strAny As String
Select Case fldAnyType
' Case dbBigInt
' strAny = "Big Integer"
Case dbBinary
strAny = "Binary"
Case dbBoolean
strAny = "Boolean"
Case dbByte
strAny = "Byte"
' Case dbChar
' strAny = "Char"
Case dbCurrency
strAny = "Currency"
Case dbDate
strAny = "DateTime"
Case dbDecimal
strAny = "Decimal"
Case dbDouble
strAny = "Double"
Case dbFloat
strAny = "Double"
Case dbGUID
strAny = "GUID"
Case dbInteger
strAny = "Integer"
Case dbLong
strAny = "Long"
' Case dbLongBinary
' strAny = "Long Binary (OLE Object)"
Case dbMemo
strAny = "Memo"
Case dbNumeric
strAny = "Numeric"
Case dbSingle
strAny = "Single"
Case dbText
strAny = "Text"
Case dbTime
strAny = "Time"
' Case dbTimeStamp
' strAny = "Time Stamp"
' Case dbVarBinary
' strAny = "VarBinary"
' Case Else
' strAny = "Unknown Type"
End Select

fGetFieldTypeName = strAny

End Function

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