Grouping issue 2

[Slippenos]

I have the following scenario:

[u]Id_no [/u]     [u]First_n[/u]      [u]Last_n[/u]      [u]Event[/u]
33         Mike         Jones       55
34         Mike         Jones       61
35         Cletus       Smith       40
36         Jim          Clein       61
37         Mike         Jones       62
38         Jim          Clein       88

How do I fetch only those names with Events 55, 61, AND 62 completed?

Thanks in advance.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[PHV]

Something like this ?
SELECT First_n, Last_n
FROM yourTable
WHERE Event In (55, 61, 62)
GROUP BY First_n, Last_n
HAVING Count(*) = 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Slippenos]

I'm unfamiliar with that type of query ... here is what I came up with ...

SELECT Attendence.[UB ID], Students.[Last Name], Students.[First Name], Attendence.[Event ID]
FROM Students INNER JOIN (Events INNER JOIN Attendence ON Events.[Event ID] = Attendence.[Event ID]) ON Students.[UB ID] = Attendence.[UB ID]
WHERE (((Attendence.[Event ID]) In (39,40)))
GROUP BY Attendence.[UB ID], Students.[Last Name], Students.[First Name], Attendence.[Event ID]
HAVING (((Count(Students.[UB ID]))=2));

With the GROUP BY statement, it would throw an error at me unless they all had that.

I got 0 results. Did I write it correctly?

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[Golom]

You need to remove Attendence.[Event ID] from the SELECT and GROUP BY. When you have it there you will get a separate record for each Attendence.[Event ID] and, unless a student has been in a specific event twice, you will never satisfy the HAVING clause.

Look at PHV's code and note that he has excluded Event from SELECT and GROUP BY. It appears only in the WHERE clause.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Slippenos]

YES, thats it. Thank you both very mich for helping me out.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[Slippenos]

Ok, 1 more question .. How would I show those who did not complete events 39 and 40?

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[PHV]

SELECT ...
FROM ...
WHERE Attendence.[Event ID] In (39,40)
GROUP BY ...
HAVING Count(*)<2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Plagerizing from PHV

SELECT DISTINCT First_n, Last_n
FROM yourTable
WHERE Event NOT IN (39, 40)

Assuming that you want those who did not complete either of the events.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Slippenos]

They both look great and I am about to test them .. will they include those who did not do any events?

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[Golom]

No. You will need to do a LEFT JOIN to get that.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Slippenos]

Worked like a charm- thanks again.

[blue]Go to work to learn. Don't go to work to earn.[/blue]