Can I show a % based on two other numbers in the same report?

K

KB

I have a table called "bid report". I made two queries, one based on total
jobs bid, and another based on jobs bid that were awarded to us. Each query
is based by employee. I get into the report, I successfully have one vertical
row of employees, next to their name shows the total amount of projects they
bid, next to that shows the total amount they bid and were awarded.
Now what I need to do is show the percentage of bids won over the amount of
total bids they did (divide bids won by total bid)
Example
Employee Jobs Bid Jobs Won Percentage
Joe 100 50
50%
How do I calculate the percentage?
 
M

Marshall Barton

KB said:
I have a table called "bid report". I made two queries, one based on total
jobs bid, and another based on jobs bid that were awarded to us. Each query
is based by employee. I get into the report, I successfully have one vertical
row of employees, next to their name shows the total amount of projects they
bid, next to that shows the total amount they bid and were awarded.
Now what I need to do is show the percentage of bids won over the amount of
total bids they did (divide bids won by total bid)
Example
Employee Jobs Bid Jobs Won Percentage
Joe 100 50
50%
How do I calculate the percentage?


Use an expression in the percent text box
=[Jobs Won] / [Jobs Bid]
and set the text box's Format property to Percent
 
K

KB

Ya know, I must have tried about 20 versions of what you gave me, and none
worked. This one worked, Thanks!!!!

Actually, I have 1 more. In the same table, I have a column labelled "bid
amounts". I want to make up a report that separates the column by amounts
using the "awarded" criteria.
Example:
Number of Bids Awarded between $1 and $15000
Number of Bids Awarded between $15000 and 30000
And so on.
I made separate queries based on the amount separations I need, I just dont
know how to show it on the report.

Thanks.
Marshall Barton said:
KB said:
I have a table called "bid report". I made two queries, one based on total
jobs bid, and another based on jobs bid that were awarded to us. Each query
is based by employee. I get into the report, I successfully have one vertical
row of employees, next to their name shows the total amount of projects they
bid, next to that shows the total amount they bid and were awarded.
Now what I need to do is show the percentage of bids won over the amount of
total bids they did (divide bids won by total bid)
Example
Employee Jobs Bid Jobs Won Percentage
Joe 100 50
50%
How do I calculate the percentage?


Use an expression in the percent text box
=[Jobs Won] / [Jobs Bid]
and set the text box's Format property to Percent
 
M

Marshall Barton

To make a report that groups on ranges like that, you need
to use a single query and let the report group by a range
name. To do this you might be able to use the Partition
function, but it is not particularly flexible about range
boundaries,

Better is to greate a table to define the ranges. Add three
fieldsL
Descr Text
Low Long
High Long
then populate the table with data like:
"Less then $15.000" 0 15000
"$15,000 - $30,000" 15000 30000
. . .

With all that in place, create a query that uses an non-equi
join to your data table. For example:

SELECT tbl.flda, tbl.fldb, . . ., Ranges.Descr
FROM tbl INNER JOIN Ranges
ON tbl.[Bids Awarded] >= Ranges.Low
AND tbl.[Bids Awarded] < Ranges.High

Then base your report on the query and set the report to
group on the Descr field.

Note that you can not create that kind of Join in the query
design grid, you must switch to SQL view to create this kind
of query.
--
Marsh
MVP [MS Access]

Ya know, I must have tried about 20 versions of what you gave me, and none
worked. This one worked, Thanks!!!!

Actually, I have 1 more. In the same table, I have a column labelled "bid
amounts". I want to make up a report that separates the column by amounts
using the "awarded" criteria.
Example:
Number of Bids Awarded between $1 and $15000
Number of Bids Awarded between $15000 and 30000
And so on.
I made separate queries based on the amount separations I need, I just dont
know how to show it on the report.

Marshall Barton said:
Use an expression in the percent text box
=[Jobs Won] / [Jobs Bid]
and set the text box's Format property to Percent
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top