To see if you can get it working, try simplifying it to:
SELECT TOP 5 tbl_Comments.CommentID,
tbl_Comments.ProjID,
tbl_Comments.Comments
FROM tbl_Comments
ORDER BY tbl_Comments.Date DESC,
tbl_Comments.CommentID;
The only other thing I can think of is that the
LinkMasterFields/LinkChildFields could be wrong, e.g. Proj1D instead for
ProjID, or a space or something.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
sam at UM said:
SELECT TOP 5 TBLPROJECTREPORTS1.[Work Request], tbl_Comments.Date,
tbl_Comments.Comments, tbl_Comments.CommentID, tbl_Comments.ProjID
FROM TBLPROJECTREPORTS1 LEFT JOIN tbl_Comments ON
TBLPROJECTREPORTS1.ProjID
= tbl_Comments.ProjID
ORDER BY tbl_Comments.Date DESC;
Allen Browne said:
That sounds perfect.
Presumably you do have the ProjID field in the subreport's query, so the
subreport can be limited on that field? Post the SQL for the subreport's
query (by switching from query design to SQL View.)
two tables: "TBLPROJECTS" and "tbl_comments". tabls are linked by
"ProjID"
in
both the main table (where it is the primary key) and the comments
table.
i
have them linked in relationships with that ProjID. this is also the
field
that i have them linked in the master report and child/subreport.
then i have a query with just the comments table, where i did the top 5
values thing. should that query also have the main table in it?
:
How are your tables connected? Are the comments actually connected to
the
projects in the main report, i.e. is there are ProjectID foreign key
field
in your Comments table?
How are the reports connected? What table(s) does the main report get
its
records from? What table(s) does the subreport get its records from?
What
is
in the LinkMasterFields and LinkChildFields of the subreport control?
it is correclty showing the matching records in the subreport, but
only
5
comments total are being displayed throughout the 60+ records in the
main
report.
:
The query itself will show just the top 5 records.
If the subreport is set up correctly, using the
LinkMasterFields/LinkChildFields so that it shows the correct
records
in
the
subreport, it will show the top 5 *matching* records in the
subreport.
i tried this - and it is only showing the top 5 of all of the
comments,
not
five for each entry in my main report. any other suggestions? i
might
have
done something wrong but i can't figure out what to look for.
thanks.
:
If you do not have a query for the subreport, create one.
Sort the query Descending by the date field, so the most recent
comments
appear first.
Open the Properties box (View menu, in query design), and set
the
query's
Top Values property to 5. This restricts it to the 5 most recent
comments.
Save the query. Close.
On the Report's tab of the database window, select the subreport
and
click
Design. Make sure its RecordSource property is the query you
just
created/modified. In the Sorting And Grouping dialog (view
menu),
define
how
you want the subreport records ordered.
Save. Close. Test. You should now see a maximum of 5 comments in
the
subreport, for each entry in the main report.
message
i am creating a project report that has a one-to-many
relationship
for
comments on each project. i have a sub-report for the comments
under
each
project and i want the sub-report to only show the last 5
comments
for
each
project. how can i do this? i know how to limit it based on
date,
but
that
is
excluding some comments that i want to have included. thanks
in
advance.
