running sum in a query for a calculated field

[SMT]

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?

 

[Michel Walsh]

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]

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.

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

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?

 

[Michel Walsh]

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

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.

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

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?

 

[SMT]

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.

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

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.

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



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?

 

[Michel Walsh]

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

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.

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

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.

:

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



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?

 

[SMT]

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

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

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.

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


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.

:

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



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?

 

[Michel Walsh]

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

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

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

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.



:

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


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.

:

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



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?

 

[SMT]

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?

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

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

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


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.



:

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


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.

:

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



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?

 

[SMT]

I dont know if I really made clear what my problem was now after reading my
post again this morning but where the 0 for completed is for 7/1/2008, I
really need to see 63 which is the previous months totals brought forth, so
my graph will plot correctly. So everywhere this is a 0 or no entry I need
the total brought forth so it continues a running sum throughout.

thanks
Susie

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?

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

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

:

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


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.



:

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


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.

:

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



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");

 

[Michel Walsh]

Would be easier if you can introduce the (missing) dates BEFORE making any
sum. In fact, to introduce them as the first step of the whole process.


Make a first query like:

SELECT allDates.myMonths, Nz(Completed, 0) AS newCompleted
FROM allDates LEFT JOIN yourtable
OIN allDates.myMonths = yourTable.myMonths



This query will so have all the required dates, with an amount of zero for
the dates that were missing. Now, use THAT query, instead of 'yourtable' to
make the running sum, in the query that does the said running sum.




Hoping it may help,
Vanderghast, Access MVP

I dont know if I really made clear what my problem was now after reading my
post again this morning but where the 0 for completed is for 7/1/2008, I
really need to see 63 which is the previous months totals brought forth,
so
my graph will plot correctly. So everywhere this is a 0 or no entry I need
the total brought forth so it continues a running sum throughout.

thanks
Susie

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?

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


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

:

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


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.



:

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


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.

:

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



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");

 

[SMT]

I am confused here. so first step I have QSum (for planned) and QSum2 (for
Completed) See Qsum below (for Qsum2 replaced planneddate with Completed
where appropriate)

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

2nd step

Testingplanned and testingactual queries. See testingplanned below (for
testingactual replaced Planned (or planned ref) with Completed where
appropriate)

SELECT a.myMonths, LAST(a.planned) AS plannedthismonth, SUM(b.planned) AS
Planned, LAST(a.dir) AS Dir
FROM qSUM AS a INNER JOIN qSum AS b ON a.myMonths>=b.myMonths
GROUP BY a.myMonths
ORDER BY a.myMonths;

3rd Step

alldates union query
SELECT myMonths, dir FROM testingplanned
UNION SELECT myMonths, dir FROM testingactual;


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


Where was I to put the code that you provided below.

thanks
Susie

 

[Michel Walsh]

Step 0_1 would be to get all date:

SELECT planneddate+1-DatePart("d",planneddate) AS myMonths FROM qryMaintbl
UNION
SELECT testingdate +1-DatePart("d",testingdate ) FROM qryTestingTbl

I am not sure about the last SELECT, it should involves the table/query
supplying the second set of dates, and the field.


Step 0_2 would be to enter missing dates into qryMaintbl:


SELECT query0_1.myMonths, planned, dir
FROM query0_1 LEFT JOIN qryMaintbl
ON query0_1.myMonths = qryMaintbl.myMonths




Step 0_3 would be to enter missing dates in qryTestingTbl (or whatever)



Steps 1


SELECT planneddate+1-DatePart("d",planneddate) AS myMonths,
Count(planned) AS Planned,
dir
FROM query0_1
WHERE (((query0_1.dir) Like [Enter Directorate]))
GROUP BY planneddate+1-DatePart("d",planneddate), query0_1.dir;


and 2, as actual


Step 3, not usefull anymore, since it is already done at step 0_1 (and
0_2).

Step 4:

SELECT testingplanned.Planned AS Planned,
testingactual.Completed AS Completed,
testingplanned.myMonths, testingplanned.dir
FROM testingplanned INNER JOIN testingactual
ON testingplanned .myMonths = testingactual.myMonths


we don't need the outer join, now, since all the dates ARE in both tables
(since 0_1 and 0_2)




Hoping it may help,
Vanderghast, Access MVP

I am confused here. so first step I have QSum (for planned) and QSum2 (for
Completed) See Qsum below (for Qsum2 replaced planneddate with Completed
where appropriate)

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

2nd step

Testingplanned and testingactual queries. See testingplanned below (for
testingactual replaced Planned (or planned ref) with Completed where
appropriate)

SELECT a.myMonths, LAST(a.planned) AS plannedthismonth, SUM(b.planned) AS
Planned, LAST(a.dir) AS Dir
FROM qSUM AS a INNER JOIN qSum AS b ON a.myMonths>=b.myMonths
GROUP BY a.myMonths
ORDER BY a.myMonths;

3rd Step

alldates union query
SELECT myMonths, dir FROM testingplanned
UNION SELECT myMonths, dir FROM testingactual;


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


Where was I to put the code that you provided below.

thanks
Susie

Would be easier if you can introduce the (missing) dates BEFORE making
any
sum. In fact, to introduce them as the first step of the whole process.


Make a first query like:

SELECT allDates.myMonths, Nz(Completed, 0) AS newCompleted
FROM allDates LEFT JOIN yourtable
OIN allDates.myMonths = yourTable.myMonths



This query will so have all the required dates, with an amount of zero
for
the dates that were missing. Now, use THAT query, instead of 'yourtable'
to
make the running sum, in the query that does the said running sum.




Hoping it may help,
Vanderghast, Access MVP

 

[SMT]

Ok, I am sorry but I am still confused.

1st step

I created query0_1 (see sql) and it contains all the dates from both planned
and actual (date Published_presented).

SELECT planneddate+1-DatePart("d",planneddate) AS myMonths FROM qryMaintbl
UNION SELECT [Date Published_presented] +1-DatePart("d",[Date
Published_presented]) FROM qryMaintbl;

I dont understand 0_2. I dont have planned or dir in query0_1 so will the
select work? I get error "The specified field qryMaintbl.myMonths could
refer to more than one table listed in the FROM clause of your SQL statement"

SELECT query0_1.myMonths, planned, dir
FROM query0_1 LEFT JOIN qryMaintbl
ON query0_1.myMonths = qryMaintbl.myMonths

Thanks for your presistance in helping me solve this problem. It is much
appreciated.

Step 0_1 would be to get all date:

SELECT planneddate+1-DatePart("d",planneddate) AS myMonths FROM qryMaintbl
UNION
SELECT testingdate +1-DatePart("d",testingdate ) FROM qryTestingTbl

I am not sure about the last SELECT, it should involves the table/query
supplying the second set of dates, and the field.


Step 0_2 would be to enter missing dates into qryMaintbl:


SELECT query0_1.myMonths, planned, dir
FROM query0_1 LEFT JOIN qryMaintbl
ON query0_1.myMonths = qryMaintbl.myMonths




Step 0_3 would be to enter missing dates in qryTestingTbl (or whatever)



Steps 1


SELECT planneddate+1-DatePart("d",planneddate) AS myMonths,
Count(planned) AS Planned,
dir
FROM query0_1
WHERE (((query0_1.dir) Like [Enter Directorate]))
GROUP BY planneddate+1-DatePart("d",planneddate), query0_1.dir;


and 2, as actual


Step 3, not usefull anymore, since it is already done at step 0_1 (and
0_2).

Step 4:

SELECT testingplanned.Planned AS Planned,
testingactual.Completed AS Completed,
testingplanned.myMonths, testingplanned.dir
FROM testingplanned INNER JOIN testingactual
ON testingplanned .myMonths = testingactual.myMonths


we don't need the outer join, now, since all the dates ARE in both tables
(since 0_1 and 0_2)




Hoping it may help,
Vanderghast, Access MVP

I am confused here. so first step I have QSum (for planned) and QSum2 (for
Completed) See Qsum below (for Qsum2 replaced planneddate with Completed
where appropriate)

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

2nd step

Testingplanned and testingactual queries. See testingplanned below (for
testingactual replaced Planned (or planned ref) with Completed where
appropriate)

SELECT a.myMonths, LAST(a.planned) AS plannedthismonth, SUM(b.planned) AS
Planned, LAST(a.dir) AS Dir
FROM qSUM AS a INNER JOIN qSum AS b ON a.myMonths>=b.myMonths
GROUP BY a.myMonths
ORDER BY a.myMonths;

3rd Step

alldates union query
SELECT myMonths, dir FROM testingplanned
UNION SELECT myMonths, dir FROM testingactual;


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


Where was I to put the code that you provided below.

thanks
Susie

Would be easier if you can introduce the (missing) dates BEFORE making
any
sum. In fact, to introduce them as the first step of the whole process.


Make a first query like:

SELECT allDates.myMonths, Nz(Completed, 0) AS newCompleted
FROM allDates LEFT JOIN yourtable
OIN allDates.myMonths = yourTable.myMonths



This query will so have all the required dates, with an amount of zero
for
the dates that were missing. Now, use THAT query, instead of 'yourtable'
to
make the running sum, in the query that does the said running sum.




Hoping it may help,
Vanderghast, Access MVP



I dont know if I really made clear what my problem was now after reading
my
post again this morning but where the 0 for completed is for 7/1/2008,
I
really need to see 63 which is the previous months totals brought
forth,
so
my graph will plot correctly. So everywhere this is a 0 or no entry I
need
the total brought forth so it continues a running sum throughout.

thanks
Susie

:

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?



:

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


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

:

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

 

[Michel Walsh]

query0_1 should only have one field, myMonths. Planned and dir should come
from qryMaintbl.

I assumed myMonths exist in qryMaintbl, and that the dates are already
'pushed' at the first on the month, if not, try:



SELECT query0_1.myMonths, qryMaintbl.planned, qryMaintbl.dir
FROM query0_1 LEFT JOIN qryMaintbl
ON query0_1.myMonths =
qryMaintbl.planneddate1-DatePart("d",qryMaintbl.planneddate)


where the modifications are: I specified the table in front of each field,
I compute the first of the month based on the
planneddate, in the ON clause


(Again, that last modification is not required if the dates are already the
first of the month).


The intended result is:

each row from the original qryMaintbl
plus
rows with dates that were previously missing, with NULL under planned
and dir fields, for these new rows.





Vanderghast, Access MVP

Ok, I am sorry but I am still confused.

1st step

I created query0_1 (see sql) and it contains all the dates from both
planned
and actual (date Published_presented).

SELECT planneddate+1-DatePart("d",planneddate) AS myMonths FROM
qryMaintbl
UNION SELECT [Date Published_presented] +1-DatePart("d",[Date
Published_presented]) FROM qryMaintbl;

I dont understand 0_2. I dont have planned or dir in query0_1 so will the
select work? I get error "The specified field qryMaintbl.myMonths could
refer to more than one table listed in the FROM clause of your SQL
statement"

SELECT query0_1.myMonths, planned, dir
FROM query0_1 LEFT JOIN qryMaintbl
ON query0_1.myMonths = qryMaintbl.myMonths

Thanks for your presistance in helping me solve this problem. It is much
appreciated.

Step 0_1 would be to get all date:

SELECT planneddate+1-DatePart("d",planneddate) AS myMonths FROM
qryMaintbl
UNION
SELECT testingdate +1-DatePart("d",testingdate ) FROM qryTestingTbl

I am not sure about the last SELECT, it should involves the table/query
supplying the second set of dates, and the field.


Step 0_2 would be to enter missing dates into qryMaintbl:


SELECT query0_1.myMonths, planned, dir
FROM query0_1 LEFT JOIN qryMaintbl
ON query0_1.myMonths = qryMaintbl.myMonths




Step 0_3 would be to enter missing dates in qryTestingTbl (or whatever)



Steps 1


SELECT planneddate+1-DatePart("d",planneddate) AS myMonths,
Count(planned) AS Planned,
dir
FROM query0_1
WHERE (((query0_1.dir) Like [Enter Directorate]))
GROUP BY planneddate+1-DatePart("d",planneddate), query0_1.dir;


and 2, as actual


Step 3, not usefull anymore, since it is already done at step 0_1 (and
0_2).

Step 4:

SELECT testingplanned.Planned AS Planned,
testingactual.Completed AS Completed,
testingplanned.myMonths, testingplanned.dir
FROM testingplanned INNER JOIN testingactual
ON testingplanned .myMonths = testingactual.myMonths


we don't need the outer join, now, since all the dates ARE in both
tables
(since 0_1 and 0_2)




Hoping it may help,
Vanderghast, Access MVP

I am confused here. so first step I have QSum (for planned) and QSum2
(for
Completed) See Qsum below (for Qsum2 replaced planneddate with
Completed
where appropriate)

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

2nd step

Testingplanned and testingactual queries. See testingplanned below (for
testingactual replaced Planned (or planned ref) with Completed where
appropriate)

SELECT a.myMonths, LAST(a.planned) AS plannedthismonth, SUM(b.planned)
AS
Planned, LAST(a.dir) AS Dir
FROM qSUM AS a INNER JOIN qSum AS b ON a.myMonths>=b.myMonths
GROUP BY a.myMonths
ORDER BY a.myMonths;

3rd Step

alldates union query
SELECT myMonths, dir FROM testingplanned
UNION SELECT myMonths, dir FROM testingactual;


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


Where was I to put the code that you provided below.

thanks
Susie






:

Would be easier if you can introduce the (missing) dates BEFORE making
any
sum. In fact, to introduce them as the first step of the whole
process.


Make a first query like:

SELECT allDates.myMonths, Nz(Completed, 0) AS newCompleted
FROM allDates LEFT JOIN yourtable
OIN allDates.myMonths = yourTable.myMonths



This query will so have all the required dates, with an amount of zero
for
the dates that were missing. Now, use THAT query, instead of
'yourtable'
to
make the running sum, in the query that does the said running sum.




Hoping it may help,
Vanderghast, Access MVP



I dont know if I really made clear what my problem was now after
reading
my
post again this morning but where the 0 for completed is for
7/1/2008,
I
really need to see 63 which is the previous months totals brought
forth,
so
my graph will plot correctly. So everywhere this is a 0 or no entry
I
need
the total brought forth so it continues a running sum throughout.

thanks
Susie

:

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?



:

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


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

:

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]

I wanted to tell you thank you for all your help and persistence in helping
me solve my problem. I now have a query group by month with my planned and
completed cumulative totals (and the cumulative values work correctly for
months that dont have both planned and completed) and I am able to make the
graph needed. Your help is much appreciated!

query0_1 should only have one field, myMonths. Planned and dir should come
from qryMaintbl.

I assumed myMonths exist in qryMaintbl, and that the dates are already
'pushed' at the first on the month, if not, try:



SELECT query0_1.myMonths, qryMaintbl.planned, qryMaintbl.dir
FROM query0_1 LEFT JOIN qryMaintbl
ON query0_1.myMonths =
qryMaintbl.planneddate1-DatePart("d",qryMaintbl.planneddate)


where the modifications are: I specified the table in front of each field,
I compute the first of the month based on the
planneddate, in the ON clause


(Again, that last modification is not required if the dates are already the
first of the month).


The intended result is:

each row from the original qryMaintbl
plus
rows with dates that were previously missing, with NULL under planned
and dir fields, for these new rows.





Vanderghast, Access MVP

Ok, I am sorry but I am still confused.

1st step

I created query0_1 (see sql) and it contains all the dates from both
planned
and actual (date Published_presented).

SELECT planneddate+1-DatePart("d",planneddate) AS myMonths FROM
qryMaintbl
UNION SELECT [Date Published_presented] +1-DatePart("d",[Date
Published_presented]) FROM qryMaintbl;

I dont understand 0_2. I dont have planned or dir in query0_1 so will the
select work? I get error "The specified field qryMaintbl.myMonths could
refer to more than one table listed in the FROM clause of your SQL
statement"

SELECT query0_1.myMonths, planned, dir
FROM query0_1 LEFT JOIN qryMaintbl
ON query0_1.myMonths = qryMaintbl.myMonths

Thanks for your presistance in helping me solve this problem. It is much
appreciated.

Step 0_1 would be to get all date:

SELECT planneddate+1-DatePart("d",planneddate) AS myMonths FROM
qryMaintbl
UNION
SELECT testingdate +1-DatePart("d",testingdate ) FROM qryTestingTbl

I am not sure about the last SELECT, it should involves the table/query
supplying the second set of dates, and the field.


Step 0_2 would be to enter missing dates into qryMaintbl:


SELECT query0_1.myMonths, planned, dir
FROM query0_1 LEFT JOIN qryMaintbl
ON query0_1.myMonths = qryMaintbl.myMonths




Step 0_3 would be to enter missing dates in qryTestingTbl (or whatever)



Steps 1


SELECT planneddate+1-DatePart("d",planneddate) AS myMonths,
Count(planned) AS Planned,
dir
FROM query0_1
WHERE (((query0_1.dir) Like [Enter Directorate]))
GROUP BY planneddate+1-DatePart("d",planneddate), query0_1.dir;


and 2, as actual


Step 3, not usefull anymore, since it is already done at step 0_1 (and
0_2).

Step 4:

SELECT testingplanned.Planned AS Planned,
testingactual.Completed AS Completed,
testingplanned.myMonths, testingplanned.dir
FROM testingplanned INNER JOIN testingactual
ON testingplanned .myMonths = testingactual.myMonths


we don't need the outer join, now, since all the dates ARE in both
tables
(since 0_1 and 0_2)




Hoping it may help,
Vanderghast, Access MVP



I am confused here. so first step I have QSum (for planned) and QSum2
(for
Completed) See Qsum below (for Qsum2 replaced planneddate with
Completed
where appropriate)

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

2nd step

Testingplanned and testingactual queries. See testingplanned below (for
testingactual replaced Planned (or planned ref) with Completed where
appropriate)

SELECT a.myMonths, LAST(a.planned) AS plannedthismonth, SUM(b.planned)
AS
Planned, LAST(a.dir) AS Dir
FROM qSUM AS a INNER JOIN qSum AS b ON a.myMonths>=b.myMonths
GROUP BY a.myMonths
ORDER BY a.myMonths;

3rd Step

alldates union query
SELECT myMonths, dir FROM testingplanned
UNION SELECT myMonths, dir FROM testingactual;


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


Where was I to put the code that you provided below.

thanks
Susie






:

Would be easier if you can introduce the (missing) dates BEFORE making
any
sum. In fact, to introduce them as the first step of the whole
process.


Make a first query like:

SELECT allDates.myMonths, Nz(Completed, 0) AS newCompleted
FROM allDates LEFT JOIN yourtable
OIN allDates.myMonths = yourTable.myMonths



This query will so have all the required dates, with an amount of zero
for
the dates that were missing. Now, use THAT query, instead of
'yourtable'
to
make the running sum, in the query that does the said running sum.




Hoping it may help,
Vanderghast, Access MVP



I dont know if I really made clear what my problem was now after
reading
my
post again this morning but where the 0 for completed is for
7/1/2008,
I
really need to see 63 which is the previous months totals brought
forth,
so
my graph will plot correctly. So everywhere this is a 0 or no entry
I
need
the total brought forth so it continues a running sum throughout.

thanks
Susie

:

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?



:

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


news:[email protected]...