Results 1 to 4 of 4

Thread: Max in access??

  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Max in access??

    My SQl is a little rusty,

    I would like to select the max check number, but the ck # is concatinated with the Department ie : 123-RE or 456-MGMT

    I would like 2 results in my query result set the latest ck for RE dept and the latest check for MGMT dept :

    Last Check
    123-RE
    456-MGMT

    my current query has to be run 2 times and returns all recs by dept :

    SELECT TOP 10 Master.CheckNum, Master.CheckDate
    FROM Master
    WHERE (((Master.CheckNum) Like [Department]))
    ORDER BY Master.CheckDate DESC;

    also the top 10 isnt working

    Any Help much appreciated.


    M~

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Max in access??

    Assuming your layout is accurate then try this:

    Select Max(Left(CheckNum, 3)) as CheckNumber,
    Right(CheckNum, Len(CheckNum)-4) As Dept,
    Last(CheckDate) As Check_Date
    From Master
    Group By Left(CheckNum, 3)), Right(CheckNum, Len(CheckNum)-4)


    Should be something like that, I think......
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  3. #3
    Join Date
    Oct 2003
    Posts
    311

    top 1 ?

    SELECT Top 1 Left(Max(CheckNum),5) AS CheckNumber, Right(Max(CheckNum),4) AS Dept, Last(Master.CheckDate) AS Check_Date
    FROM Master
    GROUP BY Master.CheckNum, Master.CheckDate
    HAVING (((Master.CheckNum) Like "*MGMT*"))
    ORDER BY Master.CheckDate DESC;


    Doesnt return me 1 record,, how can i do that???


    M~

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: top 1 ?

    SELECT Top 1 Left(Max(CheckNum),5) AS CheckNumber, Right(Max(CheckNum),4) AS Dept, Last(Master.CheckDate) AS Check_Date
    FROM Master
    WHERE (((Master.CheckNum) Like "*MGMT*"))
    GROUP BY Master.CheckNum, Master.CheckDate
    ORDER BY Master.CheckDate DESC;
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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