Access select distinct rows based only on certain fields

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
LVL 10
ecarboneAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
"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
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
ecarboneAuthor Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Rey Obrero (Capricorn1)Commented:
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
 
ecarboneAuthor Commented:
Thank you both. This is exactly what I was looking for.
0
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.