Check out some similar questions!

jakester · Feb 13, 2013, 09:54 AM

Hi Scott - hoping I can explain my problem well enough to get a perspective from you on how to solve this.

Ok, I have a roster of people where each person may work on a specific task during a given period (a month, or a quarter, etc.) and each person is assigned to a team. To illustrate my problem, let me give you some examples.

Team Jones has 30 people on it. There are 5 tasks that a person on that team may or may not work on during Q1 of 2013. Here are those tasks:

1. Business Analysis
2. Project Planning
3. Testing
4. Test Planning and Preparation
5. Quality Inspection

Now, what I want to do is to find the unique instances for each task where 1 or all of the 30 people worked on that task for the quarter. To further illustrate, say for Business Analysis that Mary Smith did Business Analysis in January, February, and March. She performed that task 3 times (1x in each month) but I am only concerned about knowing that she did perform Business Analysis during the quarter (so I’d like to show a 1 instead of 3).

Ideally, if everyone on Team Jones did Business Analysis during the quarter at least one time, I am only concerned about the one instance where they did and the first occurrence of it, not the other occurrences. So if everyone did do Business Analysis for the quarter, my total count should not exceed the count of members on the team (in this case, the 30 people). So the distribution could look like this (I’m just making this up but this is what I’d like to see):

Task Team Jan Feb Mar Total
Business Analysis Jones 7 15 8 30

My fields in the table are:
1. Task
2. User
3. Date
4. Team (there are 5 different teams)

Is this doable in a query?

Thanks.

ScottGem · Feb 15, 2013, 04:09 AM

Just do a Group By query. I think if you group by Task then by person, you will have a unique list of the tasks performed by whom during the period. If you add a calculated field just showing a 1, you will have a count of the people performing each task by adding another query, again grouping by task, (eliminating the people) but summing the calculated count field.