Removing repetitions in a calculation

[wolfsight]

I have 7 districts (A-G), with 5 departments(1-5) in each district, and employees in each district. Some of the employees have specific training (TrainingA). I am creating a report that requires a number of departments per district that have employees with this training. This is a non-relational database (not my design).

I have created the report using subsummary sorted by district (required by other parts of the report) but having difficulty obtaining a number of departments per district that have employees with TrainingA.

I have tried multiple calculations with no luck. I have had some experience with FMPro, but still having trouble with this one.

Major mental block.

[BobWeaver]

Need more info. How is your file set up?

[BobWeaver]

quote:

---

the file is set up like this:

Each employee in the database comprises one record. The database is a

single flat file. For each employee, the department and district are

specified. The district field is a editable value list, the department

is not. The training is also part of the same file, specified by date.

To get, for instance, all employees with TrainingA (started in 1970),

the calculation would read:

If(TrainingA>01/01/1970,1,0)

Then create a summary field to total the result.

I can easily find all employees in a district that have TrainingA, but

what I need is a count of the number of departments that have employees

with TrainingA, where each department may have more than one employee

with the training.

.

.

.

Can this be done without rewriting this into a relational database?

---

There may be a simpler way, but the solution that springs to mind is to use a self-join relationship. Actually two self-joins. Create a field called Flag that you set to one for employees with the required training and zero otherwise, as in your calculation above. You will also need a numeric EmployeeNo field which has a unique number for each employee (not necessarily in order or contiguous, as long as they are unique). Next, create the following calculated field:

DeptTrained, Text Calculation = Department & "" & NumToText(Flag)

Now make up two self-join relationships:

1. SelfByDistrict based on the District field

2. SelfByDept based on the DeptTrained field

And finally make two more calculated fields:

HaveTraining, Number Calculation = (Max(SelfByDept::EmployeeNo) = EmployeeNo) and Flag

Count, Number Calculation = Sum(SelfByDistrict::HaveTraining)

This last field will be the count of departments in each district that have at least one trained employee. As I said, there could very well be an easier way, but I can't think of it at the moment.

[wolfsight]

That worked well!! the only problem remaining is to be able to count the total #departments that have trained employees. Simply a SUM of the COUNT field does not return a valid result, nor does a Count of the COUNT field.

Any last hints?

[BobWeaver]

Did you mean total number of departments or total number of departments per district?

I assumed that you wanted total per district. If you just want total number of departments, Just make a summary field that totals the HaveTraining field. I think that should do it.