Running sum

T

Ticotion

Hi

I have a problem calculating a runing sum for a field in a query. As far as
I can read I need to use a subquery but I can not get this to work. I need to
calculate a runing sum on the field [Hyp procent]

My SQL query is

PARAMETERS [ [Forms]![frmSIMpareto]![Kombinationsboks7]] DateTime, [
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst] AS Fejlårsag, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS
[Fejlen hyppighed], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)
GROUP BY qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1)

HAVING (((qrySIM2sektor.Dato) Between
[Forms]![frmSIMpareto]![Kombinationsboks7] And
[Forms]![frmSIMpareto]![Combo38]) AND
((qrySIM2sektor.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]))

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

Help please

Ticotion
 
J

John Spencer

First problem is the extra brackets in the Parameters clause.

Next problem is that you should be using a WHERE clause in place of the Having
clause.

PARAMETERS [Forms]![frmSIMpareto]![Kombinationsboks7] DateTime,
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst] AS Fejlårsag
, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS [Fejlen hyppighed]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)

WHERE qrySIM2sektor.Dato Between [Forms]![frmSIMpareto]![Kombinationsboks7]
And [Forms]![frmSIMpareto]![Combo38]
AND qrySIM2sektor.Sektor=[Forms]![frmSIMpareto]![Kombinationsboks16]

GROUP BY qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1)

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

That cleans up the query as it exists. The next step is to give us a bit more
detail on what you want to do. A running sum of Hyp Procent based on the Dato
field? Over all the records, over some group of the records and then start over?

You will probably need to use this query as the basis for another query. In
that query you will return the records generated by this query and use a
subquery based on this query to calculate the running sum.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi

I have a problem calculating a runing sum for a field in a query. As far as
I can read I need to use a subquery but I can not get this to work. I need to
calculate a runing sum on the field [Hyp procent]

My SQL query is

PARAMETERS [ [Forms]![frmSIMpareto]![Kombinationsboks7]] DateTime, [
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst] AS Fejlårsag, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS
[Fejlen hyppighed], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)
GROUP BY qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1)

HAVING (((qrySIM2sektor.Dato) Between
[Forms]![frmSIMpareto]![Kombinationsboks7] And
[Forms]![frmSIMpareto]![Combo38]) AND
((qrySIM2sektor.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]))

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

Help please

Ticotion
Click to expand...
 
T

Ticotion

(Hi John

Thank you for noticing the extra brackets

I'll try to show you want I want based on an exsample. In ( ) I'll include
an english translation of the field name.The Query should return the
following when the user choose Sektor = 5 and date range from 01-01-09 to
03-01-09:

[Dato] (Date) [Sektor](Sector) [Fejlårsag] (Error) [Fejlen
Hyppighed](Frequency)
01-01-09 5 Text1 2
01-01-09 5 Text2 1
02-01-09 5 Text3 5
03-01-09 5 Text4 2


[Hyp Total] (Freq. total) [Hyp procent](Freq. pct) [Acc Hyp] (Acc freq)
10 20 20
10 10 30
10 50 80
10 20 100

So the [Acc Hyp] should accumulate [hyp procent]

Can this be done?

Ticotion


Jo10hn Spencer said:
First problem is the extra brackets in the Parameters clause.

Next problem is that you should be using a WHERE clause in place of the Having
clause.

PARAMETERS [Forms]![frmSIMpareto]![Kombinationsboks7] DateTime,
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst] AS Fejlårsag
, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS [Fejlen hyppighed]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)

WHERE qrySIM2sektor.Dato Between [Forms]![frmSIMpareto]![Kombinationsboks7]
And [Forms]![frmSIMpareto]![Combo38]
AND qrySIM2sektor.Sektor=[Forms]![frmSIMpareto]![Kombinationsboks16]

GROUP BY qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1)

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

That cleans up the query as it exists. The next step is to give us a bit more
detail on what you want to do. A running sum of Hyp Procent based on the Dato
field? Over all the records, over some group of the records and then start over?

You will probably need to use this query as the basis for another query. In
that query you will return the records generated by this query and use a
subquery based on this query to calculate the running sum.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi

I have a problem calculating a runing sum for a field in a query. As far as
I can read I need to use a subquery but I can not get this to work. I need to
calculate a runing sum on the field [Hyp procent]

My SQL query is

PARAMETERS [ [Forms]![frmSIMpareto]![Kombinationsboks7]] DateTime, [
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst] AS Fejlårsag, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS
[Fejlen hyppighed], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)
GROUP BY qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1)

HAVING (((qrySIM2sektor.Dato) Between
[Forms]![frmSIMpareto]![Kombinationsboks7] And
[Forms]![frmSIMpareto]![Combo38]) AND
((qrySIM2sektor.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]))

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

Help please

Ticotion
Click to expand...
Click to expand...
 
T

Ticotion

Hi

I have modified my query so now it looks like this

SELECT qrySimSektorparetoh.Dato, qrySimSektorparetoh.Sektor,
qrySimSektorparetoh.Fejlårsag, qrySimSektorparetoh.[Fejlen hyppighed],
qrySimSektorparetoh.[Hyp total], qrySimSektorparetoh.[Hyp procent]
(SELECT Sum(qrySimSektorparetoh.[Hyp procent])
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Dato)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16])))
As RunSum
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Dato)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]));

The result is that I get the Total sum of Hyp Procent for each record and
not the running sum. What am I doing wrong?

Please Help

Ticotion

Ticotion said:
(Hi John

Thank you for noticing the extra brackets

I'll try to show you want I want based on an exsample. In ( ) I'll include
an english translation of the field name.The Query should return the
following when the user choose Sektor = 5 and date range from 01-01-09 to
03-01-09:

[Dato] (Date) [Sektor](Sector) [Fejlårsag] (Error) [Fejlen
Hyppighed](Frequency)
01-01-09 5 Text1 2
01-01-09 5 Text2 1
02-01-09 5 Text3 5
03-01-09 5 Text4 2


[Hyp Total] (Freq. total) [Hyp procent](Freq. pct) [Acc Hyp] (Acc freq)
10 20 20
10 10 30
10 50 80
10 20 100

So the [Acc Hyp] should accumulate [hyp procent]

Can this be done?

Ticotion


Jo10hn Spencer said:
First problem is the extra brackets in the Parameters clause.

Next problem is that you should be using a WHERE clause in place of the Having
clause.

PARAMETERS [Forms]![frmSIMpareto]![Kombinationsboks7] DateTime,
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst] AS Fejlårsag
, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS [Fejlen hyppighed]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)

WHERE qrySIM2sektor.Dato Between [Forms]![frmSIMpareto]![Kombinationsboks7]
And [Forms]![frmSIMpareto]![Combo38]
AND qrySIM2sektor.Sektor=[Forms]![frmSIMpareto]![Kombinationsboks16]

GROUP BY qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1)

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

That cleans up the query as it exists. The next step is to give us a bit more
detail on what you want to do. A running sum of Hyp Procent based on the Dato
field? Over all the records, over some group of the records and then start over?

You will probably need to use this query as the basis for another query. In
that query you will return the records generated by this query and use a
subquery based on this query to calculate the running sum.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi

I have a problem calculating a runing sum for a field in a query. As far as
I can read I need to use a subquery but I can not get this to work. I need to
calculate a runing sum on the field [Hyp procent]

My SQL query is

PARAMETERS [ [Forms]![frmSIMpareto]![Kombinationsboks7]] DateTime, [
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst] AS Fejlårsag, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS
[Fejlen hyppighed], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)
GROUP BY qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1)

HAVING (((qrySIM2sektor.Dato) Between
[Forms]![frmSIMpareto]![Kombinationsboks7] And
[Forms]![frmSIMpareto]![Combo38]) AND
((qrySIM2sektor.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]))

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

Help please

Ticotion
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

I don't know of a way to solve your problem based on the information you have
presented.

There is no way to decide which of the two records data 01-01-09 is the first
record and which is the second record. Given that there is no way to generate
a running sum in a query.

It can be done on a report. If you intend to use this information for a
report post back or post in the reports newsgroup with a description of your
data as returned by the query (the original one that works) and specify what
you want to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi

I have modified my query so now it looks like this

SELECT qrySimSektorparetoh.Dato, qrySimSektorparetoh.Sektor,
qrySimSektorparetoh.Fejlårsag, qrySimSektorparetoh.[Fejlen hyppighed],
qrySimSektorparetoh.[Hyp total], qrySimSektorparetoh.[Hyp procent]
(SELECT Sum(qrySimSektorparetoh.[Hyp procent])
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Dato)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16])))
As RunSum
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Dato)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]));

The result is that I get the Total sum of Hyp Procent for each record and
not the running sum. What am I doing wrong?

Please Help

Ticotion

Ticotion said:
(Hi John

Thank you for noticing the extra brackets

I'll try to show you want I want based on an exsample. In ( ) I'll include
an english translation of the field name.The Query should return the
following when the user choose Sektor = 5 and date range from 01-01-09 to
03-01-09:

[Dato] (Date) [Sektor](Sector) [Fejlårsag] (Error) [Fejlen
Hyppighed](Frequency)
01-01-09 5 Text1 2
01-01-09 5 Text2 1
02-01-09 5 Text3 5
03-01-09 5 Text4 2


[Hyp Total] (Freq. total) [Hyp procent](Freq. pct) [Acc Hyp] (Acc freq)
10 20 20
10 10 30
10 50 80
10 20 100

So the [Acc Hyp] should accumulate [hyp procent]

Can this be done?

Ticotion


Jo10hn Spencer said:
First problem is the extra brackets in the Parameters clause.

Next problem is that you should be using a WHERE clause in place of the Having
clause.

PARAMETERS [Forms]![frmSIMpareto]![Kombinationsboks7] DateTime,
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst] AS Fejlårsag
, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS [Fejlen hyppighed]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)

WHERE qrySIM2sektor.Dato Between [Forms]![frmSIMpareto]![Kombinationsboks7]
And [Forms]![frmSIMpareto]![Combo38]
AND qrySIM2sektor.Sektor=[Forms]![frmSIMpareto]![Kombinationsboks16]

GROUP BY qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1)

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

That cleans up the query as it exists. The next step is to give us a bit more
detail on what you want to do. A running sum of Hyp Procent based on the Dato
field? Over all the records, over some group of the records and then start over?

You will probably need to use this query as the basis for another query. In
that query you will return the records generated by this query and use a
subquery based on this query to calculate the running sum.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ticotion wrote:
Hi

I have a problem calculating a runing sum for a field in a query. As far as
I can read I need to use a subquery but I can not get this to work. I need to
calculate a runing sum on the field [Hyp procent]

My SQL query is

PARAMETERS [ [Forms]![frmSIMpareto]![Kombinationsboks7]] DateTime, [
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst] AS Fejlårsag, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS
[Fejlen hyppighed], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)
GROUP BY qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1)

HAVING (((qrySIM2sektor.Dato) Between
[Forms]![frmSIMpareto]![Kombinationsboks7] And
[Forms]![frmSIMpareto]![Combo38]) AND
((qrySIM2sektor.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]))

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

Help please

Ticotion
Click to expand...
Click to expand...
Click to expand...
 
T

Ticotion

Hi John

Arh I had hoped that you could have given me the missing stones. Anyway If I
try to explain what the aim is maybe you can point me in the right direction.

The query that I'm trying to build is goin to be used for a pareto chart
placed in a report in access.
The pareto chart should show the Frequens [Hyp procent] as bars and the
running sum of the freqvens [run sum hyp procent] as a line for each error
[fejlårsag], in the [sector] and date range that the user choose. So in my
world I would need to build that query in order for the chart to work. Or
maybe this can be done directly in the report? Hope you can help

Br.
Ticotion


John Spencer said:
I don't know of a way to solve your problem based on the information you have
presented.

There is no way to decide which of the two records data 01-01-09 is the first
record and which is the second record. Given that there is no way to generate
a running sum in a query.

It can be done on a report. If you intend to use this information for a
report post back or post in the reports newsgroup with a description of your
data as returned by the query (the original one that works) and specify what
you want to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi

I have modified my query so now it looks like this

SELECT qrySimSektorparetoh.Dato, qrySimSektorparetoh.Sektor,
qrySimSektorparetoh.Fejlårsag, qrySimSektorparetoh.[Fejlen hyppighed],
qrySimSektorparetoh.[Hyp total], qrySimSektorparetoh.[Hyp procent]
(SELECT Sum(qrySimSektorparetoh.[Hyp procent])
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Dato)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16])))
As RunSum
FROM qrySimSektorparetoh
WHERE
(((qrySimSektorparetoh.Dato)=[Forms]![frmSIMpareto]![Kombinationsboks7]) AND
((qrySimSektorparetoh.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]));

The result is that I get the Total sum of Hyp Procent for each record and
not the running sum. What am I doing wrong?

Please Help

Ticotion

Ticotion said:
(Hi John

Thank you for noticing the extra brackets

I'll try to show you want I want based on an exsample. In ( ) I'll include
an english translation of the field name.The Query should return the
following when the user choose Sektor = 5 and date range from 01-01-09 to
03-01-09:

[Dato] (Date) [Sektor](Sector) [Fejlårsag] (Error) [Fejlen
Hyppighed](Frequency)
01-01-09 5 Text1 2
01-01-09 5 Text2 1
02-01-09 5 Text3 5
03-01-09 5 Text4 2


[Hyp Total] (Freq. total) [Hyp procent](Freq. pct) [Acc Hyp] (Acc freq)
10 20 20
10 10 30
10 50 80
10 20 100

So the [Acc Hyp] should accumulate [hyp procent]

Can this be done?

Ticotion


:

First problem is the extra brackets in the Parameters clause.

Next problem is that you should be using a WHERE clause in place of the Having
clause.

PARAMETERS [Forms]![frmSIMpareto]![Kombinationsboks7] DateTime,
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst] AS Fejlårsag
, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS [Fejlen hyppighed]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)

WHERE qrySIM2sektor.Dato Between [Forms]![frmSIMpareto]![Kombinationsboks7]
And [Forms]![frmSIMpareto]![Combo38]
AND qrySIM2sektor.Sektor=[Forms]![frmSIMpareto]![Kombinationsboks16]

GROUP BY qrySIM2sektor.Dato
, qrySIM2sektor.Sektor
, [Maskin navn]+" "+[Afvigelsetekst]
, qrySimSektorparetototal.[Hyp total]
, Round(([Fejlen hyppighed]/[Hyp total])*100,1)

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

That cleans up the query as it exists. The next step is to give us a bit more
detail on what you want to do. A running sum of Hyp Procent based on the Dato
field? Over all the records, over some group of the records and then start over?

You will probably need to use this query as the basis for another query. In
that query you will return the records generated by this query and use a
subquery based on this query to calculate the running sum.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ticotion wrote:
Hi

I have a problem calculating a runing sum for a field in a query. As far as
I can read I need to use a subquery but I can not get this to work. I need to
calculate a runing sum on the field [Hyp procent]

My SQL query is

PARAMETERS [ [Forms]![frmSIMpareto]![Kombinationsboks7]] DateTime, [
[Forms]![frmSIMpareto]![Combo38]] DateTime,
[Forms]![frmSIMpareto]![Kombinationsboks16] Long;

SELECT qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst] AS Fejlårsag, Sum(qrySIM2sektor.[Fejlen hyppighed]) AS
[Fejlen hyppighed], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1) AS [Hyp procent]

FROM qrySimSektorparetototal INNER JOIN qrySIM2sektor ON
(qrySimSektorparetototal.Sektor = qrySIM2sektor.Sektor) AND
(qrySimSektorparetototal.Dato = qrySIM2sektor.Dato)
GROUP BY qrySIM2sektor.Dato, qrySIM2sektor.Sektor, [Maskin navn]+"
"+[Afvigelsetekst], qrySimSektorparetototal.[Hyp total], Round(([Fejlen
hyppighed]/[Hyp total])*100,1)

HAVING (((qrySIM2sektor.Dato) Between
[Forms]![frmSIMpareto]![Kombinationsboks7] And
[Forms]![frmSIMpareto]![Combo38]) AND
((qrySIM2sektor.Sektor)=[Forms]![frmSIMpareto]![Kombinationsboks16]))

ORDER BY qrySIM2sektor.Dato, Sum(qrySIM2sektor.[Fejlen hyppighed]) DESC;

Help please

Ticotion
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab . . .again 3
Report based on two similar queries 1
engine does not recognize <name> as a valid field name or expression.(Error 3070) 3
report group subtotals incorrect 6
Count Field Once 1
Query/Report Grouping and Bad Totals 3
HELP WITH QUERY BY DATE 2
Criteria/If situation? 3

Top