Restricting data displayed in a subreport to the last x entries

[Guest]

I need to restrict the data in a subreport to the last x entries - say 3 or
5.The data list is in some cases hundreds per person, but only the last few
are relevant. Although I can see how to restrict the data to the top x I
can't see how to restrict them when sorted by person. can anyone help please?

 

[Allen Browne]

1. Create a query to act as the source for this subreport (if you don't
already have one.)

2. In query design view, sort the query in Descending order on the field
that defines last (a date/time field?).

3. Set the Top Values property to 3 (in the Properties box.)

4. Save and close the query.

5. Open your subreport in design view.

6. Choose this query as the RecordSource of the report (Data tab of
Properties box.)

7. Use the Sorting And Grouping box (View menu) to specify the sort order
within the subreport.

 

[Guest]

Thanks for the info. I might be doing something wrong. All I seem to get is
the top 3 records from the entire underlying table, rather than the 3 or 5
records per name that I am trying to acheive so that the report gives me a
name followed by their last 3 or 5 entries in the subreport and the next name
and their 3 or 5 and so on.

I have the query which produces the entire list and can produce a full
listing by name in the sub-report. It's only when I try to restrict it that
things go wrong...

 

[Allen Browne]

Answers inline.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the info. I might be doing something wrong. All I seem to get
is
the top 3 records from the entire underlying table, rather than the 3 or 5
records per name that I am trying to acheive

If you open the query on its own, you will only see 3 records.

so that the report gives me a
name followed by their last 3 or 5 entries in the subreport and the next
name
and their 3 or 5 and so on.

Isn't that what you want?