Selecting Top 10 Records

[instructorgirl]

I am trying to query on the top 10 records based on cost of claim. See following:

TOTAL COST CLAIM NUMBER CAUSAL PART
$11,132.06 D26461910A 433906A1
$8,530.66 D26713390A 377987A1
$7,352.49 D26874260A 429488A1
$7,082.75 D26852920A 291277A1
$6,722.52 D26744350A 234277A2
$6,522.19 D26312100A 192899A3
$6,124.60 D26916410A 234277A2R
$6,120.06 D26976280A 234277A2
$6,098.18 D27001300A 377518A1
$5,637.80 D26064040A 234277A1


I have two problems. My first problem is that I have multiple parts with the last two or three digits different from the rest of the number. For example, 234277A1, 234277A2, 234277A2R. I want to group those together and sum the total cost. My second problem is that I want to pull the top 10 DISTINCT part costs. I think I need to group my likened part numbers first and then I can get the top 10, but I don't know if this is even possible. Any help would be greatly appreciated!

 

[PHV]

A starting point:
SELECT TOP 10 Sum([cost field]) AS [TOTAL COST], Val([part# field]) AS [CAUSAL PART]
FROM yourTable
GROUP BY Val([part# field])
ORDER BY 1 DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244