Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: Maximum Function

    Hi

    I have a very simple database, consisting of a Table with 3 Fields: ID, Name & Date. Each field is populated with data

    eg:

    001 Joe Bloggs 01/12/03
    002 Joe Bloggs 01/03/02
    003 John Smith 03/02/02
    004 Joe Bloggs 06/06/04
    005 Joe Bloggs 07/07/04
    006 John Smith 12/12/03


    Using a simple Query I can use the MAX function to extract the Highest date for each Person, but what I am also trying to do is extract the SECOND Highest Date, for instance with Joe Bloggs the MAX function would extract 07/07/04, I would also like to extract 06/06/04.

    Is this possible?

    TIA

    Steve

  2. #2
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    Have a look at the TopValues property in Access Help. That should do what you want.

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by bmacr
    Have a look at the TopValues property in Access Help. That should do what you want.
    Hm. Is it possible to specify just two records (not percentage) - and PER CLIENT? Seems to need a join/subquery or something to get that right. How is the SQL syntax for this?

    Maybe using the "Max records" property (query property, not field property) with a subquery or linked query is an alternative? Well, that would have to run per client, though, perhaps not a viable alternative anyway, just thinking...

    D.

  4. #4
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    Originally posted by kedaniel
    Hm. Is it possible to specify just two records (not percentage) - and PER CLIENT? Seems to need a join/subquery or something to get that right. How is the SQL syntax for this?

    Maybe using the "Max records" property (query property, not field property) with a subquery or linked query is an alternative? Well, that would have to run per client, though, perhaps not a viable alternative anyway, just thinking...

    D.
    Well, I could be wrong, but TopValues will return an exact number OR a percentage. Couldn't you GroupBy name and use TopValues with a setting of 2? Haven't tried it, just a suggestion.

  5. #5
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    Ah. I see what you mean now. Not as simple as first thought.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about:
    PHP Code:
    SELECT a.nameMax(a.date), Max(b.date)
       
    FROM myTable AS a
       JOIN myTable 
    AS b
          ON 
    (b.name a.name)
       
    WHERE  b.date a.date
       GROUP BY a
    .name 
    I think that will get what you want.

    -PatP

Posting Permissions

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