Calc Based on Value List Check Box Set Help!

[FredP]

2 Part Question:

The goal is to take an existing employee table, and a timesheet table that is connected via a EmpoyeeID field, and get total number of hours. Which I can do. But they want each total of hours by SUB-TYPE. Subtypes can be Manager, Intern, Supervisor, etc etc, and is controlled by a value list.

 

The first problem is this, a person can be included in multiple sub groups.

 

1)

I have a Contact List(Employee List) table with a "Contact_SubType" field which contains a checkbox set driven by a value list.

 

Contact_SubType Value List

Manager

Intern

Supervisor

Support

Admin

 

Any combination of those can be checked. So Someone can actually be Admin and Manager for example.

 

I would like to be able to use this information for organizing the data in the timesheet table that refer to the employees ID. So If someone is a manager, I want to treat some of their data specifically. So I thought I would use a ManagerID which would be something like

 

If ( Contact SubType="Manager"; Employee_ID ; "")

 

That Way I can do a relationship based on ManagerID#'s. Only people who are managers will have something in this field.

 

But what seems to happen is if Manager is the only option checked, the IF statement rings true, and ManagerID = Contact ID, but if Manager and Admin are checked, than the test is NOT true, and the Manager ID field is left empty.

 

Does that make sense? Can you help?

 

2)

The reason why I am doing this, is because they want to be able to create reports that show how many Manager Hours, How many Supervisors, How many Intern Hours there are on a given project. (ProjectID is also joined to Timesheet Table)

 

I cant seem to get a total per sub group. I know how to get the summary field to work for a sum of the Hours based on Project information. But not based on Project and split into different sums per SubType.

 

Any help would be hugely appreciated. Im banging my head today.

 

Fred

[FredP]

Ok So I guess if i can get help solving option1 above I can always create summary fields for each Subtype inside of the timesheet table i think, but maybe there is an easier way?

[Ender]

To check for the presence of a value in a checkbox set, use patterncount().

[FredP]

Thats works. thank you!