Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42

    Unanswered: Access query maxeffdt < 1/1/2012

    Is there a quick way to modify this query below to grab the maxeffect date if the effdt is less than 1/1/2012? I want to grab the latest Model, ATTB, ATTBValue and EffDt that is dated less than 1/1/2012.

    SELECT t.Model
    , t.ATTB
    , t.ATTBValue
    , t.EffDt
    FROM ( SELECT Model
    , ATTB
    , MAX(EffDt) AS latest
    FROM daTable
    GROUP
    BY Model
    , ATTB ) AS m
    INNER
    JOIN daTable AS t
    ON t.Model = m.Model
    AND t.ATTB = m.ATTB
    AND t.EffDt = m.latest

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You could try this
    SELECT t.Model
    , t.ATTB
    , t.ATTBValue
    , t.EffDt
    FROM ( SELECT Model
    , ATTB
    , MAX(EffDt) AS latest
    FROM datable
    WHERE EffDt < #1/1/2012#
    GROUP
    BY Model
    , ATTB ) AS m
    INNER
    JOIN daTable AS t
    ON t.Model = m.Model
    AND t.ATTB = m.ATTB
    AND t.EffDt = m.latest

    I assume that Model, ATTB and EffDt is the composite Primary Key of daTable?

    HTH


    MTB

Posting Permissions

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