Deleting Duplication but keeping original

[puppy26]

Hi all,


I would like to execute a code to remove the duplications in the MS Access Query but keeping one one data of the duplications.

For example, in the below data, I want to check for the data in the column Part No. The part number 123 and 234 are repeated twice so I want to delete the part no 123 and 234 that repeated second time and keeping the one data.

Please do help me. I'm complete new to MS Access.

Many thanks!

Part No	Carline	Data	Call
123	BMW	1	1
123	Astra	2	2
234	Opel	3	4
235	Volks	4	6
234	Santro	5	8

[rpeare]

do you have a primary key in your table to help you indicate which one is the 'first' record? Without a primary key or a date to indicate which one is the correct 'first' record you're going to have some difficulty determining which one to delete.

[puppy26]

Hi,
How can I set primary key to a Query or Do I need to set primary key in the table before creating query?

[rpeare]

Adding a primary key after the fact is not going to help you. The main point is you've showed a dataset with four data fields, how are you using those fields to determine which record should be deleted, is it the 'data' column? is it the 'call' column? is it alphabetical by car type?

[puppy26]

Adding a primary key after the fact is not going to help you. The main point is you've showed a dataset with four data fields, how are you using those fields to determine which record should be deleted, is it the 'data' column? is it the 'call' column? is it alphabetical by car type?

Well, to say exactly. I don't mind which data is being deleted in that column. I want to see only one part number out of those duplications. Hope I have answered your question.

Many thanks!

[rpeare]

After re-reading this post I have another question, are you trying to delete data from your TABLE or are you just trying to show an unduplicated list of PARTNO in a QUERY and keep the data in your table.

In other words, given your example let's assume that's data in a table called tblTest, do you want to physically remove the Astra and Santro records?

Or are you looking to create a query that will show

123
234
235