Counting Sorted Entries

[Guest]

I have a table with columns "Bid Amount", "Status", and "Type". The status, I
would sort by awarded in the criteria column. Based on jobs awarded, I want
to count the number of projects awarded based on dollar amounts and sum it in
a report.
Ex.
Jobs awarded between $1.00 and $15000
Jobs awarded between $15000 and $30000
And so on.

What dollar amount jobs are we most successful bidding based on quantity
awarded. What type of jobs are we most successful bidding based on quantity
awarded.

 

[Guest]

In a footer in a report you can conditionally count rows by using the IIf
function to return a 1 or 0 if the value falls within a range or not and then
sum the return values of the function. This in effect counts the rows within
the range, so the expression for the ControlSource property of a text box for
the first range would be:

=Sum(IIf([Bid Amount] < 15000,1,0))

for the second range:

=Sum(IIf([Bid Amount] >= 15000 And [Bid Amount] < 30000,1,0))

Text boxes in a group footer in the report would give you counts for the
group; text boxes in the report footer would give you counts for the whole
report.

You can also use the same technique to conditionally aggregate values in
other ways, this time by multiplying the amount by the return value of the
IIf function, e.g. to sum all amounts in a range:

=Sum([Bid Amount] * IIf([Bid Amount] >= 15000 And [Bid amount] < 30000,1,0))

or to average the amounts in a range:

=Avg([Bid Amount] * IIf([Bid Amount] >= 15000 And [Bid amount] < 30000,1,0))

and so on for whatever other type of aggregation you might want simply by
using the appropriate aggregation operator.

Ken Sheridan
Stafford, England