using SQL TOP 1 in a report or form

[Roland Alden]

I have a sub-report that displays a photo of an employee from a table that
contains a list of photos taken of all employees. An employee can have more
than one photo in this table, but for this report I want to display only the
most recent.

The sub-report is linked by employee ID and it's easy enough to sort the
table by descending date. However, adding a TOP 1 to the SQL select
statement does not give me the most recent photo for the particular linked
employee on that section of the report; it just reduces the table to only
one row holding a photo of the last employee photographed; report generates
one photo for that employee and no photos for all others.

Is there a way to pass "top 1" semantics through to a sub-report? I am
interested in the same issue for sub-forms, thus the cross-post.

 

[Rob Oldfield]

Sorting the table won't make any difference... I'd tend to force Access to
work by working out the latest date (via a grouped query with a max) and
then use that query in another to pick out the latest record.