Cumulative Sum (1 Viewer)

Sarma

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
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

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
83 Experts have seen the question so far; but no reply. Is it a very difficult question ?
 

Sarma

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
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

Banishment Pending
Local time
Today, 18:17
Joined
May 11, 2011
Messages
11,613
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

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
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

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
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

Banishment Pending
Local time
Today, 18:17
Joined
May 11, 2011
Messages
11,613
Upload the file itself. But remember, I want 2 sets of data--starting and resulting.
 

Sarma

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
Uploading the data.
 

Attachments

  • MS Access Question.xls
    227.5 KB · Views: 143

Sarma

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
Thanks for your interest, plog!
 

Sarma

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
Are you able to open the excel sheet, plog ?
 

plog

Banishment Pending
Local time
Today, 18:17
Joined
May 11, 2011
Messages
11,613
What you want is a simple running total. This SQL will do it:


Code:
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

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
You are absolutely right, plog! Absolutely brilliant and is working. Thanks, a million, my friend!
 

Sarma

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
Plog! We got the cumulative frequencies below the buyingvalue. Can we get the same above the buying value ? Please see output 2.
 

Attachments

  • MS Access Question.xls
    242.5 KB · Views: 109

plog

Banishment Pending
Local time
Today, 18:17
Joined
May 11, 2011
Messages
11,613
Yes, that's possible. Looks like its just the reverse of what I provided. Play with the code.
 

Sarma

Registered User.
Local time
Tomorrow, 03:17
Joined
Nov 16, 2009
Messages
75
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
 

Users who are viewing this thread

Top Bottom