Running sum major problem

  • Thread starter Thread starter Ticotion
  • Start date Start date
T

Ticotion

Hi

I am currently trying to make a query for a pareto chart. For this I need to
be able to calculate a running sum.

I have a query that shows top 5 result and sums up data from serveral other
queries. The query consist of the following elements:

Date Sector Errorcause Freq Freqtotal Freqpct
06-01-09 3 Cause1 24 32 75
06-01-09 3 Cause2 6 32
18,8
06-01-09 3 Cause3 1 32
3,1
06-01-09 3 Cause4 1 32
3,1
06-01-09 3 Cause5 0 32 0

I want to be able to calculate a running sum on freqpct? How can this be done?

Br
Ticotion
 
hi Ticotion,
I am currently trying to make a query for a pareto chart. For this I need to
be able to calculate a running sum.
I want to be able to calculate a running sum on freqpct? How can this be done?
Click to expand...
The easy way: Use the running sum feature of a TextBox on a form or
report. Otherwise you need a complex domain aggregate:

http://support.microsoft.com/kb/290136

or an helper function:

http://support.microsoft.com/kb/205183

I'd normally prefer the domain aggregate rewritten to a subquery:

http://msdn.microsoft.com/en-us/library/aa217680(office.11).aspx



mfG
--> stefan <--
 
Hi Stefan

I've already tried to use a subquery with no luck. The result for each row
is the total of freqpct (in my exsample it shows 100 for each row.) Her is
the sql with subquery I have tried with

SELECT qrySimSektorparetoh.Datem, qrySimSektorparetoh.Sektor,
qrySimSektorparetoh.errorcause, qrySimSektorparetoh.Frequence,
qrySimSektorparetoh.[Freq total], qrySimSektorparetoh.Freqpct
(SELECT sum(qrySimSektorparetoh.Freqpct)
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Datem)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16])))
as RunSum
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Datem)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]));

Any suggestions?

Ticotion
 
hi Ticotion,
I've already tried to use a subquery with no luck. The result for each row
is the total of freqpct (in my exsample it shows 100 for each row.) Her is
the sql with subquery I have tried with
..
(SELECT sum(qrySimSektorparetoh.Freqpct)
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Datem)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16])))
as RunSum
..
Any suggestions?
Click to expand...
The subqueries WHERE condition must be bound against the query not the
form. It must only return the rows to sum up based on the primary key or
another meaningful candidate key - [Freq] should do it:

SELECT O.*

(SELECT SUM(I.[Freqpct])
FROM qrySimSektorparetoh I
WHERE I.[Datem] = O.[Datem]
AND I.[Sektor] = O.[Sektor]
AND I.[Freq] <= O.[Freq]
) AS [RunSum]

FROM qrySimSektorparetoh O

WHERE O.[Datem] = [Forms]![frmSIMpareto]![Kombinationsboks7]
AND O.[Sektor] = [Forms]![frmSIMpareto]![Kombinationsboks16];




mfG
--> stefan <--
 
Hi Stefan

I think we are getting closer but not yet there. So you say it should return
rows based on a primary key or another meaningful candidate. Freq is not a
unique number. I have not a primary key in my or a unique number in the query
as I see it. Could this be the problem?

The SQL give me the same number again for each row just a very high number.
Here are the SQL used.

SELECT qrySimSektorparetoh.Datem, qrySimSektorparetoh.Sektor,
qrySimSektorparetoh.Errorcause, qrySimSektorparetoh.[Freq],
qrySimSektorparetoh.[freqtotal], qrySimSektorparetoh.Freqpct
(SELECT SUM([Freqpct])
FROM qrySimSektorparetoh
WHERE qrySimSektorparetoh.[Datem] = qrySimSektorparetoh.[Datem]
AND qrySimSektorparetoh.[Sektor] = qrySimSektorparetoh.[Sektor]
AND qrySimSektorparetoh.[Freq] <= qrySimSektorparetoh.[Freq]
) AS [RunSum]
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Dato)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]));

Thank you for your Help

Ticotion

Stefan Hoffmann said:
hi Ticotion,
I've already tried to use a subquery with no luck. The result for each row
is the total of freqpct (in my exsample it shows 100 for each row.) Her is
the sql with subquery I have tried with
..
(SELECT sum(qrySimSektorparetoh.Freqpct)
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Datem)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16])))
as RunSum
..
Any suggestions?
Click to expand...
The subqueries WHERE condition must be bound against the query not the
form. It must only return the rows to sum up based on the primary key or
another meaningful candidate key - [Freq] should do it:

SELECT O.*

(SELECT SUM(I.[Freqpct])
FROM qrySimSektorparetoh I
WHERE I.[Datem] = O.[Datem]
AND I.[Sektor] = O.[Sektor]
AND I.[Freq] <= O.[Freq]
) AS [RunSum]

FROM qrySimSektorparetoh O

WHERE O.[Datem] = [Forms]![frmSIMpareto]![Kombinationsboks7]
AND O.[Sektor] = [Forms]![frmSIMpareto]![Kombinationsboks16];




mfG
--> stefan <--
Click to expand...
 
hi Ticotion,
I think we are getting closer but not yet there. So you say it should return
rows based on a primary key or another meaningful candidate. Freq is not a
unique number. I have not a primary key in my or a unique number in the query
as I see it. Could this be the problem?
Click to expand...
Maybe, but take a closer look at my example. The table alias names are
vital...


mfG
--> stefan <--
 
Hi Stefan

I get a syntax error in query expression when using the SQL statement. I'm
not so familiar with SQL so can you see what might be wrong?

Ticotion
 
hi,
The SQL give me the same number again for each row just a very high number.
Here are the SQL used.
Click to expand...
Try this:

SELECT O.Datem,
O.Sektor,
O.Errorcause,
O.[Freq],
O.[freqtotal],
O.Freqpct,
(SELECT SUM(I.[Freqpct])
FROM qrySimSektorparetoh I
WHERE I.[Datem] = O.[Datem]
AND I.[Sektor] = O.[Sektor]
AND I.[Freq] <= O.[Freq]
) AS [RunSum]
FROM qrySimSektorparetoh O
WHERE O.Dato=[Forms]![frmSIMpareto]![Kombinationsboks7]
AND O.Sektor=[Forms]![frmSIMpareto]![Kombinationsboks16];


btw, there was a comma missing...

mfG
--> stefan <--
 
Hi Stefan

Now I get a result. We are getting very close but there two issue now. The
result I get is as follow (I do not show the other attributes that the SQL
returns)

Datem Freqpct RunSum RunSum2
06-01-2009 75 100 75
06-01-2009 18,8 25 93,8
06-01-2009 3,1 6,2 96,9
06-01-2009 3,1 6,2 100

The RunSum2 is the result that I expected where it sums from the top. I
guess it says 6,2 in the last two rows because this is not unique. Can this
be handled in any way?

Br
Ticotion





Stefan Hoffmann said:
hi,
The SQL give me the same number again for each row just a very high number.
Here are the SQL used.
Click to expand...
Try this:

SELECT O.Datem,
O.Sektor,
O.Errorcause,
O.[Freq],
O.[freqtotal],
O.Freqpct,
(SELECT SUM(I.[Freqpct])
FROM qrySimSektorparetoh I
WHERE I.[Datem] = O.[Datem]
AND I.[Sektor] = O.[Sektor]
AND I.[Freq] <= O.[Freq]
) AS [RunSum]
FROM qrySimSektorparetoh O
WHERE O.Dato=[Forms]![frmSIMpareto]![Kombinationsboks7]
AND O.Sektor=[Forms]![frmSIMpareto]![Kombinationsboks16];


btw, there was a comma missing...

mfG
--> stefan <--
Click to expand...
 
hi Ticotion,
Now I get a result. We are getting very close but there two issue now. The
result I get is as follow (I do not show the other attributes that the SQL
returns)

Datem Freqpct RunSum RunSum2
06-01-2009 75 100 75
06-01-2009 18,8 25 93,8
06-01-2009 3,1 6,2 96,9
06-01-2009 3,1 6,2 100

The RunSum2 is the result that I expected where it sums from the top. I
guess it says 6,2 in the last two rows because this is not unique. Can this
be handled in any way?
Click to expand...

Use another order criteria in the sub query, imho

... AND I.[Freq] > O.[Freq] ) AS [RunSum]


mfG
--> stefan <--
 
Hi Stefan

Sorry for the late answer but it works. I'm trying to understand the SQL
syntax but do not get it. I've mad a simpler table where I'm trying to use
the following code

SELECT O.Material, O.PriceVol, O.PriceVolPct
(SELECT Sum(I.PriceVolPct)
FROM QryABCPct I
Where O.Material >=I.Material) AS RunSum
FROM QryABCPct AS O;

where i'm trying to make a runningsum on the PriceVol Pct field. The
Material field (It is a material number) is uniq. But I do not get the right
result. Any suggestions?

Br
Ticotion

Stefan Hoffmann said:
hi Ticotion,
Now I get a result. We are getting very close but there two issue now. The
result I get is as follow (I do not show the other attributes that the SQL
returns)

Datem Freqpct RunSum RunSum2
06-01-2009 75 100 75
06-01-2009 18,8 25 93,8
06-01-2009 3,1 6,2 96,9
06-01-2009 3,1 6,2 100

The RunSum2 is the result that I expected where it sums from the top. I
guess it says 6,2 in the last two rows because this is not unique. Can this
be handled in any way?
Click to expand...

Use another order criteria in the sub query, imho

... AND I.[Freq] > O.[Freq] ) AS [RunSum]


mfG
--> stefan <--
Click to expand...
 
hi,
SELECT O.Material, O.PriceVol, O.PriceVolPct
where i'm trying to make a runningsum on the PriceVol Pct field. The
Material field (It is a material number) is uniq. But I do not get the right
result. Any suggestions?
Click to expand...
The trick is the correct WHERE condition in the sub query. Your query is
working well.

Create a simple table with your three fields Material, PriceVol,
PriceVolPct, all numbers (Long Integer). Add sample records (1,1,1),
(2,2,2), (3,3,3).

Run your query against this table. This is the result you should get:

Material, PriceVol, PriceVolPct, RunsSum
1, 1, 1, 1
2, 2, 2, 3
3, 3, 3, 6

The sub-query selects all records before the current one, this what the
WHERE condtion is for, and sums them up.


mfG
--> stefan <--
 
Hi Stefan

I can see that it works with the simpel table but it just doesn't work with
the data that I have.

If you input the following data in the simple table it will not calculate it
correctly. Notice that the field PriceVolPct has field sixe set to Double

Material PriceVol PriceVolPct
AAK530D692270 2 1,62245E-06
AAK019D852609 2 1,62245E-06
AAK726A2517 3 2,43367E-06
AAK019D877499 3 2,43367E-06
AAK500D651570 3 2,43367E-06
AAK019D874699 3 2,43367E-06

What am I doing wrong?

Ticotion
 
hi,
I can see that it works with the simpel table but it just doesn't work with
the data that I have.
What am I doing wrong?
Click to expand...
I assume the sub-query. Please post a complete and concise example. Thus
means, table structure, sample records, your query so far and its output
and last but nor least the desired output instead.


mfG
--> stefan <--
 
Hi Stefan

I'm using a query as the base for the RunSum Query. This query is based on
other queries which are then again based on a large table.

The base query looks like this
SELECT qryABCFinal.Material, qryABCFinal.PriceVol,
([PriceVol]/[PriceVolTotal])*100 AS PriceVolPct
FROM qryABCFinal, QryABCTotalSum
WHERE (((qryABCFinal.PriceVol) Is Not Null) AND
((([PriceVol]/[PriceVolTotal])*100)>0))
ORDER BY ([PriceVol]/[PriceVolTotal])*100;

The file format for the base query is as follows:
Material = (Text)
PriceVol = (Long Interger)
PriceVolPct = (Double)

The first 4 rows in the query (it contains 25000 rows)

Material PriceVol PriceVolPct
AAK530D692270 2 1,62245E-06
AAK019D852609 2 1,62245E-06
AAK726A2517 3 2,43367E-06
AAK019D877499 3 2,43367E-06
AAK500D651570 3 2,43367E-06
AAK019D874699 3 2,43367E-06

I then use the following SQL to make a RunSum

SELECT O.Material, o.PriceVolPct, O.PriceVolPct
(SELECT Sum(I.PriceVolPct)
FROM QryABCPct I
Where I.Material <= O.Material) AS RunSum
FROM QryABCPct AS O;

The result I get is a follows (Only the first 4 rows )
Material PriceVolPct RunSum
AAK530D692270 1,62245E-06 61,55618892
AAK019D852609 1,62245E-06 1,437037024
AAK726A2517 2,43367E-06 61,69372642
AAK019D877499 2,43367E-06 1,48563994

It should have been

Material PriceVol RunSum
AAK530D692270 2 1,62245E-06
AAK019D852609 2 3,2449E-06
AAK726A2517 3 5,67857E-06
AAK019D877499 3 8,11225E-06
AAK500D651570 3 1,05459E-05
AAK019D874699 3 1,29796E-05

let me know if you need any other information

Thanks for all your help

Ticotion
 
hi,
I then use the following SQL to make a RunSum

SELECT O.Material, o.PriceVolPct, O.PriceVolPct
(SELECT Sum(I.PriceVolPct)
FROM QryABCPct I
Where I.Material <= O.Material) AS RunSum
FROM QryABCPct AS O;

The result I get is a follows (Only the first 4 rows )
Material PriceVolPct RunSum
AAK530D692270 1,62245E-06 61,55618892
AAK019D852609 1,62245E-06 1,437037024
AAK726A2517 2,43367E-06 61,69372642
AAK019D877499 2,43367E-06 1,48563994
Click to expand...
The result looks fine, but it is unordered:

SELECT O.Material,
O.PriceVolPct,
(SELECT Sum(I.PriceVolPct)
FROM QryABCPct I
WHERE I.Material <= O.Material) AS RunSum
FROM QryABCPct O
ORDER BY O.Material ;

mfG
--> stefan <--
 
Hi Stefan

No that doesn't work. I now get a good looking running sum but this is based
on a ascending material number which doesn't reflect the running sum.

I know get the "lowest" material number first and not the material that has
the lowest running sum. So what I get is

Material PriceVolPct RunSum
AAK010M0005 0,003582369 0,003582369
AAK010M0018 0,038679203 0,042261572
AAK010M0031 0,007869693 0,050131264
AAK010M0308 0,021984194 0,072115459

Which are other material numbers than

Material PriceVol RunSum
AAK530D692270 2 1,62245E-06
AAK019D852609 2 3,2449E-06
AAK726A2517 3 5,67857E-06
AAK019D877499 3 8,11225E-06
AAK500D651570 3 1,05459E-05
AAK019D874699 3 1,29796E-05

which I should have gotten. Any suggestions?

Ticotion
 
hi,
No that doesn't work. I now get a good looking running sum but this is based
on a ascending material number which doesn't reflect the running sum.
Click to expand...
I know get the "lowest" material number first and not the material that has
the lowest running sum. So what I get is

Material PriceVolPct RunSum
AAK010M0005 0,003582369 0,003582369
AAK010M0018 0,038679203 0,042261572
AAK010M0031 0,007869693 0,050131264
AAK010M0308 0,021984194 0,072115459
Click to expand...
This is the correct result.
Which are other material numbers than

Material PriceVol RunSum
AAK530D692270 2 1,62245E-06
AAK019D852609 2 3,2449E-06
AAK726A2517 3 5,67857E-06
AAK019D877499 3 8,11225E-06
AAK500D651570 3 1,05459E-05
AAK019D874699 3 1,29796E-05

which I should have gotten. Any suggestions?
Click to expand...
As you said, you have 25k records. Use the context menu filter and
filter for AAK019D874699 in your current (above) resultset.

mfG
--> stefan <--
 
Hi Stefan

I'm sorry but I still doesn't get the right result. See the exsamble below.
I've made a test table that consist of Material (Text) and VolPricePct
(Double). I.ve sorted it so the Material with the highestVolPricePct is in
the top. I have the following data in the table
Material VolPricePct
AAKR48131158 2,825843112
AAKR48131075 2,260511669
AAKR48131314 1,525065657
AAK522D0402 1,523360867
AAK102H069918 1,490962125

I use the following code to generate the running sum

SELECT O.Material, O.VolPricePct
(SELECT Sum(I.VolPricePct)
FROM RunSumRohTest I
Where I.Material <= O.Material) AS RunSum
FROM RunSumRohTest AS O
ORDER BY O.Material;

This is the result

Material RunSum
AAKR48131314 9,625743429
AAKR48131158 8,100677772
AAKR48131075 5,27483466
AAK522D0402 3,014322991
AAK102H069918 1,490962125

This is what it should have shown

Material RunSum2
AAKR48131158 2,825843112
AAKR48131075 5,086354781
AAKR48131314 6,611420438
AAK522D0402 8,134781304
AAK102H069918 9,625743429

As you can see thé Access result is not correct. If I change the SQL Where to
Where I.Material >= O.Material and sort it desc on material I get

Material RunSum
AAKR48131314 1,525065657
AAKR48131158 4,350908769
AAKR48131075 6,611420438
AAK522D0402 8,134781304
AAK102H069918 9,625743429

which is close but the first to numbers are not correct. I guess this is all
doe to the order by on material. I do not understand how the context filter
can solve this problem?

Please help. And once again thank you for your time and help.

Ticotion
 
hi,
Maybe this is the problem: you can only define a running sum on an
ordered recordset. If the ascending material number is the wrong order,
then you have to change the WHERE criteria in the sub-query to the
appropriate one.
Material RunSum2
AAKR48131158 2,825843112
AAKR48131075 5,086354781
AAKR48131314 6,611420438
AAK522D0402 8,134781304
AAK102H069918 9,625743429
Click to expand...
Material is not ordered, so the running sum is based on what order criteria?


mfG
--> stefan <--
 
Back
Top