Removing Duplicates from Query Results or Mailing Label Report

[DennisAS]

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.

[pbaldy]

Change Group By to Where on the fields with criteria and see what that gives you.

[DennisAS]

How would I use Where?
I tried setting it to Where and nothing happened. I think it requires some parameters.

[pbaldy]

Huh? What does it look like in design view now? Or can you attach the db here?

[DennisAS]

I posted screenshots of the Design View and part of the output.

[pbaldy]

Yes, before the changes I recommended. I'm curious what it looks like after implementing those. It should have changed the output dramatically.

[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.

[pbaldy]

You would have gotten the same result by changing Group By to Where, I suspect. In any case, glad you got a solution and welcome to the site by the way!

[DennisAS]

Thanks for the welcome.

[pbaldy]

By the way, if you're near the cities in your data, I'm just over the hill.