Need a query that will list only one name from many companies in db that have several names 1

[MacroScope]

I have a database that has many contacts from many companies. One company has 145 contacts in it, others have 1-5, some others up to 40. I am emailing them in small groups and I want to only email one person from each company with each mailing. As a contact receives an email a Sent checkbox is set to true, so it's easy not to duplicate individuals, but my problem is to create a list that only has one name from each company (whose Sent field is false).

Does anyone have a suggestion on how to accomplish that?

As always, all help will be appreciated.

 

[dhookom]

There is no solution until you can identify which of the 145 contacts should get the email. Do you want the one with the minimum ContactID or the maximum Email? Are you satified with any random contact?

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

I'm satisfied with any random contact. Eventually all will be contacted, but I don't have any preference as to which is first, etc.

 

[dhookom]

If you don't care who gets the email, you can use a totals query and with Max(EmailField) or First(EmailField).

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Well, MacroScope (Programmer), what's your logic for accomplishing this? You give us the logic, we'll help you with syntax and procedure.

 

[MacroScope]

I haven't written the logic to create the list of potential recipients. That's what I'm struggling with. What I'll do when I have the results I need is to use that query as the Record Source for a form. I don't need assistance with the actual emailing logic. It's written and works fine. I just want to be able to move through a form, record by record, which is populated with only one random record from each company.

There is an unbound field on the form for user to input the total number of emails to be generated in one mailing. So, if I select 30, then the first 30 records in the list containing only one name from each company will receive mails. If a company has only one contact and that person has already received a mail then of course there are no remaining contacts so that company is no longer considered in the list for future mailings.

I have many more fields in the database, but for simplicity consider CompanyName, ContactName, and EMail (where Sent=False) as the only fields to be returned. I can add any others as needed. It's simply the creation of the list through appropriate sorting of the company name and contacts that I'm trying to resolve.

I hope that answers your question.

 

[MacroScope]

Thank you, Duane. Your simple and obvious solution solved the problem for me! It's amazing how something so obvious can be unseen, and then it's like, "OF COURSE!" when someone points it out.

I appreciate the help.