delete duplicate record

[SMT]

I have a table that lists innovations and has a planneddate, completeddate,
and a directorate for each innovation. I need a query that will count the
total planneddate, and total completeddate by month for each directorate. I
have a query that seems to list all the pieces of data I need but its not
correct. Below is result from that query
A Oct 1 A Oct 1
A Oct 1 A Oct 1

Where I need to see

A Oct 2 2

SELECT qryPlannedDirTest.dir, qryPlannedDirTest.PlannedlCumulativeMonth,
qryPlannedDirTest.PlannedlCumulativeCount, qryActualDirTest.dir,
qryActualDirTest.ActualCumulativeMonth, qryActualDirTest.ActualCumulativeCount
FROM (tblMonth LEFT JOIN qryPlannedDirTest ON
tblMonth.month_name=qryPlannedDirTest.PlannedlCumulativeMonth) LEFT JOIN
qryActualDirTest ON tblMonth.month_name=qryActualDirTest.ActualCumulativeMonth
GROUP BY tblMonth.month_id, qryPlannedDirTest.dir,
qryPlannedDirTest.PlannedlCumulativeMonth,
qryPlannedDirTest.PlannedlCumulativeCount, qryActualDirTest.dir,
qryActualDirTest.ActualCumulativeMonth, qryActualDirTest.ActualCumulativeCount
ORDER BY tblMonth.month_id;

 

[Allen Browne]

So your table contains fields like this:
InnovationID key field (primary or foreign)
PlannedDate Date/Time
CompletedDate Date/Time
You want a query that has:
- a row for each month,
- a count of the InnovationID planned in that month,
- a count of the InnoivationID completed in that month.

I take it that you also have a tblMonth, so all months get returned, even if
there are not planned or completed dates that month.

It's not too hard getting one of the fields going (such as the count of
PlannedDate in the month.) The problem will be getting the other field as
well. I suggest you use a subquery for that one.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

A better solution might be to have a related table to handle the dates. So
one innovation can have many dates in the related table: a record for the
PlannedDate, another for the CompletedDate, and so on. If there are (or
could ever be) more than just those 2 stages (e.g. an InitiatedDate,
milestone dates, verification after completion date, etc), this would be the
only way to go. It may not be worth it if you will only ever have the 2
dates, but it would make it easier to query.

 

[SMT]

First let me apologize for the subject "delete duplicate record" is certainly
appropriate here. I left that in inadvertently.

Please see my comment back within

So your table contains fields like this:
InnovationID key field (primary or foreign)
PlannedDate Date/Time
CompletedDate Date/Time
You want a query that has:
- a row for each month,
- a count of the InnovationID planned in that month,
- a count of the InnoivationID completed in that month.

**What you have listed above is correct with one addition
the branch field (each innovation has a congnizant branch) The branch field
is related to an org table with the fields dir, dept, div, and branch. In the
case of my query I want to also add

- a row for each month,
- a count of the InnovationID planned in that month,
- a count of the InnoivationID completed in that month.
- dir who submitted that innovation (the A in my sample below is an example of a dir)

I take it that you also have a tblMonth, so all months get returned, even if
there are not planned or completed dates that month. **Yes that is correct
It's not too hard getting one of the fields going (such as the count of
PlannedDate in the month.) The problem will be getting the other field as
well.

**Yes that is true. I do have a query that shows the dir, total planneddate,
and total completeddate but it doesnt break out by months.

I suggest you use a subquery for that one.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

**I will check this out, thanks for your help