Solved

Access select distinct rows based only on certain fields

Posted on 2013-06-17
6
847 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 49

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 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