Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Query Help Needed

    Hi All, I have been using MS Access off and on for years, but I have never run into a query that is this difficult.

    I am trying to pull only records that are the latest date and have a "D" in the Request Type field:

    Here is a link to a picture of the Excel view of the data example: http://farm3.static.flickr.com/2769/...780bd9e6_o.png
    In the Excel View the needed query would only return the two records... If Possible?

    Any help is much appreciated.

    Bill

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.alias
         , t.[Display Name]
         , t.[Account Type]
         , t.and_so_on
      FROM ( SELECT alias
                  , MAX([Request Date]) AS maxdate
               FROM daTable
             GROUP
                 BY alias ) AS m
    INNER
      JOIN daTable AS t
        ON t.alias = m.alias
       AND t.[Request Date] = m.maxdate
       AND t.[Request Type] = 'D'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    Quote Originally Posted by r937 View Post
    Code:
    SELECT t.alias
         , t.[Display Name]
         , t.[Account Type]
         , t.and_so_on
      FROM ( SELECT alias
                  , MAX([Request Date]) AS maxdate
               FROM daTable
             GROUP
                 BY alias ) AS m
    INNER
      JOIN daTable AS t
        ON t.alias = m.alias
       AND t.[Request Date] = m.maxdate
       AND t.[Request Type] = 'D'
    On MS Access I created a new Query, added the Accounts_tbl fields, then switched it to SQL view and added the following:

    Code:
    SELECT Accounts_tbl.Alias
    , Accounts_tbl.[Display Name]
    , Accounts_tbl.[Account Type]
    , Accounts_tbl.[Request Type]
    , Accounts_tbl.[eDiscovery Request No]
    , Accounts_tbl.[eDiscovery Technician]
    , Accounts_tbl.Requestor
    , Accounts_tbl.[Request Date]
    , Accounts_tbl.comments
    
    FROM ( SELECT alias
    , MAX ([Request Date]) AS maxdate
    FROM daTable
    GROUP
    BY alias ) AS  m
    
    INNER
    JOIN daTable AS Accounts_tbl
    ON Accounts_tbl.Alias = m.alias
    AND Accounts_tbl.[Request Date] = m.maxdate
    AND Accounts_tbl.[Request Type] = 'D'
    When I try to save it it replies: "Join Expression not supported" Any ideas where to go from here? Or did I type some of this wrongly?

    Bill

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    [sigh] ms access can be petulant at times

    add these parens...

    Code:
    INNER
      JOIN daTable AS Accounts_tbl
        ON (
           Accounts_tbl.Alias = m.alias
       AND Accounts_tbl.[Request Date] = m.maxdate
       AND Accounts_tbl.[Request Type] = 'D'
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    THANKYOU VERY MUCH! I believe that has got it working!

Posting Permissions

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