Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

MacroScope

Programmer
Jul 17, 2010
286
US
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.
 
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
 
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.
 
Well, MacroScope (Programmer), what's your logic for accomplishing this? You give us the logic, we'll help you with syntax and procedure.

 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top