subreport shows wrong data in main report

  • Thread starter Todd Hoeft via AccessMonster.com
  • Start date
T

Todd Hoeft via AccessMonster.com

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)
 
Top