show only most recent comment record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BillA - 03 Apr 2007 02:40 GMT

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

Reply to this Message

Allen Browne - 03 Apr 2007 03:11 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have developed a filtering read-only continuous search form that allows
>me
[quoted text clipped - 19 lines]
> Any advice would be appreciated.
> Bill

Reply to this Message

BillA - 03 Apr 2007 03:50 GMT

Allen: Thank you for your suggestions. I will give them a try.
Again, thanks for your time.
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
[quoted text clipped - 35 lines]
> > Any advice would be appreciated.
> > Bill

Reply to this Message