Counting Specific Duplications in a Single Table

[alpinegroove]

I have a table that contains names of employees and the names of awards they received. It's all in one table, which I know isn't ideal.

tblEmployeeAwards

EmployeeName, AwardName
John,Nobel
John, Pulitzer
Michael, Fulbright
Jane, Nobel
Jane, Fulbright

I would like to know the following:

1. How many employees received more than one award (answer: 2).
2. How many employees who received the Nobel prize also received any second prize (Answer: 2).
3. How many employees who received the Nobel prize also received the Fulbright (answer: 1).



I am not sure if this is a SQL or VBA question or both.

Any suggestions?

Thank you.

[June7]

Build aggregate (GROUP BY) Totals queries. Access Help has more guidance on using the query builder. Try:

1. SELECT employeeID, Count(AwardName) As CountAwards FROM tblEmployeeAwards GROUP BY employeeID HAVING Count(AwardName)>0;

2. SELECT employeeID, Count(IIf(AwardName="Nobel",1,0)) AS CountNobel, Count(IIf(AwardName<>"Nobel",1,0)) AS CountOther GROUP BY employeeID HAVING Count(IIf(AwardName="Nobel",1,0))>0 AND Count(IIf(AwardName<>"Nobel",1,0))>0;

3. SELECT employeeID, Count(IIf(AwardName="Nobel",1,0)) AS CountNobel, Count(IIf(AwardName="Fulbright",1,0)) AS CountFulbright GROUP BY employeeID HAVING Count(IIf(AwardName="Nobel",1,0))>0 AND Count(IIf(AwardName="Fulbright",1,0))>0;

[alpinegroove]

Build aggregate (GROUP BY) Totals queries. Access Help has more guidance on using the query builder. Try:

1. SELECT employeeID, Count(AwardName) As CountAwards FROM tblEmployeeAwards GROUP BY employeeID HAVING Count(AwardName)>0;

2. SELECT employeeID, Count(IIf(AwardName="Nobel",1,0)) AS CountNobel, Count(IIf(AwardName<>"Nobel",1,0)) AS CountOther GROUP BY employeeID HAVING Count(IIf(AwardName="Nobel",1,0))>0 AND Count(IIf(AwardName<>"Nobel",1,0))>0;

3. SELECT employeeID, Count(IIf(AwardName="Nobel",1,0)) AS CountNobel, Count(IIf(AwardName="Fulbright",1,0)) AS CountFulbright GROUP BY employeeID HAVING Count(IIf(AwardName="Nobel",1,0))>0 AND Count(IIf(AwardName="Fulbright",1,0))>0;

Thank you. SQL #1, #2, and #3 all return the same results:

Jane,2
John,2
Michael,1

I need the answer for #1 to be: ReceivedMoreThanAward,2

I need the answer for #2 to be: ReceivedNobelAndAnotherAward,2

I need the answer for #3 to be: ReceivedNobelAndFulbright,1

Note that my table does not have an EmployeeID field, so I replaced all those references with EmployeeName. Maybe that's an issue.

Also, in the #2 and #3 statements, I need to add "from tblEmployeeAwards" before "GROUP BY." Without that, I got an error.

Thanks again.

[June7]

Sorry, did forget the FROM clause. Names do make very poor unique identifiers.

Do you not want grouping by employee? Just want totals for entire dataset, no employee detail? You want only one record in the output?

Try this for query 1:

SELECT Count(Q1.EmployeeID) AS RecievedMoreThanOneAward FROM (SELECT EmployeeID, Count(AwardName) As CountAwards FROM tblEmployeeAwards GROUP BY EmployeeID) AS Q1 WHERE CountAwards>0;

Do similar construct for the other 2 queries.

[alpinegroove]

I don't need grouping by employee here, just totals for the entire dataset.

If I run this new statement, I get 4, but the correct count is 2.

My table doesn't have an EmployeeID field, so I changed that to EmployeeName.

What does Q1 refer to?

[alpinegroove]

I suppose that ideally I would have both the detail and the summary count I mentioned above. Perhaps that would be a second query that counts the first query.

[June7]

Q1 is alias name for the nested subquery.

I don't know your data so can't say why the result is doubled. It shouldn't.

If you want details as well as summary data, consider a report and use its Grouping & Sorting features with aggregate calcs in footers.

[alpinegroove]

Thanks for the explanation. I ran the query you suggested:

SELECT Count(Q1.EmployeeID) AS RecievedMoreThanOneAward FROM (SELECT EmployeeID, Count(AwardName) As CountAwards FROM tblEmployeeAwards GROUP BY EmployeeID) AS Q1 WHERE CountAwards>0;

My entire data is as follows:

EmployeeName, AwardName
John,Nobel
John, Pulitzer
Michael, Fulbright
Jane, Nobel
Jane, Fulbright

The answer should be 2, but I am getting 3.

[recyan]

Check if below give some guidelines :


The MainQueries


qryEmpCountwithAwardsGreaterThan1

SELECT 
    Count(qryAwardPerEmployeeCountGreaterThan1.EmployeeName) AS CountOfEmployeeName
FROM 
    qryAwardPerEmployeeCountGreaterThan1;

-------------------
qryEmpCountWithNobelAndFulBright

SELECT 
    Count(qryEmpWithNobelAndFulBright.EmployeeName) AS CountOfEmployeeName
FROM 
    qryEmpWithNobelAndFulBright;

-------------------
qryEmpCountWithNobelAndOtherAward

SELECT 
    Count(qryNobelAndOrOtherAwards.qryEmpNobelAward.EmployeeName) AS CountOfqryEmpNobelAward_EmployeeName
FROM 
    qryNobelAndOrOtherAwards;

-------------------




The subQueries




qryAwardPerEmployeeCountGreaterThan1

SELECT 
    tblEmployeeAwards.EmployeeName, Count(tblEmployeeAwards.AwardName) AS CountOfAwardName
FROM 
    tblEmployeeAwards
GROUP BY 
    tblEmployeeAwards.EmployeeName
HAVING 
    (((Count(tblEmployeeAwards.AwardName))>1));

-------------------
qryEmpWithNobelAndFulBright

SELECT 
    qryAwardNobelOrFulbright.EmployeeName, Count(qryAwardNobelOrFulbright.AwardName) AS CountOfAwardName
FROM 
    qryAwardNobelOrFulbright
GROUP BY 
    qryAwardNobelOrFulbright.EmployeeName
HAVING 
    (((Count(qryAwardNobelOrFulbright.AwardName))>1));

-------------------
qryAwardNobelOrFulbright

SELECT 
    tblEmployeeAwards.EmployeeName, tblEmployeeAwards.AwardName
FROM 
    tblEmployeeAwards
WHERE 
    (((tblEmployeeAwards.AwardName)="Nobel" Or (tblEmployeeAwards.AwardName)="Fulbright"));

-------------------


qryNobelAndOrOtherAwards

SELECT 
    qryEmpNobelAward.EmployeeName, qryEmpNobelAward.AwardName, tblEmployeeAwards.EmployeeName, Count(tblEmployeeAwards.AwardName) AS CountOfAwardName
FROM 
    qryEmpNobelAward 
    INNER JOIN 
    tblEmployeeAwards 
    ON 
    qryEmpNobelAward.EmployeeName = tblEmployeeAwards.EmployeeName
GROUP BY 
    qryEmpNobelAward.EmployeeName, qryEmpNobelAward.AwardName, tblEmployeeAwards.EmployeeName
HAVING 
    (((Count(tblEmployeeAwards.AwardName))>1));

-------------------
qryEmpNobelAward

SELECT 
    tblEmployeeAwards.EmployeeName, tblEmployeeAwards.AwardName
FROM 
    tblEmployeeAwards
WHERE 
    (((tblEmployeeAwards.AwardName)="Nobel"));

-------------------

Thanks

[June7]

You mean you want to know which awards had multiple employees, not which employees had multiple awards!!

SELECT Count(Q1.Award) AS MultipleEmployeesReceived FROM (SELECT Award, Count(EmployeeName) As CountEmps FROM tblEmployeeAwards GROUP BY Award) AS Q1 WHERE CountEmps>0;

[alpinegroove]

Thank you, recyan. That works perfectly. I really appreciate your help.

One follow up question:

Assuming I want to add the year an award was received to my main table.

How would I modify the sub queries to include the year in the results next to each employee's name?

Thanks again. I really appreciate your time writing those queries.

[recyan]

None of the Main Queries have Names in the Result. They only have a count as the result.
You can definitely add a Year to the Main table & include it in the subqueries. But how you will use it will depend on the final output that you are looking for.
On the side ( important ), Would ideally have a tblEmployees, tblAwards & tblEmployeeAwards in my design, if that is not the scenario at your end. Worth doing it right now.

Thanks

[alpinegroove]

Thanks again. That's really helpful. I realize that the normalized model is the way to go and that's the goal.

[recyan]

Glad to be of help. All the best with your project.

Thanks