Single email address from multiple records

[dazza_dog]

I have a database that contains multiple companys (1000+) and each company
has multiple email addresses.
I wish to produce a query that contains 1 email address for each company
(there are further filters to apply but this is easy)

Any ideas gratefully accepted

Darryn

 

[John Spencer MVP]

Easiest way is to use an aggregate (totals) query where you Group By all the
fields except the email address and use First or Max or Min or Last on the
email address.

Build your query to select all the fields you want to display
Select View Totals from the menu
Change GROUP BY to FIRST under the EMAIL address.

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

 

[dazza_dog]

John

Thanks for this, unforunatley I can not get it to work. The SQL Query is as
follows:

SELECT tbl_eMail_Addresses.Company, tbl_eMail_Addresses.[First Name],
tbl_eMail_Addresses.[Last Name], tbl_eMail_Addresses.[Job Title],
First(tbl_eMail_Addresses.[E-mail Address]) AS [FirstOfE-mail Address]
FROM tbl_eMail_Addresses
GROUP BY tbl_eMail_Addresses.Company, tbl_eMail_Addresses.[First Name],
tbl_eMail_Addresses.[Last Name], tbl_eMail_Addresses.[Job Title];

tbl_eMail_Addresses.Company contains the ID of the company and is only
included for ease of debugging

The query still displays all of the email addresses for each company

Any ideas

Thanks

Darryn

 

[John W. Vinson]

John

Thanks for this, unforunatley I can not get it to work. The SQL Query is as
follows:

SELECT tbl_eMail_Addresses.Company, tbl_eMail_Addresses.[First Name],
tbl_eMail_Addresses.[Last Name], tbl_eMail_Addresses.[Job Title],
First(tbl_eMail_Addresses.[E-mail Address]) AS [FirstOfE-mail Address]
FROM tbl_eMail_Addresses
GROUP BY tbl_eMail_Addresses.Company, tbl_eMail_Addresses.[First Name],
tbl_eMail_Addresses.[Last Name], tbl_eMail_Addresses.[Job Title];

tbl_eMail_Addresses.Company contains the ID of the company and is only
included for ease of debugging

The query still displays all of the email addresses for each company

Of course, because you're selecting and grouping by first name, last name, and
job title as well as company.

Do you want to see the name and title of the owner of the selected email
address? If so, use First for each of them as well and don't group by them.

Note that this will give you the first record in disk storage order. This
might be the email address of an assistant temporary mail clerk or it might be
that of the CEO - does that matter?

 

[dazza_dog]

John W

Thanks for the help.
GROUPed by company, did FIRST for everything else and using WHERE to get the
records I need

Again thanks

Darryn

John

Thanks for this, unforunatley I can not get it to work. The SQL Query is as
follows:

SELECT tbl_eMail_Addresses.Company, tbl_eMail_Addresses.[First Name],
tbl_eMail_Addresses.[Last Name], tbl_eMail_Addresses.[Job Title],
First(tbl_eMail_Addresses.[E-mail Address]) AS [FirstOfE-mail Address]
FROM tbl_eMail_Addresses
GROUP BY tbl_eMail_Addresses.Company, tbl_eMail_Addresses.[First Name],
tbl_eMail_Addresses.[Last Name], tbl_eMail_Addresses.[Job Title];

tbl_eMail_Addresses.Company contains the ID of the company and is only
included for ease of debugging

The query still displays all of the email addresses for each company

Of course, because you're selecting and grouping by first name, last name, and
job title as well as company.

Do you want to see the name and title of the owner of the selected email
address? If so, use First for each of them as well and don't group by them.

Note that this will give you the first record in disk storage order. This
might be the email address of an assistant temporary mail clerk or it might be
that of the CEO - does that matter?