Counting Multiple Records and Specific Duplications

[alpinegroove]

I have a table that contains names of employees and the name of awards they received.

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.

 

[vbaInet]

SQL!

1. Count of a count: Count (John-2,Michael-1,Jane-2) where the count is > 1, then Count again.
2. Count of a count with a join: Count where AwardName='Nobel' (John-1,Jane-1). Join this query to your Employee table and perform a count again on the unique records.
3. Count (with two criteria) of a count with a join: Count where AwardName='Nobel' Or 'Fulbright' (John-1,Michael-1,Jane-2) and the count is > 1, then Count again.

... when I say count again I mean save the query from before and create another query perform a count on the saved query.

http://office.microsoft.com/en-gb/access-help/count-data-by-using-a-query-HA010096311.aspx

 

[Mile-O]

Would suggest you have a table for Employees and a table for Awards, and then a table for Awards To Employees.

tblEmployees
EmployeeID
Forename
Surname
etc.

tblAwards
Award ID
AwardName
etc.

tblAwardsToEmployees
EmployeeID
AwardID

 

[alpinegroove]

SQL!

1. Count of a count: Count (John-2,Michael-1,Jane-2) where the count is > 1, then Count again.
2. Count of a count with a join: Count where AwardName='Nobel' (John-1,Jane-1). Join this query to your Employee table and perform a count again on the unique records.
3. Count (with two criteria) of a count with a join: Count where AwardName='Nobel' Or 'Fulbright' (John-1,Michael-1,Jane-2) and the count is > 1, then Count again.

... when I say count again I mean save the query from before and create another query perform a count on the saved query.

http://office.microsoft.com/en-gb/access-help/count-data-by-using-a-query-HA010096311.aspx

Thank you. I am new to SQL. Could you please give some examples?

Note that I only have one table: tblEmployeeAwards. I don't have a tblEmployee.

 

[alpinegroove]

Would suggest you have a table for Employees and a table for Awards, and then a table for Awards To Employees.

tblEmployees
EmployeeID
Forename
Surname
etc.

tblAwards
Award ID
AwardName
etc.

tblAwardsToEmployees
EmployeeID
AwardID

Yes, that would be helpful, but at this point I can't redesign the database. I only have one table.

 

[alpinegroove]

SQL!
3. Count (with two criteria) of a count with a join: Count where AwardName='Nobel' Or 'Fulbright' (John-1,Michael-1,Jane-2) and the count is > 1, then Count again.
http://office.microsoft.com/en-gb/access-help/count-data-by-using-a-query-HA010096311.aspx

I managed to do #1 and #2. But I can't get the first part of #3. It doesn't aggregated when I filter on 'Nobel' Or 'Fulbright'.

I get:

Jane,1,Fulbright
Jane,1,Nobel
John,1,Nobel
Michael,1,Fulbright.

I can't get it to aggregate Jane,2.

 

[vbaInet]

What I explained are two queries. One with OR and a Count, and another based on query 1 with a criteria of > 1 in the counted field.

Anyway, why did you say you can't have the structure properly normalised?

 

[alpinegroove]

What I explained are two queries. One with OR and a Count, and another based on query 1 with a criteria of > 1 in the counted field.

Anyway, why did you say you can't have the structure properly normalised?

Currently, I only have one table and I don't want to change the structure of the database unless it's the only way to do this count.

I tried to follow your instructions for #3 but could get it to do what I needed.

 

[vbaInet]

Well that's up to you, what we told you is what's best for your database as a whole and basic guidelines adhered to by all developers.

What did you try?

 

[alpinegroove]

I agree that that's better practice. No question about it.

But for right now, I need to solve this query problem before making any changes to the data model, or lack thereof.

I ran the SQL statement mentioned above for #3 and got the following results:


Jane,1,Fulbright
Jane,1,Nobel
John,1,Nobel
Michael,1,Fulbright.

I need the answer to be "Number of Employees with Nobel and Fulbright: 1"

 

[vbaInet]

What I'm after is the SQL of what you currently have?