Pull only highest level contact

I'm still new to Access so please bear with me...

In our database we have accounts that are a Company and within the Company account there are 1-4 contacts. These contacts are listed as A,B,C, & D. I have a query that pulls the company and lists all the contacts associated with it.

My question is... is there a way to just pull ONLY the highest level contact? For example, IF no A, then B, IF no B, then C, IF no C, then D?

Would this be a criteria in the contact field?

Solution

I think you can get what you want from a GROUP BY query. In this example, contact_id is the field which holds the A-D values:

SELECT
    y.company_id,
    Min(y.contact_id) AS highest_level_contact
FROM YourTable AS y
GROUP BY y.company_id;

That's only an outline to guide you. But you should start with a new SELECT query in the query designer. Choose the table and the 2 fields. The click the "Totals" icon (which looks like the Greek letter sigma) and choose "Group By" from the dropdown under your company_id field and "Min" under the contact_id field.