Solved

Finding a unique value in Access 2010 query

Posted on 2014-11-11
11
71 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

713 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