Limit 'Detail' Lines in Multi-Grouped Reports

[rocky]

Hi Folks,

Is it possible to limit the "Detail" in Multi-Grouped Reports to a set number of Records or Recordsets using code?
The data for the report is from a query that provides all of the info for the various group levels used.

I am hoping there is a simple answer to help me with this issue.

Rocky

 

[Access_guy49]

In the query you can use a
SELECT TOP n
in place of a regular SELECT

Would this meet your needs?

 

[CBrighton]

In the query you can use a
SELECT TOP n
in place of a regular SELECT

Would this meet your needs?

This can also be set in the query builder in the query properties window if you do not work directly with SQL.

 

[rocky]

This can also be set in the query builder in the query properties window if you do not work directly with SQL.

I'm looking for the two greatest values at the second grouping level in the report.

If I set the query to two records, there isn't the selection for the report to group by and then total.

In the attached report, there are Entrants who have scores in "Sections" eg O.10.1, O.10.2 etc and these are grouped by the first letter (either "O", "N", or "I"). These are then grouped into "States". I'm looking for highest aggregate score of the two best entries in "O", "N", and "I" groups of sections for each State. In each case, the "Calculated Score" should be from the two highest scores; But the report is picking up more than two because there are more than two sections in "O", "N" & "I" group.

I hope this makes some sense.

 

[CBrighton]

I'm not sure you will be able to do it with a single report & query.

You may need to add subreports, each based on a query using Top x to limit the number of results for that group.

 

[rocky]

If it isn't possible with the single report, perhaps there would be a way to only add the two highest scores rather than the total?

 

[stopher]

As CBrighton mentioned, a sub-report will do the trick. The user won't know that's it's two reports.

Here's how:
http://support.microsoft.com/kb/296449

hth
Chris