Subreport problem Please Help

[Excorsa]

Hello everyone. I am trying to create a main report that shows the count of each milestone in each possible status catagory and then calculate the percentage for example:

Total Milestones: 204

Milestone Status Total Milestones Percentage
COP Underdevelopment 61 30%
COP Rejected 21 10%

This is done in the main report. I then have a subreport that counts and produces the percentage for each of these status's but where they are past due. When I run the subreport by itself everything works great. When I
run the main report with the subreport added into it's dataview section to the right of everything above, it show the percentages of the subreport as 100%. Please any help would be greatly appreciated!!

Here are the select statements.

Main report query:

SELECT MilestoneStatus.Id, Milestone.MilestoneStatus, Count
(Milestone.MilestoneStatus) AS CountOfMilestoneStatus
FROM MilestoneStatus INNER JOIN Milestone ON
MilestoneStatus.MilestoneStatus = Milestone.MilestoneStatus
GROUP BY MilestoneStatus.Id, Milestone.MilestoneStatus;

Subreport query:

SELECT MilestoneStatus.Id, Milestone.MilestoneStatus, Count
(Milestone.MilestoneStatus) AS CountOfMilestoneStatus
FROM MilestoneStatus INNER JOIN Milestone ON
MilestoneStatus.MilestoneStatus = Milestone.MilestoneStatus
WHERE (((Now()-[PDDMSDate])>1))
GROUP BY MilestoneStatus.Id, Milestone.MilestoneStatus
HAVING (((Milestone.MilestoneStatus) Not Like "Complete" And
(Milestone.MilestoneStatus) Not Like "Cancelled" And
(Milestone.MilestoneStatus) Not Like "Billed" And
(Milestone.MilestoneStatus) Not Like "Paid"));

Percentages are calculate in each report with the statement:

=Round((CountOfMilestoneStatus/TotalMilestones)*100)

 

[Hmadyson]

Seems that you could make this simpler by letting the report do all of the work instead of the query. I would create a query that grabbed MilestoneStatus and ID for all records, then in the report, I would group by the status.

In the status header or footer you ask it to do a count.

then in the report footer you put the sum of the counts and the percentages of each.

You hide all detail sections.

You may need to flip your report so that the statuses run across the top and the percentage of total runs along the bottom.

 

[Excorsa]

how do I do a count of the milestones that are in each status category. I'm sorry but I am new to access and I don't quite understand how to do this.

 

[Hmadyson]

the controlsource for the field would be =Count(*)