Hide Row based on duplicate data in selected field

Posted on 2014-08-05
Medium Priority
Last Modified: 2016-06-07
I have a SQL query in MS Access that doesn't seem to be working correctly.  Basically I want to hide rows of data that contain the same email address.  My query is hiding all entries with the same email address when I still need to see one.

SELECT DISTINCT Query1.SizeSmall, Query1.ID, Query1.CorpContact, Query1.EmailHyperlink, Query1.CategoryID, tblCategories.Category
FROM Query1 INNER JOIN tblCategories ON Query1.CategoryID = tblCategories.CategoryID
GROUP BY Query1.SizeSmall, Query1.ID, Query1.CorpContact, Query1.EmailHyperlink, Query1.CategoryID, tblCategories.Category, [Query1.EmailHyperlink]
HAVING ((([Query1.EmailHyperlink]) In (SELECT [EmailHyperlink] FROM [Query1] As Tmp GROUP BY [EmailHyperlink] HAVING Count(*)=1 )))
ORDER BY Query1.SizeSmall;

Open in new window

This is for an emailing system where my end user can select categories of people to send to.
Question by:napsternova
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40241885
Not sure I fully understand...

But a simple query like this will list al of the unique emails in a table.

SELECT YourTable.EmailAddress
FROM YourTable
GROUP BY YourTable.EmailAddress;

LVL 40

Accepted Solution

PatHartman earned 2000 total points
ID: 40244390
Duplicates can be pesky to handle.  Your query includes many columns in addition to the email and therefore a Group By will not solve the problem.  You need to use Group By for ONLY the email address and then use First() for all the other columns since you don't seem to care what the other values are.  You could use Min or Max for dates but First works as well as anything for all the other values.
LVL 28

Expert Comment

ID: 41640941
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: PatHartman (http:#a40244390)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

Experts-Exchange Cleanup Volunteer

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

840 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