How do I get a crosstab query to sum the top n lowest numbers?

[Guest]

I am setting up a database to produce some running race results, where tables
of entrant data are merged with a table of finish position through the common
key of race number.
To produce team results, I need to sum the finish positions of the three
members of each club in each age category which have the lowest finish
position.
The easiest way that I can see is to do a crosstab query - since I have many
teams and many categories, but access won't let me sort, pick out the top 3
and sum them up to form each result.
Is there a way of doing this?

 

[Duane Hookom]

I'm not sure how this works with a crosstab but assuming you want to Sum the
lowest three Freights by Customer from the Northwind Orders table:

SELECT CustomerID, Sum(Orders.Freight) AS SumOfFreight
FROM Orders
WHERE Freight In
(SELECT TOP 3 Freight
FROM ORDERS O
WHERE O.CustomerID = ORDERS.CustomerID ORDER BY Freight, OrderID)
GROUP BY CustomerID
ORDER BY CustomerID, Sum(Orders.Freight);