Hi All,

I have a problem that is driving me mad as it seems it should be simple to do but I can't get the results I want!

I have a table populated with salesmans names, and dates for their last sale.

I simply want to find, for each salesman, the dates of their last 3 sales.

Any help gratefully received.

Kevin

Microsoft provides this solution.

Alternately you could do it with a loop in VBA

Thanks for the response.
I did simplyfy my question slightly as in reality the data is in two seperate tables.
Could VBA still be used?

Kevin

See the Microsoft Knowledge Base article How to Create a "Top N Values Per Group" Query. Each group is a salesman in your case.

Excellent I will give it a go.

Many Thanks Van

Kevin

If I run the following

SELECT decouting.dohname, outing.oprevruns, outing.odate
FROM decouting LEFT JOIN outing ON decouting.dohid = outing.ohorseid
ORDER BY decouting.dohname, outing.odate DESC;

It takes a couple of minutes and produces around 10,000 records

I have tried to implement the knowledge base article thus

SELECT decouting.dohname, outing.oprevruns, outing.odate
FROM decouting LEFT JOIN outing ON decouting.dohid = outing.ohorseid
WHERE (((outing.odate) In (select top 1 [odate] from outing where [dohid]=[outing].[ohorseid] order by [odate]desc)))
ORDER BY decouting.dohname, outing.odate DESC;

Now the query never finishes, task manager show continous 50% CPU being used but still no output after an hour!

Could it be because the tables are actually Foxpro tables that I have linked to Access?

Any Ideas?

Kevin

You need to restrict the query to as few salesmen as possible...

The Query uses a correlated SubQuery which means that the SubQuery needs to be executed once for each row in the main Query and this is time consuming. You also have additional problem that the data is being pulled from FoxPro which adds to the delay.

Can't see why you need to order the rows in the main Query by odate DESC since the Query should only return 1 odate for each dohname.

Search for my posts in UA on "correlated SubQueries" for more info...