delete query maximum values

[samotek]

I want to make a delete query that deletes all the items that are in the minorty. For example, in the field “ Houses ” there are 10 rows with value 1 , 3 rows with value 3 and 4 rows with value 6. In this case I must delete all values except the value 1
I suppose the code should use Dcount, Max or something like that but I cannot do it. Any help ?”

 

[LARiot]

Create a query, switch to SQL view and type (or if you don't need to learn this, just copy and paste) this over what you find there.

DELETE *
FROM tblTestMax
WHERE (((tblTestMax.Value) Not In (SELECT Max(tblTestMax.Value) AS MaxOfValue
FROM tblTestMax)));

-Neema

 

[samotek]

Thank you for your suggestion.I have tried your code but i received the errot "too few parameters".Also,in your suggestion i cannot see the field Houses, contained in the table TblTestMax.It is namely the field Houses where i want to delete everything except the maximm values.Would you help me ?

 

[LARiot]

Actually that code would've deleted too much data. I hope you're not using real data just yet. The best way I've found is to copy the table then run the code. Once it's perfect you can run it on the live data.

What's the unique identifier and what's the name of the table?

-Neema