Multiple Criteria - reason column

[Guest]

I'm not sure if this is do-able in a query or at all, but here's my goal:

I have a table with student information and awards. I have a query running
off the table to check eligibility for certain awards (multiple criteria
rows).

Anyone returned in the query has some type of eligiblity problem. I'd like
to add a column that shows which criteria the student was returned under.

Ex: columns for Residency and AwardCode
Criteria1: residency=International Awardcode=In('Awd1','Awd2')
Would like to indicate that the students were returned in the query because
they are not eligible for awd 1 and awd 2 because they're international.

Does this have to be done with separate queries? Or is there a better way?

Thanks for any insight,

 

[Connie B via AccessMonster.com]

You can do this all in one query by using IIF. This works in three parts, 1,
the criterion to be met, 2, the result if the criterion is met, 3, the result
if it is not met. (If something 1, then result 2, otherwise result 3) A
simple example:

iif([Residency] = "International", "International", Null) as Criteria1

would set your Criteria1 field to a value of "International" if the field
Residency has a value of "International", otherwise Criteria1's value will be
null. (You could also put in a different word here, such as "Domestic" but I
suggested Null so that this field is only showing these exceptions.)

You can also insert the value of a field instead of a string value:

iif([Residency] = "International", "International", [Residency]) as Criteria1

if you have other residency values that you don't want to lose, such as the
name of the state of residency.

I'm not sure if your example means that international students are eligble
for other awards, but not for awards 1 and 2. If you want to include the
awards that they are eligible for in Criteria1 or to show what/why they are
ineligible:

iif([Residency] = "International" and [Awardcode] in ("Awd1", "Awd2"),
"Ineligble for Awards 1 and 2 - International", [Awardcode]) as Criteria1

How this translates: if residency = international AND awardcode = Awd1 or
Awd2, then "Ineligble for Awards 1 and 2 - International"
Otherwise: If if residency = international AND awardcode = anything other
than Awd1 or Awd2, then show the values of the Awardcode field (such as Awd3)
You can also nest IIF statements. If you can tell me exactly what you would
like to see in each case, I can do the syntax for you.