Append Query and search for duplicates

[Pancakes1032]

Hello,

I created an Append Query to add data to an existing on-going table. I don't mind if duplicates are added to this table, I was just wondering if there was a way to add a macro or some type of VBA to search after the new data is pasted on the existing table for any duplicates of a specific field and find the data of the corresponding field that has the employee name attached to that assignment.

Again I don't mind the duplicates because sometimes a task for one assignment may be on different reports that are being combined by this Append Query, but I do need to know which worker has already been assigned this task.

Not sure if this is possible, any ideas would help.

Thank you!

[jonh]

Add the fields you want to check for duplicates to a query and turn on totals.
Add a new field 'Dups: 1' and change its Total from 'Group By' to 'Count'.

select employee, fieldX, count(1) as Dups from table1
group by employee, fieldX

[HiTechCoach]

Hello,

I created an Append Query to add data to an existing on-going table. I don't mind if duplicates are added to this table, I was just wondering if there was a way to add a macro or some type of VBA to search after the new data is pasted on the existing table for any duplicates of a specific field and find the data of the corresponding field that has the employee name attached to that assignment.

Again I don't mind the duplicates because sometimes a task for one assignment may be on different reports that are being combined by this Append Query, but I do need to know which worker has already been assigned this task.

Not sure if this is possible, any ideas would help.

Thank you!

Access has a built-in Query wizard for find duplicates. Give that a try.

[Pancakes1032]

I have used the wizard, my only issue is that is pulling all the duplicates and not leaving one record there and because I intend to eventually delete the duplicates I need at least one of the duplicate records to stay. This one is pulling all the data pretty much.

Here's the SQL code:
SELECT CFRRR.[caseid], CFRRR.[status], CFRRR.[CFRRRID], CFRRR.[WorkerID], CFRRR.[Workeremail], CFRRR.[Workername], CFRRR.[Dateassigned], CFRRR.[assignedby], CFRRR.[assignedto], CFRRR.[RRRmonth], CFRRR.[Scheduleddate], CFRRR.[scheduledtime], CFRRR.[type], CFRRR.[ScheduledType], CFRRR.[language], CFRRR.[lastname], CFRRR.[firstname], CFRRR.[Checked in (Y/N)], CFRRR.[Qmaticid], CFRRR.[CompletedType], CFRRR.[actiondate], CFRRR.[verifyduedate]
FROM CFRRR
WHERE (((CFRRR.[caseid]) In (SELECT [caseid] FROM [CFRRR] As Tmp GROUP BY [caseid],[status] HAVING Count(*)>1 And [status] = [CFRRR].[status])))
ORDER BY CFRRR.[caseid], CFRRR.[status];

[HiTechCoach]

You can use a subquery to do what you want.

See Subquery basics here http://allenbrowne.com/subquery-01.html

Scroll down to: Delete duplicate records