SELECT TOP 3 in subreport recordsource returning no records

[glgcag]

I'm trying to use TOP 3 in a query that I've made the recordsource of a subreport and it returns no records. If I simply remove the "TOP 3" portion of the query, it returns records, no problem. Is there a caveat to using TOP 3 in certain ways? Here's the query I am using as my recordsource (the main report and subreport master/child field is AGProjectKey):

SELECT TOP 3 tAGProjectStatus.Initials, tAGProjectStatus.Date, tAGProjectStatus.Description, tAGProjectStatus.AGProjectKey FROM tAGProjectStatus ORDER BY tAGProjectStatus.Date DESC, tAGProjectStatus.AGProjectStatusKey DESC;

Any help would be greatly appreciated!

 

[dhookom]

Your query will return only 3 records total which will not be 3 per AGProjectKey.

There are several solutions. All solutions remove the TOP 3 from the query. Perhaps the simplest is to set the height of the subreport to display only 3 records and set the Sorting and Grouping the [Date] Descending.

You can also add a text box to the subreport detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No
Then add code to the On Format event of the subreport detail section:

   Cancel = Me.txtCount > 3

The 3rd solution involves changing the query to select the top three records per group.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]