Duplicate check query showing non-duplicate data (1 Viewer)

Hi,

I have the following duplicate check query that is returning inaccurate results:


Below are the results and the two rows that I have marked for some reason are being displayed...

It must be that tblListDetails contains multiple records where record_id = 44, so your Count(*) criteria is True, but only one of them has a list_id = 24.

Hi MarkK,

Thank you for the response. That makes perfect sense and yes, record_id 44 and 6664 are very likely to exist in tblListDetails under a different list_id. I'm curious if you would have an idea on how I can limit the duplicates to a certain list ID exclusively?

I'll explain what I'm trying to achieve:
These "lists" are basically containers for users to add records for other uses (report printing and exporting, for example). A user can search for records, select and easily add to a list from the search results screen. It can be possible for the same record to be added to a list multiple times. On the screen where a user views the records contained in that list, I have a Dedupe button that would remove all duplicated records. If a user was going to export a list for a mailing, they wouldn't want to have the same record printed twice.

I was hoping to use a duplicate query to find the repeated records and delete them from the list, keeping the unique records in the list. Perhaps there is an easier way?

-Mike

maybe you can filter on the subquery:

SELECT *
FROM tblListDetails
WHERE tblListDetails.record_id In (SELECT [record_id] FROM [tblListDetails] As Tmp WHERE Tmp.tblListDetails.list_id=24 GROUP BY [record_id] HAVING Count(*)>1 )

ScrmingWhisprs said:

I'm curious if you would have an idea on how I can limit the duplicates to a certain list ID exclusively?

Click to expand...

Arnel's got it, just move the "WHERE list_id = 24 " clause into the subquery.

Thanks for the input! I will give that a try tonight.

So unfortunately that did not work. When I ran the query in my test query builder I received an Enter Parameter Prompt with "Tmp.tblListDetails.list_id" above the input box. I can try using 2 queries. I was trying to accomplish this with 1 SQL statement within VBA code, only because my backend is a hosted MySQL database.

I'm not sure about this but Tmp.tblListDetails.list_id looks over qualified. Maybe Tmp.list_id would be better. If you don't want to wait for arnelgp to respond you could try


and see what happens.

You don't need to alias any tables in there I don't think. You only have to do that if the subquery uses a field from the main query, or vice-versa, or if it improves clarity somehow, and that doesn't really occur here.