Hide Row based on duplicate data in selected field

Posted on 2014-08-05
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
    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 33

    Accepted Solution

    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 26

    Expert Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    How get a scanned image on a report 4 16
    Wrong number of arguments 2 17
    message box in access 4 22
    System Analysis 5 14
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    734 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

    24 Experts available now in Live!

    Get 1:1 Help Now