query

[lee]

no no1
56 2677500780173
54 2677500780173
54 2677500780173
56 2677500780173
55 2677500819485
53 2677500819485
52 2677500819485
54 2677500820595
56 2677500820595
54 2677500820595


For every unique no1 i want to see how many times every no is
repeated- and which has maximium no of repeatences- i want to see the
below output

54 is repeated twice in two sets of no1;
56 is repeated in one set of no1;

 

[John Spencer]

You can do this with a series of nested queries.

SELECT No1, [No], Count(No) as TheCount
FROM SomeTable
GROUP BY No1, [No]

SELECT [No], Max(TheCount) as BigCount
FROM FirstQuery
GROUP BY [No]

SELECT FirstQuery.[No], FirstQuery.TheCount, Count(FirstQuery.No) as FreqCount
FROM FirstQuery INNER JOIN SecondQuery
ON FirstQuery.[No] = SecondQuery.[No]
AND FirstQuery.TheCount = SecondQuery.BigCount
GROUP BY FirstQuery.[No], FirstQuery.TheCount

Depending on your field and table names, you might be able to do this in one
complex query using sub-queries. Your names should consist of only letters,
numbers, and the underscore character and should not include any reserved
words. No is a reserved word and should be surrounded by square brackets ([No]).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County