Results 1 to 2 of 2
  1. #1
    Join Date
    May 2017
    Posts
    2
    Provided Answers: 1

    Answered: Removing Duplicates from Microsoft Access Query Results or Mailing Label Report

    I am trying to adjust a query or report that will get me the addresses of all the monthly non-contract vendors for the our Flea Market for the last eight months for printing mailing labels. I have the query created but I need a way to remove duplicates since many of the vendors have sold multiple months. I've included the unique VendorID in the query in the hopes that it can be used either in the query itself or the mailing labels Report to eliminate all but one of the duplicate entries. I don't want to delete anything, just not have them show in the query results or on the mailing labels.

    I've attached a screenshot of the query design and the current output.

    Here is the SQL generated by Microsoft Access:
    SELECT DISTINCTROW Vendors.[First Name], Vendors.[Last Name], Vendors.[Street Address], Vendors.City, Vendors.State, Vendors.Zipcode, Ticket.Class, Ticket.[Market Date], Vendors.VendorID
    FROM Vendors INNER JOIN Ticket ON Vendors.VendorID = Ticket.[Vendor ID]
    GROUP BY Vendors.[First Name], Vendors.[Last Name], Vendors.[Street Address], Vendors.City, Vendors.State, Vendors.Zipcode, Ticket.Class, Ticket.[Market Date], Vendors.VendorID
    HAVING (((Ticket.Class)<>"Contractor") AND ((Ticket.[Market Date])=#11/5/2016#)) OR (((Ticket.Class)<>"Contractor") AND ((Ticket.[Market Date])=#12/3/2016#)) OR (((Ticket.Class)<>"Contractor") AND ((Ticket.[Market Date])=#1/7/2017#)) OR (((Ticket.Class)<>"Contractor") AND ((Ticket.[Market Date])=#2/4/2017#)) OR (((Ticket.Class)<>"Contractor") AND ((Ticket.[Market Date])=#3/4/2017#)) OR (((Ticket.Class)<>"Contractor") AND ((Ticket.[Market Date])=#4/1/2017#)) OR (((Ticket.Class)<>"Contractor") AND ((Ticket.[Market Date])=#5/6/2017#)) OR (((Ticket.Class)<>"Contractor") AND ((Ticket.[Market Date])=#6/3/2017#))
    ORDER BY Vendors.[Last Name];

    I've tried changing DISTINCTROW to DISTINCT in the SQL as suggested by someone but it did not help.
    I've also tried using the Property Sheet to set Unique Values to Yes and to set Unique Records to Yes but neither helped.
    I've also been told the using "Where" in the Total selection could possibly help but I don't know how to use it.

    Click image for larger version. 

Name:	QueryDesign.jpg 
Views:	0 
Size:	159.5 KB 
ID:	17362
    Click image for larger version. 

Name:	Output.jpg 
Views:	2 
Size:	482.1 KB 
ID:	17363

  2. Best Answer
    Posted by DennisAS

    "I was able to get the answer from the Microsoft Community.
    https://answers.microsoft.com/en-us/...5-5536ab912b99

    From Ken Sheridan:
    The problem is that you are returning the Market Date column in the query's result table, so the rows are not distinct. If you uncheck the 'Show' checkbox for that column in query design view, and use the SELECT DISTINCT predicate this should return one instance of each addressee. Do the same for the Class column. You can then remove the unnecessary grouping from the query.

    Here is a screen shot of the revised query design.
    Attachment 17364"


  3. #2
    Join Date
    May 2017
    Posts
    2
    Provided Answers: 1

    Answer Found

    I was able to get the answer from the Microsoft Community.
    https://answers.microsoft.com/en-us/...5-5536ab912b99

    From Ken Sheridan:
    The problem is that you are returning the Market Date column in the query's result table, so the rows are not distinct. If you uncheck the 'Show' checkbox for that column in query design view, and use the SELECT DISTINCT predicate this should return one instance of each addressee. Do the same for the Class column. You can then remove the unnecessary grouping from the query.

    Here is a screen shot of the revised query design.
    Click image for larger version. 

Name:	QueryDesignRevised.jpg 
Views:	2 
Size:	141.5 KB 
ID:	17364

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •