Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Select the most recent subrecord (2000)

    I have a building table linked to an inspection table. Buildings are inspected at regular intervals. I need a report that lists all buildings as well as information about the most recent inspection for each. Is there any way, with a query, to get only the most recent inspection record and ignore previous records?
    Thanks

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Select the most recent subrecord (2000)

    Build a query on inspections, and make it into a grouping query by clicking the totals button on the toolbar. Group by buildingID (or whatever is the identifier of the building) and under [inspectiondate] choose Max for the Total line of the query grid. This query will list each building that has been inspected, and its most recent inspection date.

    Then Join this query to the buildings table to make a new query as the Record source for the Report you want.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Select the most recent subrecord (2000)

    I KNEW there had to be a simple way to do that, but I couldn't see it until you pointed it out. Thank you very much.

Posting Permissions

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