Delete query maximum values

  • Thread starter Thread starter samotek via AccessMonster.com
  • Start date Start date
S

samotek via AccessMonster.com

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 ?â€
 
samotek said:
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 ?â€
Click to expand...
Thank you for the reply. With your code i delete all the figures in the field
Houses, while i need to delete onlt those items that are in minorty.Would you
help ?
 
How about

DELETE * FROM Table1
WHERE Table1.Houses <>
(SELECT TOP 1 Houses FROM Table1 GROUP BY Houses ORDER BY
Count(Houses) desc)

Note that if there is a tie for first place then only one of those
tying will be kept; this may or may not be what you want but it is what
you asked for.

The following version will leave all that tie for first place
(inelegant query - may be possible to do better):

DELETE * FROM Table1
WHERE Table1.Houses IN
(SELECT Houses FROM Table1 GROUP BY Houses HAVING
Count(Houses)<(SELECT MAX(CountHouses) FROM (SELECT Count(Houses) As
CountHouses FROM Table1 GROUP BY Houses)))



Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Back
Top