Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filter largest no. on groups! (Access 2000 >)

    Hi all,

    Could someone assist me with some instructions on how to filter a query in access that will extract the largest number for each person. I have been asked for a solution by a client but I am working remotely (currently) and do not have access to MS Access to test your reply. Please be so kind as to post sufficient detailed steps that I can forward to the client to do the task. (Much appreciated!)
    The client has forwarded me an excel example of what the query needs to do. The excel attachment will make it clear what to do.

    PS: I suspect the query will be a summary query (using the total button to group) and then setting up an expression using the max function. (Am I close???). Please remember to post a detailed step by step. I will copy that and forward it to the client. Thanx again...(BTW; I'm not trying to be lazy...I cannot test it out and confirm its accuracy...so I need my pals to jump in here!) I'll make it up to you by posting some puzzles in the puzzle forum to amuse you when the boards go quiet... <img src=/S/wink.gif border=0 alt=wink width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter largest no. on groups! (Access 2000 >)

    Your idea is correct:

    Create a query based on the table.
    Add the "Name" and "Number" fields to the query grid.
    Select View | Totals (or click the Totals button on the toolbar)
    By default, the Total option for both fields is set to Group By.
    Leave the Total option for "Name" as it is.
    Set the Total option for "Number" to Max (this can be selected from a dropdown list).
    If desired, provide an alias for the "Number" field:

    Maximum Number: Number

    If no alias is provided, the column will be named MaxOfNumber in the query result.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter largest no. on groups! (Access 2000 >)

    Thx for the speedy reply Hans... I will inform the client of this approach!

    PS: Just for interest sake, how would a person go about this in Excel using filters? Would you need to filter using an expression too? I suddenly remembered that you could use sub-totals as there is a max function there, and then collapse the details and copy/paste visible cells only. But is it possible with auto/adv. filter???

    Cheers
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter largest no. on groups! (Access 2000 >)

    I would use a pivot table in Excel.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter largest no. on groups! (Access 2000 >)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>...How true!
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter largest no. on groups! (Access 2000 >)

    Hi Hans,

    This user came back to me with this request:
    "The store man discovered a problem with the query.

    We want to display other fields in the query, such as the loan date, username, department, telephone number etc. the sum, count, last, first is causing the data not to display and sum data is displayed incorrectly. "

    What do I do if I want to display other fields?

    I know Summary queries cannot show too much detail...do you still believe Pivot Tables will be a better option? I was thinking...Would it also be possible for the user to save the summary query, and then set up a second query that will pull the necessary details such as the loan date, username, department, telephone number etc and merge it with the values calculated in the summary.
    Tx
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter largest no. on groups! (Access 2000 >)

    Create a new query, based on the original table and the "max number" query from this thread. Join them on the name field (or preferably on a person ID field). Add the fields from the table, plus the "maximumn number" field from the query.

Posting Permissions

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