Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    72

    Unanswered: Update min max date with join

    I have 2 tables CONT and CUMU, the two table have the same field named RAPP(txt property)

    In CONT have a filed named DATA (as date short)

    In CUMU have a field DAL (FROM in english) and a field AL (TO in english)

    With a join on RAPP, i need to extract the Min and Max Date from CONT.DATA and insert Min date value in DAL and Max date value in AL

    How to with a query?

    note; i work with ADO, Access databse and vb6 classic

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I don't know anything about Access, but this statement worked in my small test cases:

    Code:
    UPDATE
       M 
    SET
       M.FromDate = C.d1,
       M.ToDate   = C.d2
    FROM
       Cumu M
       JOIN (SELECT C1.RAPP, MIN(C1.ContDate) d1, MAX(C1.ContDate) d2
               FROM Cont C1
               GROUP BY C1.Rapp
             ) C on C.RAPP = M.RAPP
    HTH

  3. #3
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by LinksUp View Post
    I don't know anything about Access, but this statement worked in my small test cases:

    Code:
    UPDATE
       M 
    SET
       M.FromDate = C.d1,
       M.ToDate   = C.d2
    FROM
       Cumu M
       JOIN (SELECT C1.RAPP, MIN(C1.ContDate) d1, MAX(C1.ContDate) d2
               FROM Cont C1
               GROUP BY C1.Rapp
             ) C on C.RAPP = M.RAPP
    HTH
    Can you post the example with my original table name adn filed names,,, tkx

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    DATA = ContDate
    DAL = FromDate
    AL = ToDate

    Table Names did not change

  5. #5
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by LinksUp View Post
    DATA = ContDate
    DAL = FromDate
    AL = ToDate

    Table Names did not change
    ok for tath, but M wath is?

  6. #6
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by sal21 View Post
    ok for tath, but M wath is?
    tath = that
    wath = what

    Did you really study the query that was posted? The answers are in the query.

    M is an alias for cumin
    C and C1 are aliases for cont

Posting Permissions

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