Solved

Access select distinct rows based only on certain fields

Posted on 2013-06-17
6
841 Views
Last Modified: 2013-06-18
I have a mailing list with 100 rows.
Each row represents a catalog request.
There are 5 difference catalogs.
If a user requests 3 catalogs, he appears in 3 rows.
If a user requests only 1 catalog, he only appears one time.

I am trying to write a query that returns the unique addresses within the table.
So in other words... I don't care if a customer requested 1 catalog or 3 catalogs or 5 catalogs... I only want to see his address one time in the query result.

What makes an address unique? Concatenate 5 fields: Name + Address + City + State + Zip. Call that 'MailingAddress', then that is what needs to be unique. This ensures that if two people at the same address requested a catalog, they would both remain on the mailing list.


Sample data:
   John Smith - 123 Main Street, Atlanta, GA - CatalogA
   John Smith - 123 Main Street, Atlanta, GA - CatalogB
   John Smith - 123 Main Street, Atlanta, GA - CatalogC
   Michelle Michaels - 4226 Hollywood Blvd, Hollywood, CA - CatalogE
   Charlotte Nieves - 426 NW 20th Avenue, Seattle, WA - CatalogA
   Charlotte Nieves - 426 NW 20th Avenue, Seattle, WA - CatalogB
   Michelle Michaels - 4226 Hollywood Blvd, Hollywood, CA - CatalogC
   John Smith - 123 Main Street, Atlanta, GA - CatalogA

So the results of the query should give me:
   John Smith - 123 Main Street, Atlanta, GA
   Michelle Michaels - 4226 Hollywood Blvd, Hollywood, CA
   Charlotte Nieves - 426 NW 20th Avenue, Seattle, WA
0
Comment
Question by:ecarbone
  • 2
  • 2
  • 2
6 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 39254446
drop the field for the catalog from your query and use a total query ( group by )

select [Name], Address
from tableName
group by [Name], address
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39255242
"select distinct" is utterly unconditional
i.e. "select distinct" considers the whole row (so if you have catalog in the row it is being considered).

so "select distinct rows based only on certain fields"
means you cannot use select distinct on ALL columns (to meet the wanted conditions).

select distinct
   Name, Address ,City, State, Zip
from YourTable

OR

select
   Name, Address ,City, State, Zip
from YourTable
group by
   Name, Address ,City, State, Zip

and, an advantage of using group by would be something like this:

select
   Name, Address ,City, State, Zip, count(distinct catalog)
from YourTable
group by
   Name, Address ,City, State, Zip
having count(distinct catalog) > 0

NB: this is not access syntax (sorry, not an Access user) but hopefully it's useful
0
 
LVL 10

Author Comment

by:ecarbone
ID: 39256345
Hi Paul,
Thank you. A few questions:

1.
So, I can use either one ('select distinct' or 'group by') AS LONG AS I leave out the fields that are not considered part of the 'de-duplication' criteria. Is that correct?

2.
Seems like 'select distinct' is more concise and gives me a quick result, whereas the 'group by' method gives me the same result but also allows me to use some sort of calculation (such as 'count' or 'sum'). Is that correct?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39256445
yes to both 1 and 2

if you want to display all the catalogs in one field, that can also be done by using a user define function in your query

i.e.,
Name             Address                                    Catalogs
John Smith - 123 Main Street, Atlanta, GA  CatalogA; CatalogB; CatalogC
0
 
LVL 10

Author Closing Comment

by:ecarbone
ID: 39257681
Thank you both. This is exactly what I was looking for.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39257999
1 correct

2. Don't underestimate the advantages of 'group by'; but you are also correct here - 'select distinct' can be very convenient.

Just remember that "the whole row" is compared by select distinct so if there are lots of big text fields the query sql may be brief but the query execution not brief
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question