Conditional Query

[Bekie]

Hello,
I have a database that holds school information including
who to contact at the school. Some schools have several
types of contacts (Primary, Payroll, Grants, etc), but
some only have one primary contact. I want to create a
query (to run a grants report) that displays the grant
contact name if they have one, but defaults to the
primary contact name if they do not have a specific
grants contact. Is there a way to do this? This is my
query code so far:

SELECT School.School_Name, Contacts.Contact_Name,
School.Phone, Contacts.Cell_Phone,
Contacts.Email_Address, School.Estimated_Enrollment
FROM School, Contacts, School_Contact
WHERE (((School.School_Name)=School_Contact.School_Name
And (School.School_Name)=[Enter School Name]) And
((School_Contact.Contact_Name)=Contacts.Contact_Name));

When I put "AND Contact_Type = 'Grants'" into the WHERE
statement it returned no results if there was no grant
contact listed.

Thanks so much!

 

[[MVP] S. Clark]

I would do this in multiple steps.

1st, to another table, add all of the contact names for those that HAVE a
type Grant.
2nd, For the remainder of the schools (which obviously don't have a type
Grant) add the primary contact name.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-