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 offline 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,858
    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