show only most recent comment record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have developed a filtering read-only continuous search form that allows me
to see only the criteria I select from criteria fields. Each Study has
comments, which are stored as individual records. Presently, each Study is
revealed as many times as there are comments. I have come to understand I am
unable to get around this multiplying issue (as I chose to use a continuous
form). Although it would be nice to see all the comments, I don't want to
see the same Study information 3, 5, or 10 times; I'd prefer to see the
comments nice and neat under a single instance of the Study.

I believe the only way around this problem is to show only the most recent
comment, but I'm just not seeing how to accomplish this. How do I only show
the most recent Comment (there is a date field) for each Study in a
continuous form. I am using a single query to pull data from up to 6 tables.
(some studies don't have any comments)

Any advice would be appreciated.
Bill
 
The simplest solution might be to leave the Comments table out of your
query, so you only get the record once, not once for every comment. Make
this query the source for your form, and use a subform to show the comments.

If that is not what you need, you could use a subquery to get the most
recent comment. Again, leave the comments table out of your query, and type
something like this into the Field row in query design:
CommentText: (SELECT TOP 1 CommentText FROM tblComment
WHERE tblComment.StudyID = tblStudy.StudyID
ORDER BY tblComment.CommentDate DESC, tblComment.CommentID)

If subqueries are new, see:
Subquery Basics
at:
http://allenbrowne.com/subquery-01.html
 
Allen: Thank you for your suggestions. I will give them a try.
Again, thanks for your time.
Bill
 
Back
Top