Delete query 1

[peljo]

With the help of the present forum i have built a query that counts the most occurences:
Visits Plates
John 3
Jim 3
Deuce 3
Liza 3
Lenny 1
Lorry 6

And the query is :

select top 1 Plates, count(*)
from daTable
group by Plates
order by count(*) desc

So i find that the value with the emost occurences is 3.How can i delete now all the other values, that is :
Lenny 1
Lorry 6

 

[jrbarnett]

Not sure i've got the exact join syntax correct for a delete query (its a bit more tricky than a select), but its something like:

delete from daTable left outer join YourQuery on daTable.Plates = YourQuery.Plates Where YourQuery.Plates Is Null

Basically you do an outer join from the query to the original table, so variables from the query that aren't in the table will have a null value in the datable.plates column - this is your criteria for deletion.

Please do this as a select first to check you will be deleting the correct data.

John

 

[peljo]

Thank you for your reply.I am afraid i cannot write down very clear what i want.Please do apologize me. I will try again.So my table consists of :
ID PLATES
1 2
2 4
3 6
4 2
5 2

With the help of your query i have found out that the greatest occurence has the number 2.I want to delete all the rows in the field, that have no number 2.In my
example these are the second and third row.where the numbers are 4 and 6
Could you help me writing the code with which to delete the 4th and the 6th rows? i.e. to delete all the rows which do not have the number of the greatest occurence.

 

[jrbarnett]

Create a new query in Access. Go into SQL view, then copy and paste the following code into it:

select datable.visits, datable.plates from daTable
where datable.plates <> (select datable.plates from YourQuery where yourquery.plates = datable.plates)

This is a slight rewrite in that it selects all records where the number of plates does not match the record in your query.

Run this query by clicking the red exclamation mark in the toolbar, it should show you the list of rows that will be deleted (ie your rows with 4 and 6 data.
If not, please say now and don't carry on.

If it works, change the code to:

delete datable from datable
where datable.plates <> (select datable.plates from YourQuery where YourQuery.Plates = datable.plates)

Run this - it should delete those two rows, but take a backup of your table beforehand if you will need to undo the work.

John