Reporting " count " question

[Rabun]

Heres one that is giving me fits ( = = Access newbie), more than likely
something simple that I blew right over . . . any help is appreciated -

I have a report based on a query, with several columns I need to work with.
Values in the cols are " Over " and " Under", as text values. The query is
for a start / end date, my count of records returned will be variable
depending on the runtime dates entered.

I need to count the number of " Over " , count the total lines , then return
a percentage " Over " in each col of the report ( there are 5 cols , Field
names [test_a], [test_b] ...[test_e] ) . Am I doing the right thing
attempting to " count " or do I need to take another approach ?

(count(test_a where val="Over") / count(*) x 100) // no attempt at
correct syntax here

for % Over is my goal. All my attempts are giving me " #Error " in the text
box I've placed in the footer.

Again, any help is greatly appreciated.

[Alan Webb]

Re: Reporting " count " question

Rabun,
Mebbe you need to use a subquery to provide your counts of over/under to the
query in which they live?

"Rabun" <hyper_speed64@ yahoo.com> wrote in message
news:e1ddf$4238 cf14$97d59f1a$8 878@ALLTEL.NET. ..[color=blue]
> Heres one that is giving me fits ( = = Access newbie), more than likely
> something simple that I blew right over . . . any help is appreciated -
>
> I have a report based on a query, with several columns I need to work
> with.
> Values in the cols are " Over " and " Under", as text values. The query is
> for a start / end date, my count of records returned will be variable
> depending on the runtime dates entered.[/color]

[Rabun]

Re: Reporting &quot; count &quot; question

Weeellll . . . I did get the % result by assigning 0 /1 to under / over via
a secondary field corresponding to the test_x fields with onchange subs,
then doing count / sum . . . then hiding all the calculating textboxes on
the report. Jeez, wayyyy to cluttered . . . proof that I don't belong here
:) Was looking for a cleaner way to do it . . . I'll check into the
subqueries, Access is all new to me.

Thanks for the reply !

Rab



"Alan Webb" <knogeek@hotmai l.com> wrote in message
news:EeqdnbeaOO wUbqXfRVn-jw@comcast.com. ..[color=blue]
> Rabun,
> Mebbe you need to use a subquery to provide your counts of over/under to[/color]
the[color=blue]
> query in which they live?
>[/color]
(clip)

[Lips]

Re: Reporting &quot; count &quot; question

I tend to do this sort of stuff like this.

Use an alias in a query column: Total:IIf(IsNul l([test_a]),0,1) Make this a
sum column.

Next: CountOvers:IIf([test_a]="over",1,0) Make this a sum column also.

Next: Over%: [CountOvers]/[Total] Make this an expression column and
format to percent.

Works for me. By the way, watch those control names in the reort as that can
cause errors if they are the same as the field names... I always rename them
txtBlahBlah or lblBlahBlah etc.

Hope that's of some help.

Jeff.

"Rabun" <hyper_speed64@ yahoo.com> wrote in message
news:e1ddf$4238 cf14$97d59f1a$8 878@ALLTEL.NET. ..[color=blue]
> Heres one that is giving me fits ( = = Access newbie), more than likely
> something simple that I blew right over . . . any help is appreciated -
>
> I have a report based on a query, with several columns I need to work[/color]
with.[color=blue]
> Values in the cols are " Over " and " Under", as text values. The query is
> for a start / end date, my count of records returned will be variable
> depending on the runtime dates entered.
>
> I need to count the number of " Over " , count the total lines , then[/color]
return[color=blue]
> a percentage " Over " in each col of the report ( there are 5 cols , Field
> names [test_a], [test_b] ...[test_e] ) . Am I doing the right thing
> attempting to " count " or do I need to take another approach ?
>
> (count(test_a where val="Over") / count(*) x 100) // no attempt at
> correct syntax here
>
> for % Over is my goal. All my attempts are giving me " #Error " in the[/color]
text[color=blue]
> box I've placed in the footer.
>
> Again, any help is greatly appreciated.
>
>[/color]