Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    413

    Unanswered: Show how many letters for each girl

    So, my primary key is GirlID and the name of the girl is Girl in the table. I also have a field named Letters (in the table) and I use this code to sum up the number of girls who have been written to,,, =Sum(IIf([Letters],1,0))
    But I need a way to show the girl with the most letters written to her. A top value is what I need.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Do you mean to use SUM, or would you be better of using COUNT?

    To find the highest value use an ORDER BY clause, the default sort order is ASCending but this can be overridden by using the DESCending verb.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Posts
    413
    Count I guess since I want the greatest number of Letters for each Girl. In other words, show the girl with the most letters as first in the recordset. This code works but it is from a crosstab query and I cant make it work for my form since I have pictures on it.SELECT tbl_main.GirlID, Max(tbl_main.Letters) AS MaxOfLetters, tbl_main.Girl, First(tbl_main.Pictures) AS FirstOfPictures
    FROM tbl_main
    GROUP BY tbl_main.GirlID, tbl_main.Girl
    HAVING (((Max(tbl_main.Letters))>0))
    ORDER BY Max(tbl_main.Letters) DESC , tbl_main.Girl;

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what do you think max(<columnname>) returns?
    how does using max(<columnname>) help you with your problem?

    where are you using this value?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Posts
    413
    In the vba code I posted it solves the problem showing all of the girls with criteria I specified and shows them with the greatest number of letters by girl which is what I want.Surely their is a way to count the greatest number of Letters on my form and filter as such or modify the query or something. I just dont know how.Max column name only is there because the stupid crosstab query makes you do a group by.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So if you don't know what MAX returns, what do you think SUM does, or COUNT does

    if in doubt do a google on those terms or MS Access Aggregate functions
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2011
    Posts
    413
    Thanks for your help. I do know what they return. Forget about that code, it was posted to help explain what I was after, their is no way to use it I believe. It has thrown you off course, sorry !

Posting Permissions

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