Finding a list based on 95% of a number

[christheprogrammer]

Hey all,
I have a table with two fields, one is a species code and another is the weight of that species that has been caught (fish). I also have a total weight of all species of fish caught and I need to determine the species which make up 95% of the catch...

Table:
species_code weight
111 123
201 321
222 187
hundreds more....

now say my total weight of catch is 3000
How do I get the list of species whose weights make up 95% of 3000?

Cheers and Thanks in advance



Later

 

[GoDawgs]

I don't think I'm understanding this question. Are you asking to get a list of fish until you hit 95% of the total...based on some kind of sort? So you have a running sum, then when you get to 95% you stop?

Basically I'm confused because, using your 3 fish above and saying that you want 50% instead of 95%, 111 and 201 will be 50% of the total weight, but so will 222 and 201. Hope I can help...

Kevin

 

[christheprogrammer]

Thanks Kevin for the reply but I decided to do it via code (My SQL skills bite) anyways yes it was based on the fact that the weight field was indexed in descending order. I ended up using DAO to cycle through the recordset, summing and comparing to see if I had hit my total yet, then stopping when I did.
Cheers and thanks for the post

Chris

Later