Counting the greatest number

[peljo]

I want to build a query with which to find the greatest number of figures in the field.I have a field called Plates where single numbers are entered.For example

Visits Plates
John 3
Jim 3
Deuce 3
Liza 3
Lenny 1
Lorry 6

Obvisouly in the above example we can count that the number 3 is the biggest number.
How can i express that in a query ?

 

[ajetrumpet]

I want to build a query with which to find the greatest number of figures in the field.I have a field called Plates where single numbers are entered.For example

Visits Plates
John 3
Jim 3
Deuce 3
Liza 3
Lenny 1
Lorry 6

Obvisouly in the above example we can count that the number 3 is the biggest number

You say biggest number...are you querying for the biggest number (MAX value), or for the most occurances of a value in the field??

You also say in the first line of your post...."greatest number of figures"...this, to me, means most occurances. Can you specify please??

 

[Brianwarnock]

Assuming that you mean most occurences, which by your example youd then

SELECT TOP 1 Table1.fn, Count(Table1.fn) AS CountOffn
FROM Table1
GROUP BY Table1.fn
ORDER BY Count(Table1.fn) DESC;

Where Table1 is your tablename and fn is your fieldname

Brian

 

[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 the query suggested by Brianrock 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.

 

[Brianwarnock]

First I suggest you read ACCESS help, type Delete query in the answer wizard and read about creating delete queries.

Then you can simply use the knowledge gained from running the previous select query to put <>2 or whatever the result into the delete query criteria.

To avoid manual imtervention, if this is a regular event, then a second query can be created to run the first and delete the required records, but this will not let you do a visual check as help recommends.
Using my previous example the second query would be.


DELETE DISTINCTROW table1.*, Query1.fn
FROM table1 LEFT JOIN Query1 ON table1.fn = Query1.fn
WHERE (((Query1.fn) Is Null));



Brian

 

[peljo]

First I suggest you read ACCESS help, type Delete query in the answer wizard and read about creating delete queries.

Then you can simply use the knowledge gained from running the previous select query to put <>2 or whatever the result into the delete query criteria.

To avoid manual imtervention, if this is a regular event, then a second query can be created to run the first and delete the required records, but this will not let you do a visual check as help recommends.
Using my previous example the second query would be.


DELETE DISTINCTROW table1.*, Query1.fn
FROM table1 LEFT JOIN Query1 ON table1.fn = Query1.fn
WHERE (((Query1.fn) Is Null));



Brian

Please do not be angry with me I am confused and do not know how to handle it.On the basis of the first query I need to delete from table1 all the rows which do not correspond to the figure received with the query.In my case this is the figure 2 since it occurs three times, and I must delete the second and third row,having figures 3 and 4. This will mean that query.fn <> table1.fn.
However I get the message could not delete from specified tables.
Am I wrong with my consierations and how can I do it ?



DELETE DISTINCTROW Table1.*, Query1.FN
FROM Table1 LEFT JOIN Query1 ON Table1.Fn = Query1.FN
WHERE (((Query1.FN)<>[Table1].[FN]));

 

[ajetrumpet]

On the basis of the first query I need to delete from table1 all the rows which do not correspond to the figure received with the query.

In my case this is the figure 2 since it occurs three times, and I must delete the second and third row,having figures 3 and 4.

However I get the message could not delete from specified tables.

You could be getting the message for any number of reasons. If you have figured out that you need to keep records that specify 2 plates, then just run a simple delete query, you don't need to specify the DISTINCTROWS, unless there is another criterion other than the "plate field <> 2".

DELETE [type all fields that apply here]
FROM [Table1]
WHERE [Table1].[Plates] <> 2;

If you're going to repeat this action in the future (deleting all rows except those with the greatest "plate number occurances"), then I would store two short queries and always run your delete query....

1) Per Brian's post...

SELECT TOP 1 Table1.fn, Count(Table1.fn) AS CountOffn
FROM Table1
GROUP BY Table1.fn
ORDER BY Count(Table1.fn) DESC;

Where Table1 is your tablename and fn is your fieldname

2) Delete....

DELETE [fields]
FROM [Table1]
WHERE [counted field of Query1] NOT IN (SELECT [Query1].[counted field] FROM Query1);

 

[peljo]

You could be getting the message for any number of reasons. If you have figured out that you need to keep records that specify 2 plates, then just run a simple delete query, you don't need to specify the DISTINCTROWS, unless there is another criterion other than the "plate field <> 2".

DELETE [type all fields that apply here]
FROM [Table1]
WHERE [Table1].[Plates] <> 2;

If you're going to repeat this action in the future (deleting all rows except those with the greatest "plate number occurances"), then I would store two short queries and always run your delete query....

1) Per Brian's post...

SELECT TOP 1 Table1.fn, Count(Table1.fn) AS CountOffn
FROM Table1
GROUP BY Table1.fn
ORDER BY Count(Table1.fn) DESC;

Where Table1 is your tablename and fn is your fieldname

2) Delete....

DELETE [fields]
FROM [Table1]
WHERE [counted field of Query1] NOT IN (SELECT [Query1].[counted field] FROM Query1);

Thank you to you all ! Now my code works awsome and i am very grateful to you indeed !!!!!!!!!!!!!

 

[ajetrumpet]

Good luck to ya...