Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    SQL Query Problem

    An Access Database table contains basically two fields: a Date (RDate) and a Value (RCount). A sample table could look as follows:
    RDate RCount
    2/1/1999 1256
    5/6/1999 1954
    7/9/1999 2008
    4/12/1999 1985
    ...
    3/1/2011 2389
    11/2/2011 2457
    3/3/2011 2572

    Is it possible to formulate a (SQL) query that for EACH year lists the Date where the Maximum Count occurs, together with that Count?

    For the above sample, the result should look as
    RDate RCount
    7/9/1999 2008
    ...
    3/3/2011 2572
    (Dates and Values are shown in Contintal Format)

    As an added complication, If during a given year there are multiple (same) maximimums, I would like to show them all.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Create a query that selects both both fields then change the Date field to RdateYear: Year([Rdate]).
    Then change the query into a Totals query.
    (Exactly what to click depends on the version of Access, but look for for this TotalsQuery.gif. In 2007 and 2010 it is on the Design Tab of the ribbon.
    This adds an extra line to the Query Grid with Group By already entered. For RCount change this to Max. This then finds the Max value for each Year.
    Save this query.

    Now create a second query that joins this first query back to the table. Join RCount from the table to the Max field from the query, then set criteria that the Year([Date]) from the table equals the Year from the query.

    In the attached sample I have just called them query1 and query2.
    Attached Files Attached Files
    Last edited by johnhutchison; 2011-03-18 at 07:18.
    Regards
    John



  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts

    Yes, it is

    This should do it:

    Code:
    SELECT L.MYear, T.RDate, T.RCount FROM
    ( SELECT DISTINCT Year(RDate) as MYear FROM Test) As L, Test T
    WHERE T.RCount = (SELECT Max(RCount) FROM Test WHERE Year(RDate)=L.MYear)
    ORDER By MYear, RDate
    You didn't provide the name for your table, so I just named it Test. Replace all occurrences of Test by your table name and you should be done.

    You can simple create a new query in Access, don't add any tables, choose SQL View and then copy and paste the code I posted and save the query.




    P.S.: While I was doing it, John posted another solution. I didn't see that until I posted. You should be good with any of them.
    Last edited by ruirib; 2011-03-18 at 07:01. Reason: typo

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Just one little caveat to the query solutions the other fellows provided. This may occur if the max # in a year happened on 2 or more dates within the year. It may or may not be a problem for you if you get 2 records returned for 1 year.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by MarkLiquorman View Post
    Just one little caveat to the query solutions the other fellows provided. This may occur if the max # in a year happened on 2 or more dates within the year. It may or may not be a problem for you if you get 2 records returned for 1 year.
    Actually having more than one record, when it occurred, was a request, or the query would have been different, to avoid it.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Thanks guys: once one sees the solutions, it becomes easier than I thought for possible!

Posting Permissions

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