Subreport showing different data in the main report 1

[Excorsa]

Hi, I have two reports. One is a subreport of the other. They are simple reports that are calculating totals and percentages. The problem is that the subreport is showing 100% on all of the data. When I view the subreport by itself it shows all of the correct percentages. Why are the percentages all showing 100% in the main report? Any help would be greatly appreciated. Thanks for all your help.

 

[dhookom]

How do you expect us to know what your record sources are, the control sources, or the relationship between the main and subreport?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Excorsa]

I assumed that this was just a very simple problem, that it happens often. Being that I am so new to this I thought that it was probably easy to fix I just don't know enough about what I am doing. I didn't expect you to solve all my problems, just maybe give some possible reasons why this happens. Also I try not load the whole database into the first question, people tend to not look at a question when it looks like a manual. But thanks for being helpful.

 

[dhookom]

I'm not sure how you expect us to understand "showing 100% on all of the data". Is this a calculation? Is it on just the subreport or on the main report?

Feel free to provide enough details for someone to not have to guess where you are coming from with your question.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Excorsa]

Well I have a main report that is counting how many milestones are in each status and then calculates the percentage that is of all the milestones:
Total Milestones: 204

Future start: 53 26%
COP underdevelopment: 61 30%
and so on down the list of possible status options.
this works fine. I then have a sub report added in the detail section of the main report that shows milestones that are past due in each status and the percentage:

Total Milestones Past Due: 73

Future Start: 3 4%
COP Underdevelopment: 10 14%


here is where the problem is. When I run this subreport by itself all the percentages are correct but when I run the main report that has it in it the percentages are all 100%. Both reports are based off of queries. Also all the counts are correct, it is just the percentage that is incorrect.

Is there anything else I can provide that would help?

 

[dhookom]

Is there anything else I can provide that would help?

Yes, as I asked earlier, the control sources of the text boxes that display the percents.

Does the main report have just one record per status?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Excorsa]

Control Source for Percent text Box: =Sum([CountOfExpr1])

CountOfExpr1 is the number of milestones in each status. Yes there is only one record for each status.

 

[dhookom]

I can't imagine the CountOf anything would result in a percentage. I would expect to see something like:
=[NumericField]/Sum([NumericField])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

Excorsa - your post from 2 May 05 16:41 does a much better job of conveying what you need. If you will copy this format with additional information, I'm sure that Duane can easily assist you in solving this matter:

I have a main form, the query is:

SELECT _______

The results of this query are:

Field1 Field2
Value Value

There is a sub form, the query is:
SELECT _______

The results of this query are:

Field1 Field2
Value Value


I am trying to design the form to show the information like this:

(insert what you want your results to show, like you did above)

The information that the queries return is correct, but when I use them in the form they results are shown like this:

(insert what the incorrect results look like)



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Excorsa]

My appolagies I pasted the wrong control source. Sorry.

=Round(([CountOfMilestones]/[TotalPastDueMilestones])*100)

this is actually what I meant to paste in there.

 

[Excorsa]

Main Report

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

SELECT PastDueQuery.Id, PastDueQuery.MilestoneStatus, Count(PastDueQuery.Expr1) AS CountOfExpr1
FROM PastDueQuery
GROUP BY PastDueQuery.Id, PastDueQuery.MilestoneStatus;

 

[lespaul]

the next question is:

what is the SQL for PastDueQuery?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Excorsa]

PastDueQuery

SELECT MilestoneStatus.Id, Milestone.MilestoneStatus, Now()-[PDDMSDate] AS Expr1
FROM MilestoneStatus INNER JOIN Milestone ON MilestoneStatus.MilestoneStatus=Milestone.MilestoneStatus
GROUP BY MilestoneStatus.Id, Milestone.MilestoneStatus, Now()-[PDDMSDate]HAVING (((Now()-[PDDMSDate])>1));

 

[Excorsa]

I don't know if anyone is still interested in helping me out with this one but here are the select statements again.


Main 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;

Query for subreport in the dataview of te main report:

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

Now in the subreport I Sum the CountOfMilestoneStatus to get a total. I then use that to get a percentage for each milestone status. This is calculated as such: Round((CountOfMilestoneStatus/TotalMilestones)*100)


When I view the subreport by itself all the percentages are correct. When I view the main report all the percentages are 100%.