How do I pull only records with this criteria?

[creolejazz]

I've got a table that contains people and their attendance records for an annual event. The three attendance related fields are: Attend2008, Attend2009, and Attend2010.

Each field can contain either Yes, No, or be blank. ("Yes means the person attended that year, "no" or <blank> means they did not attend that year.)

How do I design the query so that it finds all those who attended in 2010 but did NOT attend previously? In other words, I want to pull only the records that have YES in the 2010 field but anything other than YES in the other two fields. Make sense?

Any clues appreciated. Thanks.

 

[pbaldy]

Don't like the repeating fields, but try:

WHERE Attend2010 = "Yes" AND Attend2009 <> "Yes" AND Attend2008 <> "Yes"

 

[creolejazz]

Thanks for the reply. That makes perfect sense. But forgive the newb question...where exactly do I type that sequence? All I've ever used is the "criteria" line in the query design. Thanks.

 

[creolejazz]

OK, I went to the SQL view and added your line. That view now looks like this:

SELECT [MP TABLE].[LAST NAME], [MP TABLE].[FIRST NAME], [MP TABLE].[Att2007], [MP TABLE].[Att2008], [MP TABLE].[Att2010], [MP TABLE].[Att2009]
FROM [MP TABLE]
WHERE Att2010 = "Yes" AND Att2009 <> "Yes" AND Att2008 <> "Yes" AND Att2007 <> "Yes";

...but it pulls up no results even though it should be finding 100 or so records.

No doubt I've missed something. Ideas?

 

[pbaldy]

Can you post the db?

 

[creolejazz]

I really can't as it contains a lot of personal info. Sorry.

 

[pbaldy]

It occurs to me that the Null fields might not return correctly. Try this:

WHERE Att2010 = "Yes" AND (Att2009 Is Null Or Att2009 <> "Yes") AND (Att2008 Is Null Or Att2008 <> "Yes") AND (Att2007 Is Null Or Att2007 <> "Yes")

 

[creolejazz]

Perfect! That did it. Thanks so much.

 

[pbaldy]

Ah good, glad we got it sorted out.