Several locations of a company with same email skip

[Ruegen]

I require code that will let me skip emailing a record if it has a matching email of another record - I don't want to be sending the same letter twice to the same email as some of our customers have more than 12 locations that all have the same email on record.



I thought about the database building and attaching an array that if the email didn't match anything in the array then email - so every record would place it's email in the array and a function would search the array to see if any of them matched.

Would this be practical and can it be done?

[pbaldy]

It might help to see your code, but typically I'd expect to be looping a query of email addresses. That query is where I'd eliminate duplicates, either with DISTINCT or GROUP BY.

[Ruegen]

It might help to see your code, but typically I'd expect to be looping a query of email addresses. That query is where I'd eliminate duplicates, either with DISTINCT or GROUP BY.

If I can remove duplicates using "DISTINCTROW" like you say and I am assuming it still lets you see one in the query in order for you to email it - that way all emails are delivered only once. Am I right?

[pbaldy]

Will, I mentioned DISTINCT, not DISTINCTROW; they aren't the same. In any case, the principle is to create a query that returns the values as you want/need and either use the query or the SQL in a recordset.

[Ruegen]

Will, I mentioned DISTINCT, not DISTINCTROW; they aren't the same. In any case, the principle is to create a query that returns the values as you want/need and either use the query or the SQL in a recordset.

whoops yeah sorry I picked the wrong one - distinct.

thanks

[Ruegen]

Will, I mentioned DISTINCT, not DISTINCTROW; they aren't the same. In any case, the principle is to create a query that returns the values as you want/need and either use the query or the SQL in a recordset.

Can you write in SQL that you only want to see the one single field as distinct and show the others?

[Ruegen]

DISTINCT won't give me the ID of the school - if I add ID field then I don't get distinct on the email...

[pbaldy]

Since the email could be multiple schools, which school's would you want? Sounds like you want a totals query, but you haven't really given much detail.

[Ruegen]

Since the email could be multiple schools, which school's would you want? Sounds like you want a totals query, but you haven't really given much detail.

My sql prints out as

SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, tblSchools.AreaID, tblSchools.SchoolEmail, tblSchools.[1ContactName], tblSchools.[1ContactSurname], tblSchools.Removed 
FROM tblSchools 
WHERE tblSchools.AreaID=112 AND tblSchools.Removed Is Null;

now some of these records show the same email - I'd like to define just one (I don't care which) however if I use distinct I won't be able to get fields like newschoolsid - lest I get all the rows.

doing

SELECT DISTINCT SchoolEmail
FROM tblSchools;

gives me distinct emails but nothing else

that's why I thought an array would work best, it would go through these records, putting in an email and then looping through the array to check if it is already added (and if not skip or rs.movenext since I am going through a recordset).

[Ruegen]

I guess if I could pass in a public variable to a function and the field (rs!schoolemail) then I could have a bool function

if bAreadySent(varArray(),rs!schoolemail) = true then
rs.movenext
end if

[pbaldy]

Since you don't care which, how about:

SELECT Max(NewSchoolsID) As MySchoolID, Max(SchoolName) As MySchoolName, Max(AreaID) As MyAreaID, SchoolEmail,
Max([1ContactName]) As MyContactName, Max([1ContactSurname]) As YouKnowTheDrill
FROM tblSchools
WHERE tblSchools.AreaID=112 AND tblSchools.Removed Is Null
GROUP BY SchoolEmail

[Ruegen]

Since you don't care which, how about:

SELECT Max(NewSchoolsID) As MySchoolID, Max(SchoolName) As MySchoolName, Max(AreaID) As MyAreaID, SchoolEmail,
Max([1ContactName]) As MyContactName, Max([1ContactSurname]) As YouKnowTheDrill
FROM tblSchools
WHERE tblSchools.AreaID=112 AND tblSchools.Removed Is Null
GROUP BY SchoolEmail

hey that works! but how?

[pbaldy]

It gets the max value of the other fields for each email address. Only works if you don't really care about the other fields, just need a value.

[Ruegen]

It gets the max value of the other fields for each email address. Only works if you don't really care about the other fields, just need a value.

Yes that works well - it's a batch mail out so as long as the email gets out to the email available it doesn't matter which school. If it needed to be personal I wouldn't have used this method.

Thank you so much

[pbaldy]

Happy to help!