Show latest record only for each parent item (1 Viewer)

Ridden

New member
Joined
Mar 8, 2017
Messages
1
Hey all, long time reader and first time registered poster! Everyone here has been very helpful solving some interesting problems but I can't seem to find a solution for this one.

I am using Access as a very basic meeting manager/template generator for projects. I was able to create some great looking agenda items but I would like to include the last weeks notes in the agenda moving forward.

Each agenda item is on its own table and (AgendItems) and the updates are another table (AgendaItemUpdate) linked by the AgendaItemID. What I am trying to do is show the latest/last record only in the AgendaItemUpdates table based on that AgendaItemID. There are multiple AgendaItems on a meeting template and I would like the report to show the last record for each item.

When I created a subform, it showed all the updates for the AgendaItemUpdates and not the latest one. I also tried filtering by Top results and it seems to only apply for the most recent items leaving the others blank.

Any assistance on how to limit a Report Subform to only show the last record for each parent item?

Many thanks!
 

MarkK

Super Moderator
Joined
Mar 17, 2004
Messages
7,761
Hi there and welcome to the forums... :)

One thing you can do in a where clause is select for rows that have the same ID or the same date as the Max() or DMax() date or ID, like...
Code:
...
FROM ChildTable As ct INNER JOIN ParentTable As pt On ...
WHERE ct.ChildID = DMax("ChildID", "ChildTable", "ParentID = " & pt.ID)
See what's going on there? We are selecting a single child record of the current parent in the query, and having the Max() ID--so it's the latest record added as a child.
Would that work in your case? Something like that?
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom