Re: Keeping just one line for each e-mail

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I have the same question, I have a command button that pulls up an email
populating the addresses from a field on a sub form. All works great except
it repeats if the same address is in more than once.

how would the code look to insert into the loop on the command button?

thanks
--
AJ


"John Spencer MVP" wrote:

Use an aggregate query and group by the email address and return First or Last
of the person name fields.

SELECT First(dbo.tbl_Persons.fld_person_name) as PName
, First(dbo.tbl_Persons.fld_person_surname) As SName
, dbo.tbl_Companies.fld_company_name
, dbo.itbl_company_person.fld_preferred_email
FROM (dbo.itbl_company_person INNER JOIN
dbo.tbl_Companies
ON dbo.itbl_company_person.fld_company_id = dbo.tbl_Companies.fld_company_id)
INNER JOIN dbo.tbl_Persons
ON dbo.itbl_company_person.fld_person_id = dbo.tbl_Persons.fld_person_id
WHERE dbo.itbl_company_person.fld_active_relation = 1
GROUP BY dbo.tbl_Companies.fld_company_name
, dbo.itbl_company_person.fld_preferred_email

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

bifteki via AccessMonster.com wrote:
We have a DB in SQL Server which contains a number of tables, three of them
being tbl_Companies, tbl_Persons and itbl_Company_Person. Itbl_Company_Person
is an intermediate table, which actually represents the relationship of a
person with a company (it's a many-to-many relationship).
itbl_Company_Person has a field named fld_preferred_email, which is the
person's company e-mail.
In order for our company to send a newsletter using mass e-mailing, I want to
show all e-mails in the DB. I have written the following query:

SELECT
dbo.tbl_Persons.fld_person_name, dbo.tbl_Persons.
fld_person_surname, dbo.tbl_Companies.fld_company_name,
dbo.itbl_company_person.fld_preferred_email, dbo.
itbl_company_person.fld_active_relation
FROM dbo.itbl_company_person INNER JOIN
dbo.tbl_Companies ON dbo.itbl_company_person.
fld_company_id = dbo.tbl_Companies.fld_company_id INNER JOIN
dbo.tbl_Persons ON dbo.itbl_company_person.
fld_person_id = dbo.tbl_Persons.fld_person_id
WHERE (dbo.itbl_company_person.fld_active_relation = 1)

However there is one problem: In many companies there is no personal e-mail
for each employee, there's just one central e-mail, which is also the value
of the fld_preferred_email field. So, for many companies I get many
duplicates of its central e-mail. This is not good because when we send the
newsletter, in many companies it will be sent as many times as the number of
persons we have in our DB from that company. It will be very annoying, like
spamming them and of course that would be negative advertising.

Moreover, I'm interested in keeping the name and surname of each person as
well as the company name, as we will do some sorting out of the list. Some
persons will be deleted from the list so as to leave only some persons as the
recipients. Of course we could delete the multiple lines this way but it will
be extra work.

Any suggestions how I can get one line for each company? (regardless of who
the person that will show up will be)


.

Quantcast