Query to get unique counts

[jakeman]

Hello Access gurus - 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.

[alcapps]

this would be a cross tab query that you need. there is a wizard in access that is fairly easy to manipulate.
on the transpose.. make sure you format the date like Format(Date,"MMM")

Also if your table has reserved words your going to have issues. so try TaskDate instead of Date.

so to normalize your table ..

should be a Task table
ID
Task

should be a Team table
ID
Team

should be a User table
ID
User

should be a TaskWork table (this is where everything comes together)
ID
TaskID
UserID
TaskDate
TeamID

Hope this helps..

[alcapps]

here is the sql from a quick table

TRANSFORM Count(Task.ID) AS CountOfID
SELECT Task.Task, Task.Team, Count(Task.ID) AS [Total Task]
FROM Task
GROUP BY Task.Task, Task.Team
PIVOT Format([TaskDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

[alcapps]

ID	Task	Team	TaskDate	User
1	Business Analysis	Jones	1/1/2013	Jim
2	Business Analysis	Jones	1/1/2013	Jim
3	Business Analysis	Jones	1/1/2013	Jim
4	Business Analysis	Jones	1/1/2013	Jim
5	Business Analysis	Jones	1/1/2013	Jim
6	Business Analysis	Jones	1/1/2013	Jim
7	Business Analysis	Jones	1/1/2013	Jim
8	Business Analysis	Jones	2/1/2013	Jane
9	Business Analysis	Jones	2/1/2013	Jane
10	Business Analysis	Jones	2/1/2013	Jane
11	Business Analysis	Jones	2/1/2013	Jane
12	Business Analysis	Jones	2/1/2013	Jane
13	Business Analysis	Jones	2/1/2013	Jane
14	Business Analysis	Jones	2/1/2013	Jane
15	Business Analysis	Jones	2/1/2013	Jane
16	Business Analysis	Jones	2/1/2013	Jane
17	Business Analysis	Jones	2/1/2013	Jane
18	Business Analysis	Jones	2/1/2013	Jane
19	Business Analysis	Jones	2/1/2013	Jane
20	Business Analysis	Jones	2/1/2013	Jane
21	Business Analysis	Jones	2/1/2013	Jane
22	Business Analysis	Jones	3/1/2013	Jerry
23	Business Analysis	Jones	3/1/2013	Jerry
24	Business Analysis	Jones	3/1/2013	Jerry
25	Business Analysis	Jones	3/1/2013	Jerry
26	Business Analysis	Jones	3/1/2013	Jerry
27	Business Analysis	Jones	3/1/2013	Jerry
28	Business Analysis	Jones	3/1/2013	Jerry
29	Business Analysis	Jones	3/1/2013	Jerry
30	Business Analysis	Jones	3/1/2013	Jerry
32	Project Planning	Jones	1/1/2013	Jim
33	Project Planning	Jones	1/1/2013	Jim
34	Project Planning	Jones	1/1/2013	Jim
35	Project Planning	Jones	1/1/2013	Jim
36	Project Planning	Jones	1/1/2013	Jim
37	Project Planning	Jones	1/1/2013	Jim
38	Project Planning	Jones	1/1/2013	Jim
39	Project Planning	Jones	2/1/2013	Jane
40	Project Planning	Jones	2/1/2013	Jane
41	Project Planning	Jones	2/1/2013	Jane
50	Project Planning	Jones	2/1/2013	Jane
51	Project Planning	Jones	2/1/2013	Jane
52	Project Planning	Jones	2/1/2013	Jane
53	Project Planning	Jones	3/1/2013	Jerry
54	Project Planning	Jones	3/1/2013	Jerry
55	Project Planning	Jones	3/1/2013	Jerry
56	Project Planning	Jones	3/1/2013	Jerry