Delete Query maximum values

[MyGreatProgramme]

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 ?"

[Salad]

My**************@gmail.com wrote:

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 ?"

You could create a Totals query. Drop in the type (houses field) 2
times and the Values field. Click the Totals button. Group on type in
the first column and values and then Count on type. Call it Query1

Now create a second query. Use Query1. Drag the type and values and
count fields into columns. Group on Type and values and get the Max
count. Save as Query2.

Let's say the table in question is Table1. Now write some code.
Dim rst As DAO.Recordset
Dim strSQL As String
Set rst As Currentdb.openrecordset("QUery2",dbopensnapshot)
If rst.recordcount 0 then
rst.MoveFirst
do while not rst.Eof
strSQL = "Delete Table1.* From Table1 " & _
"where type = '" & rst!Type & "' And " & _
"value <" & rst!Values
Currentdb.Execute strSQL
rst.movenext
Loop
msgbox "Done
Endif
rst.close
set rst = Nothing

Make a copy of your table before running...just to be sure. BTW, I have
no idea what your field names are so you need to change them to reflect
the names in your table and the column names in Query2.

[John Winterbottom]

delete * from foo
where houses <>
(
select top 1 houses
from foo
group by houses
)
My**************@gmail.com wrote:

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 ?"