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
Solved

Finding a unique value in Access 2010 query

Posted on 2014-11-11
11
68 Views
Last Modified: 2014-12-29
In Access 2010, I have a query based on a table that has fields for FName, LName, Company, and Email. Some of the records have the same FName, LName and Email. for these records, just focusing on the Email field, I want to create a query that will only give me a record where the email is unique, so I want to get rid of the records that have duplicate fields.

Thanks,
0
Comment
Question by:contrain
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40437149
SELECT DISTINCT  FName, LName and Email FROM yourTable

should do it.  Another method:

SELECT  FName, LName and Email
FROM yourTable
GROUP BY  FName, LName and Email
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40437732
@Dale

LName and Email
your use of "and" is incorrect in the Select clause

==================
This query will give you a candidate list of duplicates:
SELECT  FName, LName, Email, Count(Email) As CountOfEmail
FROM myTable
GROUP BY  FName, LName, Email
Having Count(Email) <> 1

Open in new window


However, it produces a non-updatable dataset.  
Workarounds:
* Use the above query to populate a new table.  Then link the delete candidate table with your original table to do the deletes.
* Create a delete query.  The following query requires that you have some autonumber field.  I am using ID to denote such a field.
Delete * 
FROM myTable
WHERE
DCount("EMail", "myTable", "LName=""" & LName &""" and FName=""" & FName & """ and EMail=""" & EMail & """) <> 1
And 
ID <> DMin("ID", "myTable", "LName=""" & LName &""" and FName=""" & FName & """ and EMail=""" & EMail & """)

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40437772
@aikimark,

yeah, tough morning.

@contrain

SELECT DISTINCT  FName, LName, Email FROM yourTable

or

SELECT  FName, LName, Email
FROM yourTable
GROUP BY  FName, LName and Email

Both of these will give a list of all of the FNAME, LNAME, Email combinations.  If you truely want to clean out the duplicates, take a look at aikimarks post.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 

Author Comment

by:contrain
ID: 40438008
Dale Fye,


When I run either of your queries, I get the "Syntax Error in FROM Clause" message. Maybe I'm putting it in the wrong place, I am creating a query, and changing the generic name yourTable to the name of my table which is SFOfficeList (I'm putting it in the SQL window) and running it. Am I putting your code in the wrong place?
0
 

Author Comment

by:contrain
ID: 40438018
aikimark,

when I run your delete query and change your generic table name to the real name of my table I get a message stating "The expression you entered has an invalid .)dot) or ! operator or invalid parentheses."

What I am doing is going into the query window, choosing Delete, adding the one field to the grid I want to run the query on (in this case email) and doing Shift-F2 to open the window and pasting your code in there and then running it. I may be doing something wrong but need to know what that is.

Thanks,
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40438200
Does your table have an autonumber field named ID?
0
 

Author Comment

by:contrain
ID: 40438419
aikimark,

Yes, my table does have an autonumber field called ID, the program created it when it made the table. Thanks.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40438429
please post your SQL
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40438453
I repeated the same error in the 2nd query that was originally there, but only in the GROUP BY clause

SELECT DISTINCT  FName, LName, Email FROM yourTable

or

SELECT  FName, LName, Email
FROM yourTable
GROUP BY  FName, LName, Email
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40472490
I think from your question that you actually need a query that will yield records with duplicate combinations of FName, LName and Email.  I made queries for this purpose (and a form based on the table created by the make-table query) recently.  Here is a screen shot of the queries; note that qryDuplicateContacts screens for just the records that have duplicate values in the relevant fields (your fields will be different, of course):

Dupes checking queries
0
 

Author Closing Comment

by:contrain
ID: 40522280
This query solved what I was looking for and correctly picked the fields where I was looking for duplicates.
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

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

856 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