Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Finding a unique value in Access 2010 query

Posted on 2014-11-11
11
Medium Priority
?
84 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 49

Expert Comment

by:Dale Fye
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 46

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 49

Expert Comment

by:Dale Fye
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

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 46

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 46

Expert Comment

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

Expert Comment

by:Dale Fye
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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

580 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