Solved

Access select distinct rows based only on certain fields

Posted on 2013-06-17
6
835 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 119

Expert Comment

by:Rey Obrero
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now