query to return field based on another field

[timbits35]

Here are the relevant fields in my query

Case number Role Category Dispute
1 Claimant Child Family
1 Respondant Mother Family
1 Claimant Others Family

2 Claimant Teen Family
2 Respondant Uncle Family

3 Claimant Student School
3 Respondant Teacher School

I want a calculated field in my query that is called Party filing. If the
Dispute is Family, then I want the field to look to see which record for that
case has the role of Respondant and then return the corresponding value of
the field Category. If the dispute is any other type than family, then I
want the calculated field to return the corresponding record based on the
role of Claimant. Based on my sample data :

For Case 1, I want the Party filing field to read Mother because Mother has
the role Respondant and in Case 2 I want the result to be Uncle because Uncle
has the role Respondant and these 2 disputes are Family.

For Case 3, I want the Party filing field to read Student because the dispute
is not family and therefore it looks up the role of Claimant from the
Category field.

Thank you

 

[vbaInet]

First of all, look into using the DLookup() function. Are you familiar with this?

http://www.techonthenet.com/access/functions/domain/dlookup.php