Deleting Duplicate records from a query result

[Shallo9]

Hello All,

I have a query which returns a vast number of records. Now I want to delete any duplicate record (Leaving the first instance).

I can manually copy this resultant data to excel and delete the duplicates easily by using the Remove Duplicates Option. I want this functionality to be incorporated into my query.

NB: I do not want the records to be deleted from the table.


Any help would be highly appreciated.


Many Thanks

 

[recyan]

perhaps
SELECT DISTINCT ........
or
GROUP BY
query.

Thanks

 

[Shallo9]

Already tried with no success.

I've got a PK & a FK in this query result and want to remove the duplicate records based on the FK.

 

[recyan]

on the fly, just check if below gives some guidelines :

SELECT 
	Table1.TestID_PK, 
	Table1.TestName, 
	First(Table2.TestID1_PK) AS FirstOfTestID1, 
	Table2.TestID_FK, 
	First(Table2.TestField2) AS FirstOfTestField2
FROM 
	Table1 
	INNER JOIN 
	Table2 
	ON 
	Table1.TestID_PK = Table2.TestID_FK
GROUP BY 
	Table1.TestID_PK, 
	Table1.TestName, 
	Table2.TestID_FK;

perhaps, if you post the query or the tables involved with some dummy data thrown in & the result you want out of the query, someone should be able to help you.

Thanks

 

[Shallo9]

SELECT tblActivityContact.ActivityID, tblActivityContact.activityContactID, tblActivityContact.contactTypeID, tblActivityContact.contactReasonTypeID
FROM tblActivityContact
WHERE (((tblActivityContact.contactTypeID) In (1,4,5)) AND ((tblActivityContact.contactReasonTypeID) In (19,25,26,27)))
ORDER BY tblActivityContact.activityContactID;

tblActivityContact.ActivityID is the FK which has duplicates

 

[jdraw]

Can you post a few query results and point out the duplicates?

 

[Alansidman]

Here is another alternative

http://www.datapigtechnologies.com/flashfiles/duplicateproblem.html

Alan

 

[Shallo9]

Can you post a few query results and point out the duplicates?

Please find attached

 

[jdraw]

If you are looking to delete/not select duplicates, you must have a clear understanding of what a duplicate is.

With your code

SELECT ActivityID
, activityContactID
, contactTypeID
, contactReasonTypeID
FROM tblActivityContact
WHERE
contactTypeID In (1,4,5) AND 
contactReasonTypeID In (19,25,26,27)
ORDER BY activityContactID;

a duplicate records would have the same values in all of the fields.

I see differences in the fields of the records you are calling duplicates.

For the 109 ActivityId record, the contactTypeIds are different.
For activityID 752 the values for ContactReason are different.

So these are NOT duplicates in Access terms.

Also , the field in your query result jpg, do not match the field names in the query??

Are you using Lookups at the table level??

 

[Shallo9]

Yes they are lookup values. Basically every Activity has Many ContactID. I've sorted the result of the query so that I can keep the first contact and delete the rest.

 

[jdraw]

see http://access.mvps.org/access/lookupfields.htm

Do you still think these records are duplicates? If so, why?

 

[Shallo9]

I think you've completely missed my point. From my query above I get All the records which satisfies my criteria. However the next step is, I need Only 1 record per Activity and where there are more than one records for a given activity I'd sort them by Contact ID and pick the record with the smallest value(Which in other sense would be the first contact). (I can do this with the Remove Duplicates option in Excel would do) My duplicate value is in the Activity ID Column.

 

[jdraw]

I've probably misunderstood some of it for sure. I was confused with your "duplicates" title and the jpg you sent showing the duplicates.

Anyway, if you create a table as per the sample video that Alan suggested post #7 and take your existing query and the current results, the resultant table will have the records you want. The duplicates will show up as key violations and will not be in the table.

You can use the table for whatever you want (query/report) , then discard it or keep it as you wish.

 

[Desiderata]

I have a similar (but different) problem with duplicates. I have a query that compares two tables. The results I'm look for are comparison of activities with supervisors and their team members. I'm looking for activity from any one of a superviors team members. To do this, I want the query to delete any instances of where two or more team members have the same supervisor so the result for that supervisor would be one or none. I've tried the SELECT DISTINCT with no luck. Here is the query-

SELECT Count(Full.[Supervisor Alias]) AS [CountOfSupervisor Alias]
FROM [Full] INNER JOIN TBL_AgentInstallations_all ON Full.[Supervisor Alias] = TBL_AgentInstallations_all.[Agent Alias]
WHERE (((Full.[Status Effect Date])<=([TBL_AgentInstallations_all].[Install Compl]+1)));

Business application-To determine who has completed training on or before the application was installed. "Full" is the training completion data for everyone. The team members can only be identified by their supervisor alias. "TBL_AgentInstallations_all" is the installation data for supervisors only (agents).

Thanks,
Dennis

 

[jdraw]

Please post your database - remove anything private/confidential. I'm sure someone will look.

 

[Desiderata]

This my sandbox copy. It has some in-progress objects that I will delete later.

Thanks in advance!
Dennis

 

[jdraw]

I don't see any relationships in your database.
I think this is where to start.

Here's an article that may help put relationships into context.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

 

[Desiderata]

Thank you. I've added a relationship (Option 3) "Include ALL records from 'TBL_AgentInstallations_all' and only those records from 'Full' where the joined fields are equal."

 

[jdraw]

Glad you have it working.

Setting up relationships and turning Referential Integrity On will help you with your databases.

 

[Desiderata]

It's not working, but at least, there is a relationship now:banghead: