Query to delete registers.

[pawson]

Hi good morning

I need to remove registers with an old version (products). But depends on each device. Here an example:

DEVICE_NAME	PRODUCT_NAME
A11234	Microsoft Project Professional 2019
A11234	Microsoft Project Professional 2010
A11234	Microsoft Visual Studio Standard 2013
B56776	Microsoft Visio Professional 2018
B56776	Microsoft Visio Standard 2012
B56776	Microsoft Visio Standard 2016

The results should be like:

DEVICE_NAME	PRODUCT_NAME
A11234	Microsoft Project Professional 2019
A11234	Microsoft Visual Studio Standard 2013
B56776	Microsoft Visio Professional 2018
B56776	Microsoft Visio Standard 2016

One product for each device will be removed because the versions are older. So that's what i want.

Kind regards

 

[cheekybuddha]

Hi,

What is the table name?
What are all the field names in the table?
Which field is Primary Key?

Will this be a one-time job or something you will need to do regularly?

 

[arnelgp]

you paste to Query design (SQL View) and replace Table with your tablename.

DELETE Table.PRODUCT_NAME, *
FROM [Table]
WHERE (((Table.PRODUCT_NAME) In (SELECT A.PRODUCT_NAME 
FROM [Table] AS A Inner Join [Table] AS B
ON Left(A.PRODUCT_NAME, InstrRev(A.PRODUCT_NAME, " ")) = Left(B.PRODUCT_NAME, InstrRev(B.PRODUCT_NAME, " "))
WHERE (((Right([A].[PRODUCT_NAME],4))<Right([B].[PRODUCT_NAME],4)))
)));

 

[pawson]

Hi,

What is the table name?
What are all the field names in the table?
Which field is Primary Key?

Will this be a one-time job or something you will need to do regularly?

Hi,
Table name = Extraction
Field names = Product_Name & Device_Name
I don't have primary key.

I need to do it regularly but i know how to do it with macros. The thing is how to get the right query to delete the registers that are old version.

 

[cheekybuddha]

Do all your product names end in a year?

 

[Gasman]

Crossposted https://www.access-programmers.co.u...-everyone-query-question.311383/#post-1692409

pawson, please do not cross post without at least mentioning it. Normally the cross post is on another site, but for it to be on the same site.

arnelgp has given you the same informaton in both threads.