If - then query

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

A company has 3 levels of management. I want to first
select the CFO for a mailing. If the company doesn't have
a CFO, I want to select and send the mailing to the
President. If the company doesn't have a president .....


The fields included are account number, title, last name
and first name.

Can I write a select statment to perform this query? Any
other suggestions?
 
Hi,


I would make it in multiple query, to keep things clear, for illustration.

query QPresident:

SELECT Account, FirstName, LastName FROM contacts WHERE
title='president'

query QCFO :

SELECT Account, FirstName, LastName FROM contacts WHERE
title='CFO'


query QAnyoneElseOutThere:

SELECT Account, Last(FirstName) , Last(LastName)
FROM contacts
WHERE title NOT IN('president', CFO'')
GROUP BY Account


Then, the main query can be:


SELECT z.Account,
Nz( a.FirstName, Nz(b.FirstName, c.FirstName)) As TheFirstName,
Nz( a.LastName, Nz(b.LastName, c.LastName)) As TheLastName
FROM ((contacts as z LEFT JOIN QPresident As a ON z.account=a.account)
LEFT JOIN QCFO as b ON
z.account=b.account)
LEFT JOIN QAnyoneElseOutThere As c ON z.account=c.account



Sure, if you use MS SQL Server, rather that nesting your Nz (Nz does not
exist anyhow with MS SQL Server), use the very powerful COALESCE:

COALESCE( a.FirstName, b.FirstName, c.FirstName) As
TheFirstName,
COALESCE( a.LastName, b.LastName, c.LastName) As TheLastName


but then, you will get some problem with QAnyoneElseOutThere, since MS SQL
Server does not have the very powerful LAST aggregate.



Another possibility, probably it may even run faster, is to define a table
like:

Priorities ' table name
Title, Priority ' fields name
CFO 2
president 1


next, make the query, Qp;

SELECT accounts.*, Nz(priority, 0) As thePriority
FROM accounts LEFT JOIN priorities ON accounts.title=priorities.title


It is then a matter to find, for each account, the record with the highest
value for priority. Any technique described in
http://www.mvps.org/access/queries/qry0020.htm may untimely be used, but I
would select the third, GROUPing on account number and First( ) on all the
other required fields:

SELECT Q.account, LAST(Q.firstName), LAST(Q.lastName), LAST(Q.address)
FROM Qp As Q INNER JOIN Qp As T ON Q.Account=T.Account
GROUP BY Q.account, Q.Priority
HAVING Q.Priority = Max(T.Priority)


Indeed, with any other of the three techniques, you would get, possibly,
multiple records per account if there is no CFO, no president, and multiple
"someone else out there", and would eventually required extra processing.
The solution 3, as illustrated here, already take care of keeping just one
record per account. Unfortunately, it is not portable to MS SQL Server
(which lack the powerful LAST aggregate, I know, I repeat myself).



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top