Results 1 to 8 of 8
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    How to get Max date

    this is query and result

    SELECT t1.VEND_NUM, t1.CITY_NM, t1.TERR_CD, t1.CNTRY_CD, t1.EFF_DT
    FROM VEND_ADDR t1 where vend_num='0003012370';






    VEND_NUM CITY_NM TERR_CD CNTRY_CD EFF_DT
    1 0003012370 Shelburne ON CA 11/10/2016
    2 0003012370 Toronto ON CA 3/7/2013
    3 0003012370 Shelburne ON CA 6/17/2016
    4 0003012370 Toronto ON CA 5/31/2013
    5 0003012370 MelancthonON CA 4/17/2018
    6 0003012370 MelancthonON CA 11/6/2018


    I want to pick up vendor which has most effective date. I want max (eff_date). Kindly help me this
    i want this result only

    VEND_NUM CITY_NM TERR_CD CNTRY_CD EFF_DT
    6 0003012370 MelancthonON CA 11/6/2018

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try:
    Code:
    SELECT TOP 1 t1.VEND_NUM, t1.CITY_NM, t1.TERR_CD, t1.CNTRY_CD, t1.EFF_DT
    FROM VEND_ADDR t1 
    WHERE vend_num='0003012370'
    ORDER BY t1.EFF_DT DESC;
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by Bob Fitz View Post
    Try:
    Code:
    SELECT TOP 1 t1.VEND_NUM, t1.CITY_NM, t1.TERR_CD, t1.CNTRY_CD, t1.EFF_DT
    FROM VEND_ADDR t1 
    WHERE vend_num='0003012370'
    ORDER BY t1.EFF_DT DESC;
    Thanks it worked but only worked for one vendor 0003012370, there are 10k vendors , I want to group by vend_num but want most recent eff_dt. I hope you understand.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use an aggregate query to find the latest for each vendor

    Something like

    SELECT T1.*
    FROM VEND_ADDR t1 INNER JOIN (SELECT VEND_NUM Max(Eff_dt) AS mDate FROM VEND_ADDR GROUP BY VEND_NUM) T2 ON T1.VEND_NUM=T2.VEND_NUM AND T1.EFF_DT=T2.mDate
    WHERE T1.vend_num='0003012370'

    Note - excluding the WHERE will make your query slow - always try to limit the number of records returned unless you actually want to see 10k records

    Edit: code modified to provide alias for max date

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Research Group By and Max(CD EFF_DT)

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Seems you data is missing a field name.
    I added some records for testing:
    Code:
    VEND_NUM    CITY_NM    TERR_CD    PROV_CD    CNTRY_CD    EFF_DT
    1    0003012370    Shelburne    ON    CA    11-Oct-16
    2    0003012370    Toronto    ON    CA    03-Jul-13
    3    0003012370    Shelburne    ON    CA    17-Jun-16
    4    0003012370    Toronto     ON    CA    31-May-13
    5    0003012370    Melancthon    ON    CA    17-Apr-18
    6    0003012370    Melancthon    ON    CA    11-Jun-18
    7    0003012373    GHlancthon    ON    CA    11-Jun-16
    8    0003012373    GHlancth     NB    CA    06-Nov-18
    9    0003012373    GHlancthon    ON    CA    11-Sep-18
    Try
    Code:
    SELECT Acanvend.city_nm, Max(Acanvend.EFF_DT) AS maxEffDate
    FROM Acanvend
    GROUP BY Acanvend.city_nm;
    city_nm maxEffDate
    0003012370 11-Jun-18
    0003012373 06-Nov-18

  7. #7
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    as this is posted in the SQl server section, you can use T-SQL windowing, something like:

    SELECT t1.VEND_NUM, t1.CITY_NM, t1.TERR_CD, t1.CNTRY_CD
    ,
    max(t1.EFF_DT) OVER(PARTITION BY t1.VEND_NUM) as EffectiveDate
    FROM VEND_ADDR t1 ';

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    On fly (must be run on SQL Server, so probably you create a view):
    Code:
       ;WITH LastDate AS
          (SELECT add0.VEND_NUM, MAX(add0.EFF_DT) AS MaxDate FROM dbo.VEND_ADDR add0 GROUP BY add0.VEND_NUM)
       SELECT ld.VEND_NUM, add.CITY_NM, add.TER_CD, add.CNTRY_CD, ld.EFF_DT
       FROM LastDate ld LEFT OUTER JOIN dbo.VEND_ADDR adr ON adr.VEND_NUM = ld.VEND_NUM AND adr.EFF_DT = ld.EFF_DT

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Convert date from short date to general date
    By cebrower in forum Access
    Replies: 4
    Last Post: 04-24-2019, 08:08 AM
  2. Replies: 4
    Last Post: 11-20-2018, 11:57 PM
  3. Replies: 20
    Last Post: 04-18-2017, 03:19 PM
  4. Replies: 1
    Last Post: 04-29-2016, 04:03 AM
  5. Replies: 3
    Last Post: 08-21-2012, 03:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums