Goodmorning everyone. Query Question (1 Viewer)

[pawson]

Nice to meet you all.

I need to elaborate a query that is capable of removing products with an older version if a larger one is detected. For example:

Microsoft Project Professional 2010

Microsoft Visual Studio Standard 2019

Microsoft Project Professional 2012

Microsoft Visual Studio Standard 2009

So, the results should be like:

Microsoft Project Professional 2012

Microsoft Visual Studio Standard 2019

The thing is remove the products that the version is older.

Thanks

 

[NauticalGent]

Do you want to uninstall an older program or remove/update a record in a table?

 

[Gasman]

I would have to do it as :

Create a query that is the MAX() of the name
Then a Select query that selects everything that is NOT in the MaxQuery (Just to make sure it works)
Then change the Select Query to a Delete Query. ?

 

[arnelgp]

is the 2009, 2012 part always on the Right side?

to Select in a Query:

select Field1 From Table As A Inner Join Table As B On
Left(A.Field1, InstrRev(A.Field1, " ")) = Left(B.Field1, InstrRev(B.Field1, " "))
Where (Right(A.Field1, Len(A.Field1)-InstrRev(A.Field1, " ")+1) >(Right(B.Field1, Len(B.Field1)-InstrRev(B.Field1, " ")+1)

 

[pawson]

Do you want to uninstall an older program or remove/update a record in a table?

I need to remove the registers with an old version

 

[arnelgp]

or simply:

select A.Field1 From [Tablename] As A Inner Join [Tablename] As B On
Left(A.Field1, InstrRev(A.Field1, " ")) = Left(B.Field1, InstrRev(B.Field1, " "))
Where Right(A.Field1, 4) > Right(B.Field1, 4)

 

[pawson]

is the 2009, 2012 part always on the Right side?

to Select in a Query:

select Field1 From Table As A Inner Join Table As B On
Left(A.Field1, InstrRev(A.Field1, " ")) = Left(B.Field1, InstrRev(B.Field1, " "))
Where (Right(A.Field1, Len(A.Field1)-InstrRev(A.Field1, " ")+1) >(Right(B.Field1, Len(B.Field1)-InstrRev(B.Field1, " ")+1)

Yes, always in a right position

 

[pawson]

or simply:

select A.Field1 From [Tablename] As A Inner Join [Tablename] As B On
Left(A.Field1, InstrRev(A.Field1, " ")) = Left(B.Field1, InstrRev(B.Field1, " "))
Where Right(A.Field1, 4) > Right(B.Field1, 4)

Thank you sir. But, which is the meaning A and B. are registers?
Kind regards

 

[arnelgp]

A and B are but Alias for the Same table (Tablename).
see the Query:

...[Tablename] As A ... [Tablename] As B

 

[arnelgp]

do delete Old Version
replace Table with the name of your table.
replace Field1 with the name of the field in your table.

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