Report Calculation Assistance

[Jakboi]

Hello,

Having some confusion for a calculation on one of my reports. I am trying to count the number of times in my table column "AMOUNT" that is greater than the average.

I have the average figured out on my Report as follows:

[Text663]=[text50]/[text29]

Then I have this to find how many times in the column "Amount" that it is higher than average:

=Count(IIf([AMOUNT]>[text663],0))

It keeps giving me a enter parameter box. Anyone have insight to why this may not be working?

Thanks.

 

[Jakboi]

Hmm something tells me I shouldnt be using Count, but not sure what else to try.

I need to count the number of times in my table column Amount is greater than the average that is calculated on the report.

The way I have tried, mentioned above, wants the parameter value for Text663, but why isnt it finding the value of that box on my report?

 

[Brianwarnock]

I'll guess that you need to do something like

=sum(iif([Amount]>([text50]/[text29]),1,0))

Brian

 

[Jakboi]

Hey thanks for the reply Brian...

Tried your idea and it now wants the parameter value of text50 and text29.

=sum(iif([Amount]>([text50]/[text29]),1,0))

Tried as well:

=count(iif([Amount]>([text50]/[text29]),1,0))

Maybe I cant use Count or Sum when dealing with text boxes? Is it for tables or queries only...just noob guessing.

 

[Brianwarnock]

Sorry but yes you need to go back to the original query, for some daft reason I thought Text50 and text29 where.

brian

 

[Jakboi]

So i would have to make a query right? Ok now how would I start...I just tried and kinda lost with query's.

Ok so I query the Amount in a new query...but how do I get the average of that column in a query and a count as well?

Maybe I am completely off here but not sure.

This is where I do it right?

 

[Brianwarnock]

Oh! I took it for granted that your report was based on a query, Is it based directly on a table?, in which case I presume you can refer directly to the fields in the table, what I am saying is that you go back to the Record Source.

Brian

 

[Jakboi]

Yea it is from tables...is that bad? This is my first database and really shootin from the hip.

Yea see my table has a column named Amount. So on my report I have different text boxes that count the number of amounts, the total, and the average for the column.

Now I trying to find the amounts that are above the average.

Thanks for help or insight Brian.

 

[Brianwarnock]

I'm no guru, but shortly after finding this site a post from SJ damn what's his surname, he was known as Milo then now he's an Admin, anyway he pointed out to another poster that reports should always be based on queries not directly on tables as it gave greater flexibility, this has been repeated by other posters since then.

Brian

edit SJ McAbney

 

[Jakboi]

I'm no guru, but shortly after finding this site a post from SJ damn what's his surname, he was known as Milo then now he's an Admin, anyway he pointed out to another poster that reports should always be based on queries not directly on tables as it gave greater flexibility, this has been repeated by other posters since then.

Brian

edit SJ McAbney

Thanks Brian I may start a post about that if I cant find anything through searches...