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



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
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
WHERE O.CustomerID = ORDERS.CustomerID ORDER BY Freight, OrderID)
ORDER BY CustomerID, Sum(Orders.Freight);

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question