Query Based on Crosstab Query

[JudyJones]

Access 2002, SP3
I have a crosstab query displaying various email addresses for parents/guardians of each student. The columns are based on the relationship to the student: Mother, Father, StepMother, StepFather, FosterMother, FosterFather, Guardian, etc. What I want to achieve in another query is to pull out one email address for each student. If there is no email address for Mother, then I want the one for Father. If there is no email address for Mother or Father then I want the one for StepMother. If there is no email address for Mother, Father, or StepMother then I want the one for StepFather, etc. What expression would I use to achieve that result?

[HansV]

Try:

Email: Nz([Mother],Nz([Father],Nz([StepMother],Nz([StepFather],Nz([FosterMother],Nz([FosterFather],[Guardian]))))))

Make sure that the number of closing parentheses at the end is equal to the number of opening parentheses after the Nz's.

[JohnH]

I think it would be easier to add a field (or a whole table if need be) that ranks the types of email addresses with a number, then just use a grouping query to find the address with the minimum rank for each person.

1 Mother
2 Father
3 Step Mother
etc

[JudyJones]

Thank you very much Hans. Your solution worked perfectly.

I will try out the solution from JohnH as well. It always helps to know more than one way to accomplish the same thing.