Solved

Finding a unique value in Access 2010 query

Posted on 2014-11-11
11
64 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

910 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

21 Experts available now in Live!

Get 1:1 Help Now