Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    View the latest comment (2k)

    I have a query which links a main data table to a comments table. This is a one-to-many link i.e. there can be many comments per record on the main form.

    How can I only show each record from the main table with ONLY the latest comment?

  2. #2
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: View the latest comment (2k)

    Hi. Could you not have a field in the comments table that hols the date and time the comment was added... Then look for the comment with the most recent date....

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: View the latest comment (2k)

    I do have a date/time of when the comment was added but when I try to show only the last or max of CommentDate I get all the comments..

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: View the latest comment (2k)

    You need two queries:

    1. A Totals query based on the comments table that groups on the ID from the main table and takes the Max of the date field. This query returns the most recent date for each ID from the main table.
    2. A query based on the Totals query and the comments table, joined on the date vs maxdate field. Add the ID and the comment field to the query grid. This query returns the comment with the most recent date for each ID from the main table.

Posting Permissions

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