Counting instances over multiple fields

[Texarado]

Each record of my database represents a department of our company. Fields within each record (department) list employee names and titles. For each department, I need a field that calculates the number of managers, another field that calculates the number of salespersons, and another that calculates the number of administrators.

I need the results to appear within each record, not in a summary field in a report.

I can create numeric 'if' fields for each employee field, then a calculation field that adds the results. For example:

if(Employee 01 = "Manager", 1,0)

if(Employee 02 = "Manager", 1,0)

Another field would add all of these fields to give me the number of managers.

I would repeat this process for sales persons and administrators.

This seems like a cumbersome way to do what I want. Might there be a simple calculation to evaluate multiple fields within each record?

Thanks!

[Genx]

Your going to have to be clearer about your data structure here..

Are your employees in a related table or in the same table as your "departments"?

[Texarado]

Same table. The purpose of this database is so simple that I saw no need to use a related table.

Essentially, this database will function much like an Excel spreadsheet. Each record (like a separate 'page' in Excel) applies to a unique department. I created fields for 50 employees in each department:

1.) Employeee Name

2.) Employee Title (management, administration, sales), entered via a drop-down menu

What I'm trying to create are three counting fields, one to show the number of management employees in that department, one to show the number of administrators in that department and one to show the number of salespersons for that department.

I'd like to find a way for three fields to look at all 50 "Employee Title" fields for each record to tell me how many managers, administrators and salerspersons there are in that department.

As I mentioned originally, I could create additional fields for each employee:

if(Employee Title = Management, 1, 0)

if(Employee Title = Administration, 1, 0)

if(Employee Title = Sales, 1, 0)

Then, I could create three fields that add up the values of these fields to show the results I'm looking for.

This method seems cumbersome because it adds 150 fields for each record. I'm looking for a more simple way to do this.

Thanks for your help!

[Genx]

You see.. you could've created 3 fields in one related table instead of 100 locally... oh well.

Anyyyway. Hmmm, Ive honestly got no clue how to do this in any simple way.

The easiest way would probably be to run through all the fields in a script and collect numbers, dumping the output into different fields.

E.g.

Commit Record

Set Variable[$field ; "EmployeeTitle" //this is the field prefix we're looking for]

Loop

Go To Next Field

If[Left(Get(ActiveFieldName) ; Length($field)) = $field // The Length function isn't really needed here, i just can't be bothered counting.

[color:blue]If[Get(ActiveFieldContents) = "Manager"]

Set Variable[$managers = $managers + 1]

Else If[Get(ActiveFieldContents) = "Administration"]

Set Variable[$administrators = $administrators + 1]

Else If[... etc. etc. for the rest of your positions]

.

.

.

End If

End If

Exit Loop If[Right(Get(ActiveFieldName);2) = "50" // checks if we're at field "Employee Title 50"]

End Loop

Then just use set Field to set the managerCount field = $managers, administratorCount field = $administrators etc.

Again... wouldve been much easier with a related structure, no scripts involved... just a series of simple relationships with constants.

[Genx]

You would however have to have all your fields in the tab order.

[LaRetta]

Hi Texarado,

"The purpose of this database is so simple that I saw no need to use a related table. "

Ah. The trap. Using a relational structure is EASIER. Always.

"This method seems cumbersome because it adds 150 fields for each record. I created fields for 50 employees in each department."

I suggest splitting it now - you weren't succeeding at creating an Excel spreadsheet (which is flat-file, each Employee would have the Department and their title and each Employee would STILL be a different row. You instead created an Excel Cross-tab (or so you were attempting). Those 50 [color:red]fields should be 50 Employee [color:red]Records holding their title AND Department.

I'm not surprised that Genx couldn't think of an easy way to handle it. With your current structure, this is just the beginning of your problems. With relational, you'll be rolling in no time.

LaRetta

[Texarado]

Thanks for the helpful suggestions. (Regarding my Excel comparison, I made it work perfectly in Excel. I'd simply rather have this project in database form rather than in a spreadsheet.)

I know how to create a relational database from scrath, but is there a way to migrate fileds from one table to another when changing a flat file to a relational file? That would be preferable to recreating the files in the second table.