Subquery - how to count in access

[Dagpauk]

Assume the following table holding information about the planning date
and execution date for an imaginary "objects"

ObjectPlan

ObjectID PlannedDate ExecutedDate
1 19.03.04 28.03.04
2 19.03.04 24.03.04
3 19.03.04 19.04.04
1 19.04.04 20.04.04

Now, I want to display a count of the number of planned executions vs.
the real executions - grouped by monthly. So the resulting table would
be:

Month CountOfPlanned CountOfExecuted
03 3 2
04 1 2

How can this be achieved in Access? I tried the following:

Select Format(a.PlannedDate,"mm") As Month, Count(a.ObjectID) As
CountOfPlanned, _
(SELECT count(b.ObjectID)
FROM ObjectPlan As b
WHERE Format(b.ExecutedDate,"mm") = Format(a.PlannedDate,"mm")
) As CountOfExecuted
FROM ObjectPlan As a
GROUP BY Format(a.PlannedDate,"mm")

But this doesn't seem to do the trick. I could write two queries
counting separately then joining using union but there must be an
easier way ??

cheers,

Dag

[Trevor Best]

Dagpauk wrote:

Assume the following table holding information about the planning date
and execution date for an imaginary "objects"

ObjectPlan

ObjectID PlannedDate ExecutedDate
1 19.03.04 28.03.04
2 19.03.04 24.03.04
3 19.03.04 19.04.04
1 19.04.04 20.04.04

Now, I want to display a count of the number of planned executions vs.
the real executions - grouped by monthly. So the resulting table would
be:

Month CountOfPlanned CountOfExecuted
03 3 2
04 1 2

How can this be achieved in Access? I tried the following:

Select Format(a.PlannedDate,"mm") As Month, Count(a.ObjectID) As
CountOfPlanned, _
(SELECT count(b.ObjectID)
FROM ObjectPlan As b
WHERE Format(b.ExecutedDate,"mm") = Format(a.PlannedDate,"mm")
) As CountOfExecuted
FROM ObjectPlan As a
GROUP BY Format(a.PlannedDate,"mm")

But this doesn't seem to do the trick. I could write two queries
counting separately then joining using union but there must be an
easier way ??

cheers,

Dag

Not elegant but:

SELECT Format([PlannedDate],"mm/yy") AS [Month],
DCount("*","objectplan","month(planneddate)=" & Month([plannedDate]) & "
and year(plannedDate)=" & Year([plannedDate])) AS CountOfPlanned,
DCount("*","objectplan","month(Executedate)=" & Month([ExecuteDate]) & "
and year(ExecuteDate)=" & Year([ExecuteDate])) AS CountOfExecuted
FROM ObjectPlan
GROUP BY Format([PlannedDate],"mm/yy"),
DCount("*","objectplan","month(planneddate)=" & Month([plannedDate]) & "
and year(plannedDate)=" & Year([plannedDate])),
DCount("*","objectplan","month(Executedate)=" & Month([ExecuteDate]) & "
and year(ExecuteDate)=" & Year([ExecuteDate]));

HTH
--
But why is the Rum gone?

[R.A.Balder]

You might easily get a similar result - but not exactly what you are looking
for with this:
SELECT Count(Test.ObjectID) AS CountOfObjectID, Month([Test]![PlannedDate])
AS PMonth, Month([Test]![ExecutedDate]) AS MonthEx
FROM Test
GROUP BY Month([Test]![PlannedDate]), Month([Test]![ExecutedDate]);

It will look like this:
Count PMonth EMonth
2 3 3
1 3 4
1 4 4

Telling you: 2 object where both planned and executed in moth 3. 1 object
planned in moth 3 and executed in moth 4, and 1 object planned and executed
in moth 4. Not exactly what you wanted, but done only in one simple query.

Mvh
Rolfern

"Dagpauk" <da*****@yahoo.co.uk> wrote in message
news:54**************************@posting.google.c om...

Assume the following table holding information about the planning date
and execution date for an imaginary "objects"

ObjectPlan

ObjectID PlannedDate ExecutedDate
1 19.03.04 28.03.04
2 19.03.04 24.03.04
3 19.03.04 19.04.04
1 19.04.04 20.04.04

Now, I want to display a count of the number of planned executions vs.
the real executions - grouped by monthly. So the resulting table would
be:

Month CountOfPlanned CountOfExecuted
03 3 2
04 1 2

How can this be achieved in Access? I tried the following:

Select Format(a.PlannedDate,"mm") As Month, Count(a.ObjectID) As
CountOfPlanned, _
(SELECT count(b.ObjectID)
FROM ObjectPlan As b
WHERE Format(b.ExecutedDate,"mm") = Format(a.PlannedDate,"mm")
) As CountOfExecuted
FROM ObjectPlan As a
GROUP BY Format(a.PlannedDate,"mm")

But this doesn't seem to do the trick. I could write two queries
counting separately then joining using union but there must be an
easier way ??

cheers,

Dag

[Dagpauk]

Thanks for the ideas guys. In the end I decided to go for a union
query like this:

select month(PlannedDate) As M, Count(PlannedDate) As Planned, 0 As
Executed
from ObjectTable
group by month(PlannedDate)
union
select month(ExecutedDate) As M, 0 As Planned, Count(ExecutedDate) As
Executed
from ObjectTable
group by month(ExecutedDate)
Then I created a final query summing it all up:

select M, sum(Planned), sum(Executed)
from ObjectTable
group by M

I did this to include all cases - for example if an object was
Executed in a month in which no other objects were planned:

Month Planned Executed
06 0 1

The union query shouldn't be too costly as it groups by month creating
at the most 12x12 = 144 records (???)

I guess a cross-tab query could have easily solved this problem but I
would the solution to be as "pure" as possible in case the application
is to be used with Oracle/SQL-Server.

cheers,

Dag

"R.A.Balder" <R_********@xhotmail.com> wrote in message news:<IA*********************@juliett.dax.net>...

You might easily get a similar result - but not exactly what you are looking
for with this:
SELECT Count(Test.ObjectID) AS CountOfObjectID, Month([Test]![PlannedDate])
AS PMonth, Month([Test]![ExecutedDate]) AS MonthEx
FROM Test
GROUP BY Month([Test]![PlannedDate]), Month([Test]![ExecutedDate]);

It will look like this:
Count PMonth EMonth
2 3 3
1 3 4
1 4 4

Telling you: 2 object where both planned and executed in moth 3. 1 object
planned in moth 3 and executed in moth 4, and 1 object planned and executed
in moth 4. Not exactly what you wanted, but done only in one simple query.

Mvh
Rolfern

"Dagpauk" <da*****@yahoo.co.uk> wrote in message
news:54**************************@posting.google.c om...

Assume the following table holding information about the planning date
and execution date for an imaginary "objects"

ObjectPlan

ObjectID PlannedDate ExecutedDate
1 19.03.04 28.03.04
2 19.03.04 24.03.04
3 19.03.04 19.04.04
1 19.04.04 20.04.04

Now, I want to display a count of the number of planned executions vs.
the real executions - grouped by monthly. So the resulting table would
be:

Month CountOfPlanned CountOfExecuted
03 3 2
04 1 2

How can this be achieved in Access? I tried the following:

Select Format(a.PlannedDate,"mm") As Month, Count(a.ObjectID) As
CountOfPlanned, _
(SELECT count(b.ObjectID)
FROM ObjectPlan As b
WHERE Format(b.ExecutedDate,"mm") = Format(a.PlannedDate,"mm")
) As CountOfExecuted
FROM ObjectPlan As a
GROUP BY Format(a.PlannedDate,"mm")

But this doesn't seem to do the trick. I could write two queries
counting separately then joining using union but there must be an
easier way ??

cheers,

Dag