Solved

Finding a unique value in Access 2010 query

Posted on 2014-11-11
11
62 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)
Comment Utility
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
Comment Utility
@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)
Comment Utility
@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
 

Author Comment

by:contrain
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 45

Expert Comment

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

Author Comment

by:contrain
Comment Utility
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
Comment Utility
please post your SQL
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
This query solved what I was looking for and correctly picked the fields where I was looking for duplicates.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

6 Experts available now in Live!

Get 1:1 Help Now