Re: running sum in a query for a calculated field

Thanks so much, I sure appreicate you hanging in there with me. I have put
that code into my query and now I just need one more thing. I need a line
chart that shows planned versus actual by (cumulative) month so what I have
now is:

Planned Completed myMonths dir
21 21 10/1/2007 E
46 47 11/1/2007 E
58 59 12/1/2007 E
61 62 1/1/2008 E
62 63 2/1/2008 E
63 0 7/1/2008 E
64 64 12/1/2008 E

And what I need is:

Planned Completed myMonths dir
21 21 10/1/2007 E
46 47 11/1/2007 E
58 59 12/1/2007 E
61 62 1/1/2008 E
62 63 2/1/2008 E
63 63 7/1/2008 E
64 64 12/1/2008 E

How can I do that?



"Michel Walsh" wrote:

Use Nz on the 'right side' table, like:


SELECT Nz(testingplanned.Planned, 0),
Nz(testingactual.Completed, 0),
AllDates.myMonths,
AllDates.dir
FROM (AllDates LEFT JOIN testingplanned ON AllDates.myMonths =
testingplanned.myMonths) LEFT JOIN testingactual ON AllDates.myMonths =
testingactual.myMonths;


that will change the NULL to a zero. You can, next, make the running sum ( I
am lost at where, which step exactly) we are, but I assume it is before
making the running sum).



Vanderghast, Access MVP


"SMT" <SMT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AF775586-8F5D-499E-8FBC-F1042B51F631@xxxxxxxxxxxxxxxx
Well I am getting there and I sure do appreciate your assistance but I
need a
little more. Below is the results after your last suggestions. I made one
adjustment and added the Dir to the union select. So I have all the dates
showing however for the months that have a planned and no actual or vice
versa I need to have that month be a 0 and the cumulative count continue
down. So for example below for the date of 7/01 the completed sum should
be
62 for 11/01 the planned should be 64.

SQL SELECT testingplanned.Planned, testingactual.Completed,
AllDates.myMonths, AllDates.dir
FROM (AllDates LEFT JOIN testingplanned ON AllDates.myMonths =
testingplanned.myMonths) LEFT JOIN testingactual ON AllDates.myMonths =
testingactual.myMonths;

Results
Planned Completed myMonths dir
21 20 10/1/2007 E
46 46 11/1/2007 E
58 58 12/1/2007 E
61 61 1/1/2008 E
62 62 2/1/2008 E
63 7/1/2008 E
64 63 10/1/2008 E
64 11/1/2008 E

"Michel Walsh" wrote:

You could use a full outer join, but that is not supported by Jet. You
can
simulate it, though.

Make a first query will all the 'dates':

SELECT myMonths FROM testingplanned
UNION
SELECT myMonths FROM testingactual



save it, say, under the name AllDates.


Then, use:



SELECT testingplanned.Planned,
testingactual.Completed,
testingplanned.Dir,
testingactual.Dir,
testingplanned.myMonths,
testingactual.myMonths
FROM (AllDates LEFT JOIN testingplanned
ON AllDate.myMonths = testingplanned.myMonths)
LEFT JOIN testingactual
ON AllDates.myMonths = testingactual.myMonths





Hoping it may help,
Vanderghast, Access MVP


"SMT" <SMT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3CBC272B-368F-4F36-9628-40A6B45D076A@xxxxxxxxxxxxxxxx
Thanks again, that worked just fine. Now I created the query from those
two
queries, however if I have a month were I have a planned but no
completed
or
vice versa then it doesnt show unless they are equal. See my SQL below
and
please tell me how I can get the month to show even if it may not have
both a
planned and completed.

SELECT testingplanned.Planned, testingactual.Completed,
testingplanned.Dir,
testingactual.Dir, testingplanned.myMonths, testingactual.myMonths
FROM testingplanned INNER JOIN testingactual ON testingplanned.myMonths
=
testingactual.myMonths;

Thanks again for all you help, it is greatly appreciated.



"Michel Walsh" wrote:

You are not doing anything wrong, Access query designer is not able to
display not-equi join, graphically ( a join implying something else
than
the operator = is a "not-equi join").

You can give alias to expression adding AS and the name you want, in
the
SELECT clause:


SELECT a.myMonths AS theMonth,
LAST(a.monthCount) AS countThisMonth,
SUM(b.monthCount) AS cumulativeMonth,
LAST(a.dir) AS thisDir
FROM qSUM AS a INNER JOIN qSum AS b
ON a.myMonths >= b.myMonths
GROUP BY a.myMonths
ORDER BY a.myMonths DESC;




You can make another query using your two different queries, to join
them
through their common field theMonth. You can probably use the
graphical
interface, in that last case.



Hoping it may help,
Vanderghast, Access MVP


"SMT" <SMT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6093519D-C002-4312-9861-365C4FA75517@xxxxxxxxxxxxxxxx
Hello,

I thank you for the help. I created the queries as you listed below
but
since I have a Planned and an Completed date (field name of
completed
date
is Date published_presented) I created two queries. qsum and in
that
query I
used exactly as you have listed below but changed monthCount to
Planned
and
then I made another query, qsum2 for the completed date and changed
monthCount to Completed. The I made the second queries changing
monthCount
to
Planned and Completed as required. The calculations seem to be
working
correctly, however only the myMonth shows as a field title the other
titles
are Expr1001, Expr1002, Expr1003 (in both of the secondary queries).
And
I
cant get into the design view (can only view the sql). I get error
Microsoft
Office Access cant represent the join expression a.myMonths.
>=b.myMonths
in
Design View. What am I doing wrong there.

WHen I get this correct I think I need to do a join so that I can
get
both
of the cumulative values for planned and completed in one query so
that
I
can
make a line graph comparison of planned and completed through the
months.
That is my ultimate goal.

Thanks for all your help so far, it has gotten me very very close.

"Michel Walsh" wrote:

I would use a second query.


I would also change your actual query to:


SELECT planneddate+1-DatePart("d", planneddate) AS myMonths,
Count(*) AS monthCount,
dir
FROM qryMaintbl
WHERE (((qryMaintbl.dir) Like [Enter Directorate]))
GROUP BY planneddate+1-DatePart("d", planneddate) , dir

(saved under the name qSum). I kept REAL dates, for the month, so
I
can
easily navigate over years, and the month ordering will not depend
on
their
alphabetical NAME ordering.


Then, the new query, the one you will call to see the results:
------------------------------------
SELECT a.myMonths, LAST(a.monthCount), SUM(b.monthCount),
LAST(a.dir)
FROM qSUM AS a INNER JOIN qSum AS b
ON a.myMonths >= b.myMonths
GROUP BY a.myMonths
ORDER BY a.myMonths DESC;
------------------------------------

And note that now, December will be BETWEEN January and November,
as
I
assume you want it, really, AND that the running sum will be
historical,
ie,
starting form the oldest date and growing up, with time.




Hoping it may help,
Vanderghast, Access MVP



"SMT" <SMT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:84654F36-932B-4A77-8C66-CBECAAEFEC35@xxxxxxxxxxxxxxxx
I have a qry to sum the planneddate field in my maintbl and groups
it
by
month. I need the sum of those grouped totals in a cumulative
format.
See
my
sql from the qry and please help me get this column to be
cumulative

SELECT Format([planneddate],"mmm") AS PlannedlCumulativeMonth,
Count(*)
AS
PlannedlCumulativeCount, qryMaintbl.dir
FROM qryMaintbl
GROUP BY Format([planneddate],"mmm"), qryMaintbl.dir,
Month([planneddate])
HAVING (((qryMaintbl.dir) Like [Enter Directorate]))
ORDER BY Format([planneddate],"mmm");


SO out of this qry I get :


Dec 11 E
Jan 3 E
Nov 23 E
Oct 13 E

So I need

Dec 11 E
Jan 14 E
Nov 37 E
Oct 50 E
Any way to do this?














.

Relevant Pages

  • Re: running sum in a query for a calculated field
    ... Make a first query like: ... make the running sum, in the query that does the said running sum. ... FROM qSUM AS a INNER JOIN qSum AS b ... PlannedlCumulativeCount, qryMaintbl.dir ...
    (microsoft.public.access.queries)
  • Re: running sum in a query for a calculated field
    ... FROM qSUM AS a INNER JOIN qSum AS b ... You can make another query using your two different queries, ... PlannedlCumulativeCount, qryMaintbl.dir ...
    (microsoft.public.access.queries)
  • Re: running sum in a query for a calculated field
    ... FROM qSUM AS a INNER JOIN qSum AS b ... You can make another query using your two different queries, ... PlannedlCumulativeCount, qryMaintbl.dir ...
    (microsoft.public.access.queries)
  • Re: running sum in a query for a calculated field
    ... that code into my query and now I just need one more thing. ... SUMAS cumulativeMonth, ... FROM qSUM AS a INNER JOIN qSum AS b ... PlannedlCumulativeCount, qryMaintbl.dir ...
    (microsoft.public.access.queries)
  • Re: running sum in a query for a calculated field
    ... FROM qSUM AS a INNER JOIN qSum AS b ... You can make another query using your two different queries, ... PlannedlCumulativeCount, qryMaintbl.dir ...
    (microsoft.public.access.queries)
Loading