Hi,

I have created a report from a query that looks at our sales teams' targets and actual results each month. I have done the sum of the actual sales to get the total and ticked the box that Calculates the Percentage of Total for Sums.

I edited the formula for the percentage to give a percentage of each individuals actual sales compared to their target for that period which works fine. However, I cannot sort by that percentage to give me the top sales people for each period. I read that to do this using the report may not be possible as it only calculates the percentages once the report is run each time.

Is there a way to sort the sales people by these percentage figure?

Thanks in advance for you help.

Alex

What is your underlying query? Could it be adapted to produce the percentages? If so, you could get around the problem that way.

What is your underlying query? Could it be adapted to produce the percentages? If so, you could get around the problem that way.

The underlying query pulls sales tagets from one table, and sales figures from another table. How do I produce percentages in a query?

Thanks,

Alex

As long as all figures are ending up in one query, you can just do it as with a normal calculation (X/B * 100).

If you want to omit the '* 100' part, you can set the field to be displayed on the report as a percentage.

If you can't work it out, post the query and I'll take a look.

As long as all figures are ending up in one query, you can just do it as with a normal calculation (X/B * 100).

If you want to omit the '* 100' part, you can set the field to be displayed on the report as a percentage.

If you can't work it out, post the query and I'll take a look.

OK thanks! I will give it a try and let you know how I get on! :)

Alex

OK thanks! I will give it a try and let you know how I get on! :)

Alex

Hi Matt,

I've been trying the formula you suggested - do I put it in a new column of the query? And if so, whereabouts in the column would I write the formula?

Thanks,

Alex

Hi Matt,

I've been trying the formula you suggested - do I put it in a new column of the query? And if so, whereabouts in the column would I write the formula?

Thanks,

AlexAs Matt may be in bed right now I'll jump in.
Yes you put the formula in a new column in the Field row. After you've entered the formula Access will assign a default name to the formula which you can edit to something sensible.

hth
Stopher

As Matt may be in bed right now I'll jump in.
Yes you put the formula in a new column in the Field row. After you've entered the formula Access will assign a default name to the formula which you can edit to something sensible.

hth
Stopher

Thanks for the assist, Stopher.
At 04:38 (Ontario time) I was dead to the world and can assure you my dreams had little to do with Access. :D

Thanks for the assist, Stopher.
At 04:38 (Ontario time) I was dead to the world and can assure you my dreams had little to do with Access. :D

Thanks to both of you for your help! Hopefully I will suceed this time! :)