Selecting Multiple Subsets Of Rows From A Child Table

[mcwebtree]

I've tried to research this but can't find out how to do it anywhere. This is an example of a 2 table scenario I've faced a few times.

I have 2 tables. table 1 lists a number of sports events. table 2 lists the results of all the events. I want to write a report that takes each event in table 1 and extracts the top 3 places from table 2 and outputs them in the report. I have tried to think my way through it using nested queries and unions and apart from a temporary table and a code module stepping through table one and appending records to the temp table from table 2 I can't do it.

e.g. Table 1 has records,
1, 100m,
2, 200m ,
3, 400m,
4, relay.
table 2 has

1, 1, dave, 10.4
2, 1, steve, 11.9
3, 1, fred, 12.0
4, 1, helen, 14.0
5, 1, sara, 10.8
6, 2, dave, 10


7, 2, steve, 14
8, 2, roger, 13
and so on.

I want to output
100m, dave, 10.4, 1st
100m, sara, 10.8, 2nd
100m, steve, 11.9, 3rd
200m, dave, 10, 1st
200m, roger, 13, 2nd
200m, steve, 14, 3rd
...
and so on for as many events as there are.

A similar scenario is where I have a client and a number of phone calls. I want the latest phone call listed next to the client and the notes from that call. getting the latest date for the call is easy using a nested query, dlookup, but I can't then get a second column without a second nested query and the time penalty starts to get crazy. Is there a neat sql way to do what I want without having to write and save multiple queries and then link them together in another query?

Please say this can be done and I'm just being stupid...

Many thanks for any help.

[hertfordkc]

the report is based?
Have you tried building a report which provides the basic category (event) and a subreport which is based on a query referring to the parent report's query? The subreport might have to refer to a field/control in the main report to get a filter element.

[mcwebtree]

I can do it with a sub report, but what I really want to know is can it be done in one sql statement.
Thanks