counting related records

[Jdawg]

I'm trying to set up a calculation field that counts the number of a certain type of related record in another table.

For example:

employee table is linked to the Absence table

I want a field in the employee table called "Count Sick" to count the number of related absences in the Absence table that were coded as sick for each employee. (I have a reason code field in the Absence table where I select the reason code from a value list.

Here's a psudocode example of what I'm trying to do.

Count (Absence::Employee ID) WHERE Absence::Reason Code = "Sick"

Any help is appreciated,

thanks.

Edited February 1, 2007 by Guest

[T-Square]

This is easily handled with a relationship and a simple calculation field.

1) Create a relationship between Employees and Absences on the relationship graph between Employees::ID and Absences::EmpID

2) Create a calculated unstored number field in Employees: Count(Absences::EmpID)

If you have a limited set of specific types of absences (i.e., a specific set that consists of, say, "SICK" "VAC" and "PERSONAL"), you can extend this concept by setting up relationships for the specific circumstances. In this situation, you would put additional Table Occurrences on your relationship graph--one for each category--and set up the relationship to use the specific absence type (go to the relationship and add another match field). For ease of reading, you could name the new occurrences of Absences by their type. That is, name Absences "SICK" for one and "VAC" for another. With this approach, you can create separate calculations for each category and display them on the Employee layout.

You could also use a single global field with a value list to allow you to select which count you want to evaluate and display. This would be beneficial for a more dynamic set of Absence reasons.

HTH,

David

[Jdawg]

David, You're the man!

Your suggestion to create seperate instances of my Absence table worked.

I simply created a field in my employee table for each type of absence, "sick", "maternity","jury duty", etc, and linked those unique fields (along with the Employee ID)to an instance of Absence.

I called each instance of Absence the name of what I was filtering for, i.e. "sick". I set up a calculation field in the Employee table for each Reason code I'm tracking in it's respective instance of Absence.

For example, to count all the sick days that have been recorded for an employee, my Count Sick field in Employee is set up like this...Count (Sick::EmployeeID)

I have a similar field to count jury duty, maternity, etc.

It's like using relationships as a query tool.

thanks again !!!

Edited February 1, 2007 by Guest
also...