Running Total in SQL query not working as needed

[Guest]

I posted on this subject recently but didn't have all my ducks in a row then,
so I'm reposting with clarifications.

I am producing reports that describe defect rates involved in product
packing. I need to group and sort the defects first by responsible party and
next by defect ID. I have a query that aggregates data from another query
and produces what I need, including count based on defect type and the sum of
all defects (the latter is currently repeated in each row and is just for
calculation purposes). The SQL is as follows:

SELECT First([Order Setup Defects Query].[Defect Description]) AS [Defect
Description Field], Count([Order Setup Defects Query].[Defect Description])
AS DefectCounts, DSum("DefectCounts","[Order Setup defects GRAPH Query]") AS
DefectSum
FROM [Order Setup Defects Query]
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup Defects
Query].[Defect ID]
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];

For the most part this works as desired and generates results along the
lines of these:

Defect Count Sum

Labels Missing 9 30
Docs missing 7 30
Other crap 5 30
Still more crap 5 30
Etc 3 30
Last and least 1 30

My problem is I need to drive a Pareto Chart from this. My chart works well
except for the plot of cumulative percentage. I used an algorithm that seems
to be fairly common in these situations, but it didn't work properly.

The problem is that the algorithm requires a unique ascending or descending
ordinal key of some type when calculating the running total. Based on my
sorting and grouping requirements, I don't have this nor can I see how to
create one on the fly. So I'm desperately hoping someone has an alternative
that will work. This is the only thing stopping my project from being
released.

Thanks in advance for any ideas,

Randall Arnold

 

[Gary Walter]

I posted on this subject recently but didn't have all my ducks in a row
then,
so I'm reposting with clarifications.

I am producing reports that describe defect rates involved in product
packing. I need to group and sort the defects first by responsible party
and
next by defect ID. I have a query that aggregates data from another query
and produces what I need, including count based on defect type and the sum
of
all defects (the latter is currently repeated in each row and is just for
calculation purposes). The SQL is as follows:

SELECT First([Order Setup Defects Query].[Defect Description]) AS [Defect
Description Field], Count([Order Setup Defects Query].[Defect
Description])
AS DefectCounts, DSum("DefectCounts","[Order Setup defects GRAPH Query]")
AS
DefectSum
FROM [Order Setup Defects Query]
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup Defects
Query].[Defect ID]
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];

For the most part this works as desired and generates results along the
lines of these:

Defect Count Sum

Labels Missing 9 30
Docs missing 7 30
Other crap 5 30
Still more crap 5 30
Etc 3 30
Last and least 1 30

My problem is I need to drive a Pareto Chart from this. My chart works
well
except for the plot of cumulative percentage. I used an algorithm that
seems
to be fairly common in these situations, but it didn't work properly.

The problem is that the algorithm requires a unique ascending or
descending
ordinal key of some type when calculating the running total. Based on my
sorting and grouping requirements, I don't have this nor can I see how to
create one on the fly. So I'm desperately hoping someone has an
alternative
that will work. This is the only thing stopping my project from being
released.

Hi Randall,

This is one of those "exceptions to the rule"....

I might suggest creating a table to hold your report data.

Clear it out when you start the process.

Then append data from your query (in "proper order").

I imagine your report table would also have an Autonumber pk field
(and could have a runningsum field also).

After append, run update query to assign value to runningsum
field based on ordinality of autonumber field.

Maybe counterintuitive, but will probably be faster also.

Apologies if misunderstood.

good luck,

gary

 

[Guest]

You have a good idea there. I was actually going to try creating a dummy
table with an Autonumber ordinal that I could link to the query solely for
generating the ascending numbers. I think one way or another I have an idea
now what to do. Thanks!

Randall Arnold

I posted on this subject recently but didn't have all my ducks in a row
then,
so I'm reposting with clarifications.

I am producing reports that describe defect rates involved in product
packing. I need to group and sort the defects first by responsible party
and
next by defect ID. I have a query that aggregates data from another query
and produces what I need, including count based on defect type and the sum
of
all defects (the latter is currently repeated in each row and is just for
calculation purposes). The SQL is as follows:

SELECT First([Order Setup Defects Query].[Defect Description]) AS [Defect
Description Field], Count([Order Setup Defects Query].[Defect
Description])
AS DefectCounts, DSum("DefectCounts","[Order Setup defects GRAPH Query]")
AS
DefectSum
FROM [Order Setup Defects Query]
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup Defects
Query].[Defect ID]
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];

For the most part this works as desired and generates results along the
lines of these:

Defect Count Sum

Labels Missing 9 30
Docs missing 7 30
Other crap 5 30
Still more crap 5 30
Etc 3 30
Last and least 1 30

My problem is I need to drive a Pareto Chart from this. My chart works
well
except for the plot of cumulative percentage. I used an algorithm that
seems
to be fairly common in these situations, but it didn't work properly.

The problem is that the algorithm requires a unique ascending or
descending
ordinal key of some type when calculating the running total. Based on my
sorting and grouping requirements, I don't have this nor can I see how to
create one on the fly. So I'm desperately hoping someone has an
alternative
that will work. This is the only thing stopping my project from being
released.

Hi Randall,

This is one of those "exceptions to the rule"....

I might suggest creating a table to hold your report data.

Clear it out when you start the process.

Then append data from your query (in "proper order").

I imagine your report table would also have an Autonumber pk field
(and could have a runningsum field also).

After append, run update query to assign value to runningsum
field based on ordinality of autonumber field.

Maybe counterintuitive, but will probably be faster also.

Apologies if misunderstood.

good luck,

gary

 

[Guest]

I did some more thinking on this and realized my thought process was still
askew....sigh.

It isn't a series of unique ordinals the running sum function requires to
work-- it's a COLLECTION of like ordinals based on each record grouping (in
my case, defect description). So "Labels Missing" records would all have the
same ordinal (repeated 9 times), "Docs Missing" would have the same ordinal
(next highest in the series), etc. This is how the Where part of the DSum
function is accumulating a running total: it always needs to see ordinal
values less than or equal to the ordinal of the current record.

I could certainly add tables and queries to acheive this, but isn't there a
way I can just get the recrodset position of the last defect matching the
defect of the currently selected record? I could sum up all previous counts
to get the running total that way. Problem is, I'm not sure how to get this
functionality into my query; I have tried DLast but it isn't doing what I
want it to...

Randall Arnold

I posted on this subject recently but didn't have all my ducks in a row
then,
so I'm reposting with clarifications.

I am producing reports that describe defect rates involved in product
packing. I need to group and sort the defects first by responsible party
and
next by defect ID. I have a query that aggregates data from another query
and produces what I need, including count based on defect type and the sum
of
all defects (the latter is currently repeated in each row and is just for
calculation purposes). The SQL is as follows:

SELECT First([Order Setup Defects Query].[Defect Description]) AS [Defect
Description Field], Count([Order Setup Defects Query].[Defect
Description])
AS DefectCounts, DSum("DefectCounts","[Order Setup defects GRAPH Query]")
AS
DefectSum
FROM [Order Setup Defects Query]
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup Defects
Query].[Defect ID]
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];

For the most part this works as desired and generates results along the
lines of these:

Defect Count Sum

Labels Missing 9 30
Docs missing 7 30
Other crap 5 30
Still more crap 5 30
Etc 3 30
Last and least 1 30

My problem is I need to drive a Pareto Chart from this. My chart works
well
except for the plot of cumulative percentage. I used an algorithm that
seems
to be fairly common in these situations, but it didn't work properly.

The problem is that the algorithm requires a unique ascending or
descending
ordinal key of some type when calculating the running total. Based on my
sorting and grouping requirements, I don't have this nor can I see how to
create one on the fly. So I'm desperately hoping someone has an
alternative
that will work. This is the only thing stopping my project from being
released.

Hi Randall,

This is one of those "exceptions to the rule"....

I might suggest creating a table to hold your report data.

Clear it out when you start the process.

Then append data from your query (in "proper order").

I imagine your report table would also have an Autonumber pk field
(and could have a runningsum field also).

After append, run update query to assign value to runningsum
field based on ordinality of autonumber field.

Maybe counterintuitive, but will probably be faster also.

Apologies if misunderstood.

good luck,

gary

 

[Gary Walter]

Hi Randall,

I confess I do not know what a
Pareto Chart is (nor any algorithm for it).
Plus demands at work leave me with
little time or bandwidth at this time to
learn. Sorry....

I thought you were after a cumulative sum,
such that if you had the results of your example
in a table it might end up looking like:

tblReport:
AutoNum Defect Count CumSum Sum
1 Labels Missing 9 9 30
2 Docs missing 7 16 30
3 Other crap 5 21 30
4 Still more crap 5 26 30
5 Etc 3 29 30
6 Last and least 1 30 30

where you appended Defect, Count, and Sum
from your query to an empty tblReport,
then ran an update query like:

UPDATE tblReport
SET CumSum =
DSum("Count","tblReport","[AutoNum]<=" & tblReport.AutoNum);

In my experience, because domain functions can
be so slow, I have found that using a report table
(especially if going to do further calcs or sorting
in report) will actually speed up the process.

Hopefully the experts will not ignore your post
because I have responded. If you do not receive
any other suggestions, it might be best to repost
for that very reason.

Apologies,

gary

I did some more thinking on this and realized my thought process was still
askew....sigh.

It isn't a series of unique ordinals the running sum function requires to
work-- it's a COLLECTION of like ordinals based on each record grouping
(in
my case, defect description). So "Labels Missing" records would all have
the
same ordinal (repeated 9 times), "Docs Missing" would have the same
ordinal
(next highest in the series), etc. This is how the Where part of the DSum
function is accumulating a running total: it always needs to see ordinal
values less than or equal to the ordinal of the current record.

I could certainly add tables and queries to acheive this, but isn't there
a
way I can just get the recrodset position of the last defect matching the
defect of the currently selected record? I could sum up all previous
counts
to get the running total that way. Problem is, I'm not sure how to get
this
functionality into my query; I have tried DLast but it isn't doing what I
want it to...

Randall Arnold

I posted on this subject recently but didn't have all my ducks in a row
then,
so I'm reposting with clarifications.

I am producing reports that describe defect rates involved in product
packing. I need to group and sort the defects first by responsible
party
and
next by defect ID. I have a query that aggregates data from another
query
and produces what I need, including count based on defect type and the
sum
of
all defects (the latter is currently repeated in each row and is just
for
calculation purposes). The SQL is as follows:

SELECT First([Order Setup Defects Query].[Defect Description]) AS
[Defect
Description Field], Count([Order Setup Defects Query].[Defect
Description])
AS DefectCounts, DSum("DefectCounts","[Order Setup defects GRAPH
Query]")
AS
DefectSum
FROM [Order Setup Defects Query]
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup
Defects
Query].[Defect ID]
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];

For the most part this works as desired and generates results along the
lines of these:

Defect Count Sum

Labels Missing 9 30
Docs missing 7 30
Other crap 5 30
Still more crap 5 30
Etc 3 30
Last and least 1 30

My problem is I need to drive a Pareto Chart from this. My chart works
well
except for the plot of cumulative percentage. I used an algorithm that
seems
to be fairly common in these situations, but it didn't work properly.

The problem is that the algorithm requires a unique ascending or
descending
ordinal key of some type when calculating the running total. Based on
my
sorting and grouping requirements, I don't have this nor can I see how
to
create one on the fly. So I'm desperately hoping someone has an
alternative
that will work. This is the only thing stopping my project from being
released.

Hi Randall,

This is one of those "exceptions to the rule"....

I might suggest creating a table to hold your report data.

Clear it out when you start the process.

Then append data from your query (in "proper order").

I imagine your report table would also have an Autonumber pk field
(and could have a runningsum field also).

After append, run update query to assign value to runningsum
field based on ordinality of autonumber field.

Maybe counterintuitive, but will probably be faster also.

Apologies if misunderstood.

good luck,

gary

 

[Guest]

It looks like you understood my request, Gary. Pareto charts are a
combination histogram and line chart; a single line plots cumulative
percentage of defects. I needed a running total to determine cumulative
percentage.

After much fruitless work on this, it finally dawned on me yesterday that,
based on our sorting and grouping needs, there is no way based on my present
knowledge to get a running total working within the query. Or if it is
somehow possible (and it would take more tables and queries than I care to
implement if so) it would get too messy. Therefore, I am placing dummy data
into the cumulative percentage column and then using the report's OnFormat
event to manipulate the contents of that column so that they become a true
cumulative percentage. I actually got this to work 100% so I am happy with
the results even as I'm disgusted with the solution... lol.

Thanks for your contributions, though, your comments did get me to thinking
and certainly helped me figure out what needed to be done (and what couldn't
possibly work). ; )

Randall

Hi Randall,

I confess I do not know what a
Pareto Chart is (nor any algorithm for it).
Plus demands at work leave me with
little time or bandwidth at this time to
learn. Sorry....

I thought you were after a cumulative sum,
such that if you had the results of your example
in a table it might end up looking like:

tblReport:
AutoNum Defect Count CumSum Sum
1 Labels Missing 9 9 30
2 Docs missing 7 16 30
3 Other crap 5 21 30
4 Still more crap 5 26 30
5 Etc 3 29 30
6 Last and least 1 30 30

where you appended Defect, Count, and Sum
from your query to an empty tblReport,
then ran an update query like:

UPDATE tblReport
SET CumSum =
DSum("Count","tblReport","[AutoNum]<=" & tblReport.AutoNum);

In my experience, because domain functions can
be so slow, I have found that using a report table
(especially if going to do further calcs or sorting
in report) will actually speed up the process.

Hopefully the experts will not ignore your post
because I have responded. If you do not receive
any other suggestions, it might be best to repost
for that very reason.

Apologies,

gary

I did some more thinking on this and realized my thought process was still
askew....sigh.

It isn't a series of unique ordinals the running sum function requires to
work-- it's a COLLECTION of like ordinals based on each record grouping
(in
my case, defect description). So "Labels Missing" records would all have
the
same ordinal (repeated 9 times), "Docs Missing" would have the same
ordinal
(next highest in the series), etc. This is how the Where part of the DSum
function is accumulating a running total: it always needs to see ordinal
values less than or equal to the ordinal of the current record.

I could certainly add tables and queries to acheive this, but isn't there
a
way I can just get the recrodset position of the last defect matching the
defect of the currently selected record? I could sum up all previous
counts
to get the running total that way. Problem is, I'm not sure how to get
this
functionality into my query; I have tried DLast but it isn't doing what I
want it to...

Randall Arnold

:
I posted on this subject recently but didn't have all my ducks in a row
then,
so I'm reposting with clarifications.

I am producing reports that describe defect rates involved in product
packing. I need to group and sort the defects first by responsible
party
and
next by defect ID. I have a query that aggregates data from another
query
and produces what I need, including count based on defect type and the
sum
of
all defects (the latter is currently repeated in each row and is just
for
calculation purposes). The SQL is as follows:

SELECT First([Order Setup Defects Query].[Defect Description]) AS
[Defect
Description Field], Count([Order Setup Defects Query].[Defect
Description])
AS DefectCounts, DSum("DefectCounts","[Order Setup defects GRAPH
Query]")
AS
DefectSum
FROM [Order Setup Defects Query]
GROUP BY [Order Setup Defects Query].Responsibility, [Order Setup
Defects
Query].[Defect ID]
ORDER BY Count([Order Setup Defects Query].[Defect Description]) DESC ,
[Order Setup Defects Query].Responsibility DESC , [Order Setup Defects
Query].[Defect ID];

For the most part this works as desired and generates results along the
lines of these:

Defect Count Sum

Labels Missing 9 30
Docs missing 7 30
Other crap 5 30
Still more crap 5 30
Etc 3 30
Last and least 1 30

My problem is I need to drive a Pareto Chart from this. My chart works
well
except for the plot of cumulative percentage. I used an algorithm that
seems
to be fairly common in these situations, but it didn't work properly.

The problem is that the algorithm requires a unique ascending or
descending
ordinal key of some type when calculating the running total. Based on
my
sorting and grouping requirements, I don't have this nor can I see how
to
create one on the fly. So I'm desperately hoping someone has an
alternative
that will work. This is the only thing stopping my project from being
released.

Hi Randall,

This is one of those "exceptions to the rule"....

I might suggest creating a table to hold your report data.

Clear it out when you start the process.

Then append data from your query (in "proper order").

I imagine your report table would also have an Autonumber pk field
(and could have a runningsum field also).

After append, run update query to assign value to runningsum
field based on ordinality of autonumber field.

Maybe counterintuitive, but will probably be faster also.

Apologies if misunderstood.

good luck,

gary