Multiple Values

[tdailey11]

Please, help!! I know this is probably a very simple query, but, for the life of me, I cannot figure it out.

I have a table of registration histories, tblRegHistory, and from that table I need to extract people who have attended a certain conference every year for the past 4 years. (We want to recognize those individuals who have attended every year.) When I run the query, I'm getting everybody who has attended any one of those years even though they may have only attended one or two years or if I run the query below then I get "zero" results. How do I extract only those individuals that have attended every year?

SELECT DISTINCT tblRegHistory.[Person Id], tblRegHistory.[Meeting Code], tblRegHistory.[Name Prefix], tblRegHistory.[Last Name]
FROM tblRegHistory
WHERE (((tblRegHistory.[Meeting Code]) Like "wood99" And (tblRegHistory.[Meeting Code]) Like "Wood00" And (tblRegHistory.[Meeting Code]) Like "Wood*01" And (tblRegHistory.[Meeting Code]) Like "Wood*02"))
ORDER BY tblRegHistory.[Person Id];

Thanks!!

 

[TessB]

I can name that tune in 2 queries...

I'm sure someone else has a way to get you the answer using only one query. AND I would love to see that posted...

But here is my answer using 2 queries. First of all, you are getting 0 records because your Meeting Code can't be Wood01 AND Wood00 or anything else at the same time.... it's either Wood01 OR Wood00. But I think you figured that out for yourself by now.

So, first, you need to change your criteria to reflect the OR statements for your meeting code. Secondly, I would add an expression 4yr:1 which would put a 1 for every year that person attended a meeting.

Then I would write another query asking only for the distinct names of the attendees, grouped by attendees, Summed on the 4yr expression and the criteria for the 4yr expression field would simply be 4.

I'm sure you could also do this with a count, somehow.. but this is the first thing that comes to mind. Anyone else want to take a stab at it with a single query?

Tess

 

[shay]

I think I understood the problem! See if the following helps:

SELECT DISTINCT tblRegHistory.[Person ID], tblRegHistory.[Name Prefix], tblRegHistory.[Last Name], Left([Meeting Code],4) AS ConfCode, Count(tblRegHistory.[Meeting Code]) AS [CountOfMeeting Code]
FROM tblRegHistory
GROUP BY tblRegHistory.[Person ID], tblRegHistory.[Name Prefix], tblRegHistory.[Last Name], Left([Meeting Code],4)
HAVING (((Left([Meeting Code],4)) Like ("Wood")) AND ((Count(tblRegHistory.[Meeting Code]))=4))
ORDER BY tblRegHistory.[Person ID];

shay

 

[Jon K]

Try this query:

SELECT [Person ID], [Name Prefix], [Last Name]
FROM tblRegHistory
GROUP BY [Person ID], [Name Prefix], [Last Name]
HAVING sum(iif([Meeting Code] in ("Wood99","Wood00","Wood*01","Wood*02"),1,0))=4;

 

[tdailey11]

Thank you, Thank you, Thank you!! Shay's code worked. I will need this in the future as well so this is great. Thanks everyone!!