Selecting Top N Problem

[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!

 

[traingamer]

If (as in your example) ALL of the parts have the first 7 characters as the 'common' link.

Your subquery could be something like this:

Select left([casual part], 7) as PartGroup, sum([total cost]
from table
group by  left([casual part], 7)

But you then have to determine which claim number and [casual part] to display on your final report.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Please do not double post your inquires. See thread701-1024646

 

[instructorgirl]

Where do I type in the code? In the SQL screen? Also, how do I then select the top 10 based on cost?

 

[instructorgirl]

Thank you very much for the code to group my part numbers. It works great! Now, I have another problem. Is there any way to take the grouped part numbers in a report in a header for instance and then attach a subreport listing out the individual records that the part is grouped from.

For example:

SumOfTOTAL COST PARTGROUP
$30,137.80 234277A
$19,136.75 8740681
$12,481.54 394538A
$11,972.68 433906A
$10,919.67 452013A
$10,331.65 394539A
$10,099.87 192899A
$9,400.70 433904A
$9,055.31 404281A
$8,530.66 377987A


The first record for total cost $30,137.80, partgroup 234277A consists of 5 individual claims that are tied to it. See the following:

CLAIM NUMBER CAUSAL PART DEFECT DESC TOTAL COST
D26064040A 234277A1 VIBRATION $5,637.80
D26744350A 234277A2 SEIZED $6,722.52
D26976280A 234277A2 SEIZED $6,120.06
D25925010A 234277A2R SURGING OR GALLOPING $5,532.82
D26916410A 234277A2R ERRATIC OR FLUCTUATING $6,124.60

These need to be displayed on the report as well. I thought about putting this information on as a subreport, but I can't figure out how to get it linked to the partgroup. Is it possible to do this in Access? Or is this beyond the realm of what it can do. I am completely stumped on this one. If any one has any ideas on this, I would appreciate it. Thanks!

 

[traingamer]

You may try a query including the partgroup without using the 'group by' clause, then use the 'Sorting and grouping' functions in the report to group by partgroup to get the detail records with headers / footers summarizing the partgroup.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[instructorgirl]

That didn't work. Any other thoughts?