Cumulative Sum

[Sarma]

I have this Access query that has the following fields:

Pair: These are various forex pairs like AUD/CAD, AUD/CHF, etc, etc..totally 54 pairs.

Value: This is a field that contains values of the forex on a 0-100 scale. This is on an ascending scale, starting from 1 and going till 100; for each pair.

Frequency: How many times the value has occurred.

Now my question is how do I Cumulate Frequency ?

My initial query has the format: Pair-Value-Frequency

My output should be: Pair-Value-Frequency-Cumulative Frequency

I need the help of experts.

 

[Sarma]

83 Experts have seen the question so far; but no reply. Is it a very difficult question ?

 

[Sarma]

I have this Access query that has the following fields:

Pair: These are various forex pairs like AUD/CAD, AUD/CHF, etc, etc..totally 54 pairs.
(All unique names)

Value: This is a field that contains values of the forex on a 1-100 scale. This is on an ascending scale, starting from 1 and going till 100; for each pair. (Each value is unique as it appears only once for each pair)

Frequency: How many times the value has occurred.

Now my question is how do I Cumulate Frequency ?

My initial query has the format: Pair-Value-Frequency

My output should be: Pair-Value-Frequency-Cumulative Frequency

I need the help of experts.

 

[plog]

I'll bite. Can you post some sample starting data (along with table and field names) and then what the result should be? Just to be clear, I want 2 sets of data, starting and ending. Use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 45, 3/9/2008
Sally, 560, 6/7/2010
Larry, 44, 4/5/2001

 

[Sarma]

Data looks like this:

Pair BuyingValue Freq AUD/CAD 1 1 AUD/CAD 2 1 AUD/CAD 3 1 AUD/CAD 9 1 AUD/CAD 13 1 AUD/CAD 14 1 AUD/CAD 92 5 AUD/CAD 93 1 AUD/CAD 95 1 AUD/CAD 96 2 AUD/CAD 98 2 AUD/CAD 100 1 AUD/CHF 1 1 AUD/CHF 3 1 AUD/CHF 6 1 AUD/CHF 96 4 AUD/CHF 99 2 AUD/CHF 100 2

 

[Sarma]

Don't know how I can attach the table. As I pasted the excel data, it all got mixed up and is shown in a row.

 

[plog]

Upload the file itself. But remember, I want 2 sets of data--starting and resulting.

 

[Sarma]

Uploading the data.

 

[Sarma]

Thanks for your interest, plog!

 

[Sarma]

Are you able to open the excel sheet, plog ?

 

[plog]

What you want is a simple running total. This SQL will do it:

SELECT [(09) Buying Occurrences Cum].Pair, [(09) Buying Occurrences Cum].BuyingValue, [(09) Buying Occurrences Cum].Freq, DSum("Freq","[(09) Buying Occurrences Cum]","[Pair]='" & [Pair] & "' AND [BuyingValue]<=" & [BuyingValue]) AS CumFreq
FROM [(09) Buying Occurrences Cum];

 

[Sarma]

You are absolutely right, plog! Absolutely brilliant and is working. Thanks, a million, my friend!

 

[Sarma]

Plog! We got the cumulative frequencies below the buyingvalue. Can we get the same above the buying value ? Please see output 2.

 

[plog]

Yes, that's possible. Looks like its just the reverse of what I provided. Play with the code.

 

[Sarma]

Thanks again. I was thinking of finding the highest cumulative frequency for each pair and subtract the cumulative frequency for a particular value (below the value) to get the frequency above the value. Total frequencies = freq below + freq above a value. Will do your idea also. Rgds