Query to get unique counts
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.
