Select query for running totals

[asawadude]

My Access skills are leaving a lot to be desired, and my self-taught skills are limited to the QBE screens.



I could really use some guidance on how to create a simple query that generates a cumulative total. I have a simplified example of what I need in the attached screen capture.

I have an Excel spreadsheet that performs the evaluation, but in the real world, my sample population contains approximately 800 prototypes with 12 different measurements evaluated. As a result, my spreadsheet is grossly unwieldy.

If someone can help me get started based on the simplified info on the screen capture, it would be greatly appreciated. Thanks!

[weekend00]

I don't understand the [# needed] column, most of them are same with [# samples], but the last 3 are not. How did you calculate [# needed]?

Here is a solution to get the total, but this is not a good solution because it runs very very slowly. Actually, Access query is not good at this kind of calculation. If you want speed it up, you need to write some VBA codes.

select *, dsum("# samples","YOURTABLE","SCORE>="& SCORE) from YOURTABLE

[asawadude]

I don't understand the [# needed] column, most of them are same with [# samples], but the last 3 are not. How did you calculate [# needed]?

Here is a solution to get the total, but this is not a good solution because it runs very very slowly. Actually, Access query is not good at this kind of calculation. If you want speed it up, you need to write some VBA codes.

select *, dsum("# samples","YOURTABLE","SCORE>="& SCORE) from YOURTABLE

Thanks for the code, I'll give it a try.

In my spreadsheet, the "# needed" column is used to find the cut-off point for 200 samples. In this case, I need 14 of 22 samples of prototype Viking to reach 200. The formula in Column F contains a nested multi IF-THEN-ELSE statements which evaluate Column E to see if the target (200) has been reached. If not, the prototype's samples count is added to the target total. It continues down the spreadsheet until it finds a value in Column E that equals or exceeds the target. In this case, the Viking sample contains the 200th highest scoring value can be found, however it's cumulative total is 208, 8 more samples then required.

The IF-THEN-ELSE statement also contains the parameter that looks at the previous cumulative total to determine how many samples in the final group are required to reach the target. The formula looks at line 15 and sees that the Beta Sample cumulative total was 186. 200-186 is 14, so therefore only 14 samples of Viking are required to reach the target goal. The remaining samples are discarded...

Because I looking for a defined number from the total population, this was the only way I could figure out how to do it. I need a more elegant solution than my spreadsheet which works fine, but is totally cumbersome.