in Access DB... I have to extract the itemcode / desc combination for every itemcode within the following table in which the desc has been utilized most often.

most often might imply that there is just one version (see added record for pear)

within the situation of itemcode 777, I'm going to decide later which description version to make use of. if you will find multiple records, each that contains just one version of the description, which will certainly create one more problem.

the initial question should most likely likewise incorporate coming back the very first row for itemcodes like 777 where all of the existing records to have an itemcode have a single, unique description (to ensure that the count would continually be 1). the very first row might not continually be the right version - however i will not have the ability to automate that phase anyway.

---------------------
itemcode  | desc
---------------------
123       | apple
123       | apple
123       | apple
123       | apple 2
123       | apple-2
001       | orange
001       | orange
001       | ORANGE 1
001       | orange-1
666       | pear
777       | bananananana
777       | banana

so - I am searching to finish track of the next:

---------------------
itemcode  | desc
---------------------
123       | apple
001       | orange
666       | pear
777       | bananananana

I believe I am close, however the following only will get the description within the database which seems most often and just returns one row.

SELECT itemcode, desc, count(desc)
from table
group by itemcode, desc
having count(desc) = 
(
 select max(ct) from 
 (
  select itemcode, desc, count(desc) as ct
  from table
  group by itemcode, desc
  )
);

returns:

---------------------
itemcode  | desc
---------------------
123       | apple

This could sort out a correlated sub-query:

SELECT 
  t.itemcode, t.desc, Count(t.desc) AS CountOfdesc
FROM 
  [table] AS t
GROUP BY 
  t.itemcode, t.desc
HAVING 
  Count(t.desc) IN (
    SELECT TOP 1 
      Count(i.desc)
    FROM 
      [table] AS i
    WHERE 
      i.itemcode = t.itemcode
    GROUP BY 
      i.itemcode, i.desc
    ORDER BY 
      Count(i.desc) DESC
  )
  AND t.desc = (
    SELECT TOP 1 
      i.desc
    FROM 
      [table] AS i
    WHERE 
      i.itemcode = t.itemcode
    GROUP BY 
      i.itemcode, i.desc
    ORDER BY 
      i.desc
  )
;

Returns (examined with Access 2003):


itemcode  desc          CountOfdesc

001       orange        2

123       apple         3

666       pear          1

777       blueberry        1

BTW you need to not really be calling a table "table" along with a column "desc". Individuals are reserved SQL key phrases, just prevent them to create your existence simpler.

Your query returns the MAX. Try to produce a rule that will match your needs.

That "which seems most oftenInch means what? appears>2? appears>3? appear>4?...

Fixed and examined. It really works as exspected - or better as designed, since it returns all rows when the greatest count to have an ItemCode seems several occasions.

SELECT ItemCode, ItemDescription, COUNT(ItemDescription) AS ItemCount
FROM Items I1
GROUP BY ItemCode, ItemDescription
HAVING COUNT(ItemDescription) = 
   (SELECT MAX(ItemCount)
      FROM (
         SELECT COUNT(ItemDescription) AS ItemCount
         FROM Items I2
         WHERE I2.ItemCode = I1.ItemCode
         GROUP BY ItemDescription
      ) I3
   )

UPDATE

Just simplifed the query a little.

UPDATE

Not able to ensure whether it works together with Access 2003. Tryed it, but Access keeps requesting I1.ItemCode.

Wait, why can't you simply order through the count and go ahead and take top however many you would like? Will I get me wrong your question? For instance ...

SELECT TOP N itemcode, desc, count(desc) AS [Count] FROM table
GROUP BY itemcode, desc
ORDER BY [Count]

Okay, what about this ...

;WITH dt AS
(
  SELECT 
  ROW_NUMBER() OVER 
  ( PARTITION BY itemcode ORDER BY COUNT([desc])DESC ) AS 'RowNumber',
  COUNT([desc]) AS [Count],
   itemcode,
   [desc] 
   FROM [table]
   GROUP BY itemcode, [desc]
)
SELECT * FROM dt WHERE dt.RowNumber = 1

Will that steer clear of the h8? :)

Ahhh Access! I quit!