Keeping just one line for each e-mail

[bifteki via AccessMonster.com]

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)

 

[John Spencer MVP]

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

 

[AJ]

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

 

[bifteki via AccessMonster.com]

Thank you for your answer.

This doesn't work. I get an ADO Error: 'First' is not a recognized built-in
function name.

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

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

[quoted text clipped - 33 lines]

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

 

[John Spencer MVP]

Sorry, I should have realized you might be using ADO from the Dbo.Table.Field
structure.

This makes things a bit more complicated and I'm not quite that good. I think
you might be able to build a ranking query and then use all the records that
had a rank of 0. That MIGHT look something like the following UNTESTED SQL
statement.


SELECT P.fld_person_name, P.fld_person_surname
, C.fld_company_name
, CP.fld_preferred_email, CP.fld_active_relation

FROM dbo.itbl_company_person CP INNER JOIN
dbo.tbl_Companies C ON CP.fld_company_id = C.fld_company_id

INNER JOIN
dbo.tbl_Persons P ON CP.fld_person_id = P.fld_person_id

WHERE(CP.fld_active_relation = 1)
AND P.fldPersonId in (

SELECT A.FldPersonID
FROM
(SELECT P.fld_PersonID
, CP.fld_preferred_email
FROM dbo.itbl_company_person CP
INNER JOIN
dbo.tbl_Persons P ON CP.fld_person_id = P.fld_person_id
WHERE CP.fld_active_relation = 1) As A
LEFT JOIN
(SELECT P.fld_PersonID
, CP.fld_preferred_email
FROM dbo.itbl_company_person CP
INNER JOIN
dbo.tbl_Persons P ON CP.fld_person_id = P.fld_person_id
WHERE CP.fld_active_relation = 1)) as B
ON A.Fld_Preferred_Email = B.Fld_Preferred_Email
AND A.fld_PersonID < B.fld_PersonID
GROUP BY A.fld_PersonID
HAVING Count(*) = 0)


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

Thank you for your answer.

This doesn't work. I get an ADO Error: 'First' is not a recognized built-in
function name.

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

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

[quoted text clipped - 33 lines]

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