Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Nov 2008
    Posts
    9

    Unanswered: Retrieve Max Date from Left Join Tables

    I have two tables, tblProjectStatus has a primary key ID# field and AFE# field while tblComments has an ID# and a Date field plus a comments field which isnt important at the moment. The relationship between the two tables is a 1 to many from tblProjectStatus to tblComments. The structure is summarized below:

    tblProjectStatus
    ID# AFE#
    1 1
    2 2
    3 3
    4 4

    tblComments
    ID# Date
    1 Jan1
    1 Jan10
    2 Feb1
    2 Feb5
    3 Mar1
    4 Mar1

    What I would like to do is pull every single AFE# from tblProjectStatus in a query, however I only want to retrieve the latest date for each AFE. For example, For AFE1 with ID#1, I only want to retrieve the Jan10 date, not the Jan1. The same goes for AFE2. I have put together a query below that pulls the data from the two tables however it gets every single date and not just the latest. Here is a sample of the query:

    Code:
    SELECT tblProjectStatus.AFE,tblComments.Date, tblComments.Comments
    FROM tblProjectStatus LEFT JOIN tblComments ON tblProjectStatus.ID = tblComments.ID;
    I know there is a way to do a subquery, however I cant seem to put together the write statement. I appreciate any help you guys can provide.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT tblProjectStatus.AFE
         , tblComments.Date
         , tblComments.Comments
      FROM tblProjectStatus 
    LEFT OUTER
      JOIN ( SELECT ID
                  , MAX(Date) AS MaxDate
               FROM tblComments 
             GROUP
                 BY ID ) AS daMaxDates
        ON daMaxDates.ID = tblProjectStatus.ID
    LEFT OUTER
      JOIN tblComments
        ON (
           tblComments.ID =  tblProjectStatus.ID
       AND tblComments.Date = daMaxDates.MaxDate 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    9
    Thank you for the reply. I gave it a shot but there seems to be some difference in the syntax that you are using and what I have done. This is for MSAccess 2003. Here is what I tried to put together but is says I have a syntax issue.


    Code:
    SELECT tblProjectStatus.AFE, tblProjectStatus.[Project Name], tblProjectStatus.Description, tblProjectStatus.[Project Manager], tblProjectStatus.[AFE Amount], tblComments.Date, tblComments.Comments
    FROM tblProjectStatus LEFT JOIN (SELECT tblComments.ID, MAX (tblComments.Date) AS MaxDate FROM tblComments GROUP BY ID) AS daMaxDates ON daMaxDates.ID = tblProjectStatus.ID LEFT JOIN tblComments ON (tblProjectStatus.ID = tblComments.ID AND tblComments.Date = daMaxDates.MaxDate);

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    arrgh, i forgot da stupid Access parentheses

    Code:
    SELECT tblProjectStatus.AFE
         , tblComments.Date
         , tblComments.Comments
      FROM (
           tblProjectStatus 
    LEFT OUTER
      JOIN ( SELECT ID
                  , MAX(Date) AS MaxDate
               FROM tblComments 
             GROUP
                 BY ID ) AS daMaxDates
        ON daMaxDates.ID = tblProjectStatus.ID
           )
    LEFT OUTER
      JOIN tblComments
        ON (
           tblComments.ID =  tblProjectStatus.ID
       AND tblComments.Date = daMaxDates.MaxDate 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2008
    Posts
    9
    Thank you so much for your help, I think we are almost there! I made the changes you suggested with the parenthesis but now it is giving me a different error. It says "Join expression not supported." and then highlights this portion of the SQL statement:

    "tblComments.ID = tblProjectStatus.ID AND tblComments.Date = daMaxDates.MaxDate"

    Any ideas? Again, I appreciate your help!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i know there's a way around this, but i thought it was putting parentheses around compound ON conditions

    Access is ~so~ obtuse...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    i know there's a way around this, but i thought it was putting parentheses around compound ON conditions
    No need for dem der parenthalisms Rudy

    Code:
    SELECT tblProjectStatus.AFE
         , tblComments.Date
         , tblComments.Comments
      FROM (
           tblProjectStatus 
    LEFT OUTER
      JOIN ( SELECT ID
                  , MAX(Date) AS MaxDate
               FROM tblComments 
             GROUP
                 BY ID ) AS daMaxDates
        ON daMaxDates.ID = tblProjectStatus.ID
           )
    LEFT OUTER
      JOIN tblComments
        ON 
           tblComments.ID =  tblProjectStatus.ID
       AND tblComments.Date = daMaxDates.MaxDate
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2008
    Posts
    9
    I tried removing the parenthilisms, so far this is what I got.

    Code:
    SELECT tblProjectStatus.AFE, tblProjectStatus.[Project Name], tblProjectStatus.Description, tblProjectStatus.[Project Manager], tblProjectStatus.[AFE Amount], tblComments.Date, tblComments.Comments
    FROM (tblProjectStatus LEFT OUTER JOIN (SELECT ID, MAX(Date) AS MaxDate FROM tblComments GROUP BY ID) AS daMaxDates ON daMaxDates.ID = tblProjectStatus.ID) LEFT OUTER JOIN tblComments ON tblComments.ID = tblProjectStatus.ID AND tblComments.Date = daMaxDates.MaxDate;
    Still no luck though...

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Exactly the same error text?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2008
    Posts
    9
    Yep, it gives me the same error "Join expression not supported" however it doesnt highlight any portion of the SQL string to indicate where the error is.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    [opens a box of popcorn]

    the next reply should be interesting ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2008
    Posts
    9
    =(

    Well whats ironic about this is I was trying to write this program to make life easier for our project managers, but its become trouble than its worth. It has been 3 years since I have dabbled in SQL for Access and I only remember enough to be dangerous. Its a simple problem yet it has me pulling my hair out.

    You guys have been great, thanks for the help.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by r937
    [opens a box of popcorn]

    the next reply should be interesting ...
    Agreed.

    I would have designed it in the Design View, then you don't have to worry about gettting your brackets wrong or getting join type syntax incorrect. Access is good like that

    But now that you have started the ball rolling in this direction...

    * yumm... popcorn *
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    I would have designed it in the Design View
    Cooo - you can build derived tables in design view?

    Code:
    SELECT tblProjectStatus.AFE
         , tblComments.Date
         , tblComments.Comments
      FROM (
           tblProjectStatus 
    LEFT OUTER
      JOIN ( SELECT ID
                  , MAX(Date) AS MaxDate
               FROM tblComments 
             GROUP
                 BY ID ) AS daMaxDates
        ON daMaxDates.ID = tblProjectStatus.ID
           )
    LEFT OUTER
      JOIN tblComments
        ON 
           tblComments.ID =  daMaxDates.ID
       AND tblComments.Date = daMaxDates.MaxDate
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Meh... I can build the parts of it in Design View and then use some copy and paste here'n'there... anything else and I usually find another way to do it

    * mmmmmmmmmmmmmmmmmmmmm popcorn *
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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