Home » questions » How can i get the value that is repeated the maximum number of times in a colum of a access databas

How can i get the value that is repeated the maximum number of times in a colum of a access databas

2
2006-08-16 06:36:32, Category: Programming & Design
the exact problem is that i want an id which is repeated maximum number of times in another field called fid in the same table. eg: id field ranges form 500-530 and out of that each of this 30 ids have a value for fid... as an example 505 can be the fid value for 500,530,525,505 509 can be the value for 510,502 and so on...in the worst case all 30 will have the the same value for fid. My problem is to count the fid which occurs maximum number of times..

Answers

  1. Master Mind

    On 2006-08-16 22:40:45


    i need a little detail and discussion , so please use my yahoo id for IM: edgelogix@yahoo.com

  2. Dennis Gove

    On 2006-08-16 18:52:42


    (this is generic SQL, might need to be modified for msAccess) "SELECT Count(fid) AS fidCount FROM [your table name] GROUP BY fid ORDER BY fidCount DESC TOP 1". This should get you what you want - the total number of matching fids will be stored in fidCount, and there will be a row for each unique fid.