Counting, then Dividing in a Report using Subreport Data Values

[Czeszyn]

Okay, I am new to this and I hope that I am explaining this clearly.

I have a main report that has 4 subreports. I want to take totals from the subreports and display them in my main report. I had used the code below. Once there is a total from all subreports, I want it to be divided by the amount of subreports that have data value.

=(IIf([rptEmployEvaluationAuditor1 subreport].[Report].[HasData],[rptEmployEvaluationAuditor1 subreport].[Report]![OPTotal],0)+IIf([EmployEvaluationAuditor2 subreport].[Report].[HasData],[EmployEvaluationAuditor2 subreport].[Report]![OPTotal],0)+IIf([EmployEvaluationAuditor3 subreport].[Report].[HasData],[EmployEvaluationAuditor3 subreport].[Report]![OPTotal],0)+IIf([EmployEvaluationAuditorTrainer subreport].[Report].[HasData],[EmployEvaluationAuditorTrainer subreport].[Report]![OPTotal],0))/4

I want to replace the 4 at the end of the formula so that it will only divide the subreports that have value.

Example:
subreport 1 = 10
subreport 2 = 0
subreport 3 = 0
subreport 4 = 15
Total value = 25
Then I want to divide 25/2 giving me 12.5

or another example:
subreport 1 = 10
subreport 2 = 10
subreport 3 = 0
subreport 4 = 10
Total value = 30
Then divide 30/3 giving me 10

I had put that formula right in my text box. But I do not know how to get the dividing number to only divide by the number of subreports that have value.


I wasn't sure if it is to look like this, which did not work:



= Sum[Report] where x>0
Count([Report] where x>0
(IIf([rptEmployEvaluationOperator subreport].[Report].[HasData],[rptEmployEvaluationOperator subreport].[Report]![OPTotal],0)+IIf([rptEmployEvaluationOperatorTrainer subreport].[Report].[HasData],[rptEmployEvaluationOperatorTrainer subreport].[Report]![OPTotal],0))/Count

Well, I hope someone can help me.
Thank you in advance for your help.

Tony.

[orange]

cross posted http://www.access-programmers.co.uk/...d.php?t=279995

Tony,

It isn't that cross posting -- same/similar request in multiple forums is wrong. However, when you do cross post tell the readers you have done so. Provide a link. If you don't tell readers, it is considered poor/bad etiquette.

Here's why.

Good luck.

[Czeszyn]

I thought this was a different site. I wasn't getting what I needed to figure what was needed. What you can only post on one site for help???

[orange]

Nobody said you can/should only post on one site.
Post where ever you want, BUT have the courtesy to inform users of your other post(s).

I hope you read and understood the "Here's why" by excelGuru.


People on forums are unpaid volunteers who have real lives to live in addition to participating in forum(s). If you are not getting answers, then it could be that people don't understand your issue; have given you advice/suggestions that you may have not understood or ignored; are occupied with other post(s); or have nothing to add/suggest.
If you aren't getting responses you like, then
-rephrase your question
-get more specific/more detailed in the area of concern
-restate your question in step format with some sample data
-use Google to research the issue
-don't overlook Youtube for tutorials/examples

I gave you advice/suggestion in the other forum, and even repeated it when you did not address it.

Tony,

Data exists in tables. It is displayed, formatted and manipulated in reports.
I suggested you use a few queries against some tables until you get some
 results for what you are attempting to achieve.

Can you post a copy of your database and some explicit instructions
 of what to do with what?

Your response

So would I use what you mentioned above in my query?

indicates to me that you either didn't understand my request, or chose to ignore it.
You did not show a query, so perhaps you are struggling with the basics of Access.

Good luck with your project.

[Czeszyn]

Normally I was using the forum that was from UK I think. Did not know about cross posting till you said that now. Sorry. I did not even know these two were connected.

I am confused about the answer I was getting. I do not ignore anything. Its just seems that there has to be another way than just going through a query, like doing it right in the report like I did when I brought the subreports total in. So I was looking for another forum to get other options. So if you post in one forum, you put that you are cross posting at the top of your thread??? I also had checked YouTube, and there seems to be things like I want to do, but it is not broken down to where I can see how they did it. Sorry for the confusion, and did not mean to step on any ones toes.

Tony

[orange]

You didn't step on anyone's toes.
The forums are not linked.

My suggestion was to use a few queries to test some options an alternatives. It just seems more straight forward than playing with recordsources and reports to do some basic testing.

I've asked you to post a copy of your database with some data and instructions of how to get around the database. You have not provided that.

From first principles on any subject -- set up something simple as a proof of concept. Get the proof of concept understood and working; then move to the "production version".

You have some tables. Clearly identify the concept (calculation) you want to test.
Set up a query or queries to do the calculation. Test, and adjust as necessary. Then,
take you proven proof of concept query , adjusted for Production, and use that in your report.

I still don't know in clear terms what you are trying to do. And do not know what QUERY you have referred to.