Duplicate Records with multiple fields (1 Viewer)

[honey2wood]

Hi, I have had a quick look through these threads but can find nothing that gives me the answer I need.
I have a Training Database with a Query that I need to identify/remove duplicates from.
There are 35 employees (EMP_ID) with around 40 different procedures (Pro_Num).
Each Procedure has at least one issue Number (Pro_Iss)but possibly two or three depending on historic training and changes to procedures.
When an Employee is trained a sequential record number is given to the training session (TrRNo) this means more than one employee can have the same trRNo.
The Record table the query is taken from has a primary key (Rec_ID)
A lot of employees are trained on a lot of procedures. Some are trained on the same procedure with different Issue numbers.
What I need to find is duplicates where Employee and procedures are the same.
This should show if someone has trained on a number of versions.
I am hoping to be able to use a delete query to remove the older versions.
It will also show up where someone has been entered in the same version more than once.
Apart from the Rec_ID I cant see and unique individual fields to sort. What I need is a set of unique fields.
I seem to be going round in circles
Many thanks
Graham

 

[Uncle Gizmo]

I suspect you've supplied all the information necessary to answer your question, however it's hidden in the way you have presented it. I'm thinking a better approach to asking the question, might be to supply some sample data, possibly a list of what you've got and a list of what you'd like.

 

[Pat Hartman]

Are you sure you want to delete the history? If it isn't needed, perhaps you should not keep it. You could update the date and version rather than inserting a new row.

 

[arnelgp]

What I need to find is duplicates where Employee and procedures are the same.

this will get you the result:

SELECT query1.emp_id, query1.pro_num, Count([emp_id] & [pro_num]) AS x
FROM query1
GROUP BY query1.emp_id, query1.pro_num
HAVING (((Count([emp_id] & [pro_num]))>1));

 

[honey2wood]

Hi,
Thanks for getting back to help all.
I have been trying to get a sample which has all the different possibilities as requested by Gizmo. I have attached if it clarify.
Hi Pat. Updating the date and version would work but not everyone would have the training at the same time.
arnelgp. I need to test this but it looks promising.
Thanks

 

[isladogs]

Hi
Have a look at the attached and check if it correctly removes duplicate items
The queries I've done indicate 13 duplicate records should be deleted

EDIT
Oops - I originally uploaded a copy of the original file
Amended version now attached!

 

[honey2wood]

Thank you to all that have got back to this 'problem'. I now have the solution I needed. I just now need to understand how it works.

 

[isladogs]

Glad you have a solution.
If you used my example, just ask if you need an explanation