Display only the last 10 records in a subform

[Siobhan Perricone]

I have a form that shows a volunteer's name and details, then the
subform links to the time spent working. It shows the date, amount of
time, and comments.

I want my form to display only the last 10 entries for the given
employee, not all of them. I'd also like to be able to add record
using the same form, but I can make a separate entry form if the
recordset I need to create to get the last 10 entries displayed won't
allow updates.

I have been poring over the help files but I can only find a way to
make it display the top 10 entries from the whole table, not the top
10 for each individual in the table.

Any suggestions for where to look or how to do this?

 

[Gerald Stanley]

To show 10 rows per student, you need to put in a subquery
with a TOP restrictor. Without knowing your table layout,
it is hard to give an accurate piece of SQL but it would be
something like

SELECT etc
FROM MyTable AS T1
WHERE dateColumn IN (SELECT TOP 10 dateColumn FROM MyTable
WHERE studentId = T1.studentId ORDER BY dateColumn DESC)

The above would give you the latest 10 rows based upon
dateColumn.

If you are not able to apply this to your application,
please reply to this thread with the table structures.

Hope This Helps
Gerald Stanley MCSD