Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Unanswered: Access Query with Max and Group by

    OK I need some help with this query. It seems simple but I just can't get it in to SQL form. I have 3 tables [Request], [Comment], and [Administrator]. What I'm trying to do is show the last admin who made a comment on the request.

    [Request]
    *REQ_ID
    REQ_DT
    REQ_CMNT_TX

    [Comment]
    CMNT_ID
    *ADMIN_ID
    *REQ_ID
    CMNT_DT
    CMNT_TX

    [ADMINISTRATOR]
    *ADMIN_ID
    ADMIN_NM_TX

    OUTPUT SHOULD SHOW
    REQ_ID CMNT_ID ADMIN_ID
    1 123 4
    3 187 2
    etc...


    What I'm getting now is
    SQL Statement:
    SELECT REQUEST.REQ_ID, Max(COMENT.CMNT_ID) AS MaxOfCMNT_ID, COMENT.ADMIN_ID FROM (REQUEST INNER JOIN COMENT ON REQUEST.REQ_ID = COMENT.REQ_ID) INNER JOIN ADMINISTRATOR ON COMENT.ADMIN_ID = ADMINISTRATOR.ADMIN_ID GROUP BY REQUEST.REQ_ID, COMENT.ADMIN_ID ORDER BY REQUEST.REQ_ID, Max(COMENT.CMNT_ID) DESC;

    REQ_ID CMNT_ID ADMIN_ID
    1 1 3
    ...
    1 123 4
    3 119 7
    3 187 2
    etc...

    Any help would be appreciated, thanks.

    Rob

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could probably do it with a subquery in your where clause, something like this:

    SELECT REQUEST.REQ_ID, COMMENT.COMMENT_ID, COMMENT.ADMIN_ID
    FROM (REQUEST INNER JOIN COMMENT ON REQUEST.REQUEST_ID = COMMENT.REQUEST_ID) INNER JOIN ADMINISTRATOR ON ADMINISTRATOR.ADMIN_ID = COMMENT_ID
    WHERE COMMENT.COMMENT_ID IN (SELECT MAX(COMMENT_ID) FROM COMMENT C WHERE C.REQUEST_ID = REQUEST.REQUEST_ID)

    You could use DMax() instead of the query, but lots of people take issue with domain functions... your choice.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question

    Quote Originally Posted by Teddy
    You could probably do it with a subquery in your where clause, something like this:

    SELECT REQUEST.REQ_ID, COMMENT.COMMENT_ID, COMMENT.ADMIN_ID
    FROM (REQUEST INNER JOIN COMMENT ON REQUEST.REQUEST_ID = COMMENT.REQUEST_ID) INNER JOIN ADMINISTRATOR ON ADMINISTRATOR.ADMIN_ID = COMMENT_ID
    WHERE COMMENT.COMMENT_ID IN (SELECT MAX(COMMENT_ID) FROM COMMENT C WHERE C.REQUEST_ID = REQUEST.REQUEST_ID)

    You could use DMax() instead of the query, but lots of people take issue with domain functions... your choice.
    Hey Teddy,

    Again my curiosity gets me again, but in his SQL doesn't the TableName of COMENT have to be spelled the same as the actual table itself? The Table is CoMMent, (2 M's), in his SQL it is CoMents...only one (M)? Again, just checking.

    BUD

Posting Permissions

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