Keeping a running count of records based on criteria
I am running FileMaker Pro 11 on Windows Vista.
I am trying to do something that seems like it should be very simple, but is giving me a lot of trouble.
I am creating a database of faculty members at the university where I work. There is a table that stores faculty members' personal information, such as their nationality, degrees, department, etc.
I want to have a layout that will show me a summary with a running count of things such as:
Number of faculty members currently employed
Number of faculty members with an M.A.
Number of faculty members with a Ph.D.
Number of faculty members in department X
Number of countries from which the faculty members come
This seems like it should be very simple, and the kind of thing FileMaker is designed for, but short of going into the database and searching for each count, I can't figure out how to get these numbers. Is it possible to have a new, related table where the fields auto-calculate these values? It doesn't appear that the Count ( ) function can be used to count records based on criteria (say, count all records in the faculty table who are listed as being in the English department.)
I don't know if this makes things easier or more difficult, but information like departments and degrees is stored in separate tables, related to the faculty table. (There is a list of all departments at the school connected to a table that acts as a roster for the departments, which in turn connects to the faculty table. Similarly, there is a table of degrees connected to the faculty table.) Trying to use these tables to get the numbers, I hit the same wall. Is it possible to use my degree table and count all the M.A.s or the department roster table to count the number of faculty members in the English department?
I hope that is enough information. If you need me to provide more details, please let me know.