Select first, If no first, then select 2nd

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My accounts contacts have three levels of contacts. The accounts may or may
not have a Main Contact, may or may not have a level Second Contact ....

How can I write a query that select only one contact per account in the
following order?

If a Main Contact exist, select it
If a Main Contact does not exist, select Second Contact
If Main Contact or Second Contact does not exist, select Contact Three
 
Write a query that includes all three. Add an IIF() statement that follows
the logic you described. Use this IIF() statement as another field to be
displayed by the query.

Good luck

Jeff Boyce
<Access MVP>
 
May I have a little more detail as a novice?

Jeff Boyce said:
Write a query that includes all three. Add an IIF() statement that follows
the logic you described. Use this IIF() statement as another field to be
displayed by the query.

Good luck

Jeff Boyce
<Access MVP>
Click to expand...
 
In your query, put something like:

SELECT (IIF(NZ([Main Contact],"") <> "",[Main Contact],IIF(NZ([Second
Contact],"") <> "",[Second Contact],IIF(NZ([Contact Three],"") <>
"",[Contact Three],"No Contact")))) AS [Contact] FROM ...

HTH,

Carl Rapson
 
Back
Top