Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    mode as aggregate function (access 2002 (xp))

    I need to use Mode (ie most common value) as part of an aggregate function in an access query.

    Data example:

    ID,class,month,result
    1,a,200202,200
    2,a,200203,250
    3,a,200204,200
    4,a,200205,190
    5,a,200206,200
    6,a,200207,950

    if I group by [class] (=a) I need a [result] value of 200. Obviously max, avg and min give incorrect values. Can I use the excel function? How do I pass the field ?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: mode as aggregate function (access 2002 (xp))

    I used an odd sort of approach but it worked.

    I built a table titled Test1 and included the fields you referenced.
    I then built one query with the following SQL:
    SELECT tblTest1.Class, tblTest1.Result, Count(tblTest1.ID) AS CountOfID
    FROM tblTest1
    GROUP BY tblTest1.Class, tblTest1.Result
    ORDER BY Count(tblTest1.ID) DESC;

    I then built a second query that refer3enced the Query1:
    SELECT Query1.Class, Query1.Result, Query1.CountOfID
    FROM Query1
    WHERE (((Query1.CountOfID)=DMax("[CountofID]","Query1","[Class]='" & [Class] & "'")));

    Give it a try and let me know if it works ok. Also, it will return multiple values if they appear the same number of times.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: mode as aggregate function (access 2002 (xp))

    The Excel function is not available in Access. Recommend take a look at MSKB Article 210569 which provides a way of doing this with a user-defined VBA function:

    ACC2000: How to Use Code to Derive the Statistical Mode

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •