Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-08-2017, 09:36 AM   #1
Ridden
Newly Registered User
 
Join Date: Mar 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Ridden is on a distinguished road
Thumbs down Show latest record only for each parent item

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!

Ridden is offline   Reply With Quote
Old 03-08-2017, 09:52 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,289 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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?
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Reply

Tags
child and master , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Smart brain needed: Show latest record monthly from time stamp onwards Acid1979 Queries 1 08-27-2015 08:23 PM
show latest date record from matching multiple records zonexs123 Queries 1 05-10-2012 10:23 PM
Query for latest date for each equipment item Jen_RT Queries 6 06-23-2010 02:39 AM
Show only record with latest date on subform fibayne Queries 7 11-14-2008 06:45 AM
Only show record with latest date mark curtis Reports 1 10-27-2000 05:29 AM




All times are GMT -8. The time now is 07:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World