Duplicate check query showing non-duplicate data

S

ScrmingWhisprs

I <3 Coffee Milk.
Local time
Today, 07:34
Joined
Jun 29, 2006
Messages
156
Hi,

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

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

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

Code: 
id	list_id	record_id
2123	24	6374
2124	24	6374
2125	24	44	<------
2126	24	6664	<------
2128	24	5958
2129	24	5958
2130	24	6926
2131	24	6926
2133	24	6667
2134	24	6667
2135	24	6373
2136	24	6373
 
Last edited:
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.
 
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

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

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.
Code: 
SELECT *
FROM tblListDetails
WHERE record_id IN 
   (
   SELECT record_id
   FROM tblListDetails
   WHERE list_id = 24 
   GROUP BY record_id 
   HAVING Count(*) > 1 
   )
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom