Limit on "league table" subreport without using TOP

[Henryp]

Two queries, two reports. The first query takes a company name as input and returns all towns in which that company has an office. The second query returns company, town(ie office location) and number of orders, ordered by number of orders descending.

The first report is based on the first query, the second on the second. The second report is a subreport in the first, linked on the town field.

What the combined report does is to return a "league table" of companies for each town in which the input company has an office. I'd like to limit the number of results for each "league table" to 10 (ie get the subreport to return a maximum of 10 results per town).

The wrong way of doing it is to put the SELECT TOP 10 in the second query!

I've seen some posts about putting a counter text box in the report and there is an MS article on this but it seems only to relate to pre-Access 2002, which is what I am using.

Anyone who can explain it in simple terms will be much admired by this amateur.

Henry

 

[MichaelRed]

????????????????????????????????

wrong way of doing it is to put the SELECT TOP 10

????????????????????????

and -perhaps- you could 'enlighten' some of us (even less PROFESSIONAL then thou) re W H Y this is ?





MichaelRed

 

[Henryp]

Sure, apologies for not making myself clear.

The second query returns around 1000 rows, in the form

Company Town Number of orders
ABC Ltd Richmond 134
B2B plc Newcastle 123
ABC Ltd Newcastle 101
3rdCo Birmingham 98
B2B plc Richmond 96
etc.

Say I put in ABC Ltd as the company in the first query. This would return Richmond and Newcastle as the relevant towns, and create league tables based on all companies in those towns, using the whole 1000 rows to find matches.

If I put select top 10 in the second query, it returns only 10 rows rather than 1000, so I end up fishing for matches in way too small a pond.

What I need is a way of restricting the number of results returned in a report (or subreport) that is not dependent on the query.

Is that clearer, or have I made it worse?

Henry

 

[MichaelRed]

Have the report based on a THIRD query which includes the TOP N and the SECOND query?




MichaelRed