CODEWITHSUNDEEP
sqlms-access
tontheman
tontheman

Reputation: 13

Microsoft Access: Select one email address per company

I have a table that looks similar to this:

+---------+---------+-----------------+
| company |  type   |      email      |
+---------+---------+-----------------+
| ID001   | user    | [email protected]  |
| ID001   | admin   | [email protected] |
| ID002   | user    | ...             |
| ID002   | admin   | ...             |
| ID002   | janitor | ...             |
| ID003   | admin   | ...             |
+---------+---------+-----------------+

I would like to SELECT all email addresses where the type = user. If user doesn't exist, it should select admin. If that also doesn't exists, it should select janitor, etc.

At the end I need a result with only one email address per company. I was able to sort by type and then use SELECT TOP 1 like this:

SELECT TOP 1
  contacts.company,
  contacts.type,
  contacts.email
FROM contacts
WHERE contacts.Email <> "" AND contacts.company = "ID001"
ORDER BY 
IIf([contacts.type] = 'user',1,
IIf([contacts.type] = 'admin',2,
IIf([contacts.type] = 'janitor',3,
4)))) ASC)

This only works for one specific company, see the WHERE clause. How can I make it work for the whole table?

Upvotes: 1

Views: 46

Answers (2)

Parfait
Parfait

Reputation: 107577

Consider a conditional aggregate query with nested IIF():

SELECT c.company,
       MAX(IIF(c.type = 'user' AND c.Email IS NOT NULL, c.Type,
               IIF(c.type = 'admin' AND c.Email IS NOT NULL, c.Type,
                   IIF(c.type = 'janitor' AND c.Email IS NOT NULL, c.Type, NULL)
                  )
               )
          ) As final_type,
       MAX(IIF(c.type = 'user' AND c.Email IS NOT NULL, c.Email, 
               IIF(c.type = 'admin' AND c.Email IS NOT NULL, c.Email,
                   IIF(c.type = 'janitor' AND c.Email IS NOT NULL, c.Email, NULL)
                  )
               )
          ) As final_email
FROM contacts c
GROUP BY c.company

Upvotes: 1

Lee Mac
Lee Mac

Reputation: 16015

Since your table contacts does not have a primary key field, then you could use something along the lines of the following:

SELECT
    t.company,
    t.type,
    t.email
FROM
    contacts t
WHERE
    t.company & t.type =
    (
        SELECT TOP 1 
            contacts.company & contacts.type
        FROM 
            contacts
        WHERE 
            contacts.Email IS NOT NULL AND contacts.company = t.company
        ORDER BY 
            IIf([contacts.type] = 'user',1,
            IIf([contacts.type] = 'admin',2,
            IIf([contacts.type] = 'janitor',3,
            4))) ASC
    )

This assumes that the concatenation of the company & type will be unique within the dataset.

Upvotes: 1

Related Questions