-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You've got a problem. Usually, a running (cumulative) total can be
calculated in a query this way:
SELECT Rep_ID, WeekNo, SUM(RepSales) As SalesSum,
(SELECT SUM(RepSales)
FROM Sales
WHERE Rep_ID = S.Rep_ID AND WeekNo <= S.WeekNO ) As Cumulative
FROM Sales As S
WHERE ... < your criteria > ...
ORDER BY Rep_ID, WeekNo
The subquery in the SELECT clause will calc the running total for the
column RepSales for the query's "current" Rep_ID and WeekNo.
The trouble is you want something like this:
SELECT Rep_ID, WeekNo, VAR(RepSales) As Variance,
(SELECT SUM(VAR(RepSales))
FROM Sales
WHERE Rep_ID = S.Rep_ID AND WeekNo <= S.WeekNO ) As Cumulative
FROM Sales As S
.... etc. ...
Which can't be done, because SQL doesn't allow an aggregate function
to have another aggregate function as part of it's expression (what's
inside the parentheses). So, you'll probably have to run 2 queries to
get the results:
qryVariance:
SELECT Rep_ID, WeekNo, VAR(RepSales) As Variance,
FROM Sales As S
WHERE ... < your criteria > ...
GROUP BY Rep_ID, WeekNo
qryVarianceRunningSum:
SELECT Rep_ID, WeekNo, Variance,
(SELECT SUM(Variance)
FROM qryVariance
WHERE Rep_ID = V.Rep_ID AND WeekNo <= V.WeekNO ) As Cumulative
FROM qryVariance AS V
ORDER BY Rep_ID, WeekNo
You can also substitute the DSum() function for the subquery:
DSum("Variance", "qryVariance",
"Rep_ID=V.Rep_ID AND WeekNo <= V.WeekNO")
Not sure if this will work ('cuz of summing a variance column in
another query). If it doesn't you may want to save the results of
qryVariance to a temp. table and then change qryVarianceRunningSum to
this:
SELECT Rep_ID, WeekNo, Variance, SUM(Variance) As Cumulative
FROM tblVariance_tmp AS V
GROUP BY Rep_ID, WeekNo, Variance
I've found that using a subquery in the SELECT statement of a query
causes Access report's to reject the query, even though the query runs
from query design view! That's when using a domain aggregate function
(e.g.: DSum()) is needed.
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP6qwioechKqOuFEgEQJtRwCfV1e1/ubXicEAWetPXZVBKwiQDLYAn1Ct
gstDlvVS8OF9AAp7i9Y1+l9I
=ZftM
-----END PGP SIGNATURE-----
Matt Larkin wrote:
I am pulling my hair out trying to solve this one (presumably because
I am not particularly trained or skilled at access!)
I have a query which summarises the variances that each of my sales
guys have created from their targets. This summarises by week, and
works fine. I then re-use this into a cross-tab which presents a nice
orderly table.
My boss would like to see these variances accumulate, so that we could
make a nice graph in Excel of who is doing well against the baseline
target, and who isn't.
I can't seem to find a way of creating a running total of the
variances that are being created.
e.g
Rep_ID Week No Variance Cumulative
1 1 0.01 0.01
1 2 -0.03 -0.02
1 3 0.01 -0.01
etc etc
I've seen some discussion of Dsum as the tool for this, but I think I
may be missing something......
Any pointers gratefully received!
Matt
UK
