Calculated Fields - Percentage

[aeddipa]

Does anybody know how to calculate a percentage based of another field in a query?

Essentially what I have is 100 rows of data with different dollar amounts. I want to divide each rows dollar amount by the sum of all the rows and multiply that by 100 to get the percentage for each row.

Ex.

100
200
300
350
250
400

Total = 1600

So, 100/1600 = .0625 * 100 = 6.25%

I want my field to show the result of 6.25%, but I'm not sure how to calculate it. Any ideas?

Thanks in advance!

 

[Joe4]

One way is to use a two-step process (two queries).

First, create an aggregate query to get your SUM. This should just end up returning a single record.

Then create a new query using your original data set and SUM query together. You can then divide each record by the SUM to get your percentage.

 

[aeddipa]

Awesome, that worked. The only question I have remaining is after I convert it to a percentage by multiplying by a 100, is there a way to show the '%' sign and is there a way to round to the nearest hundredth?

Ie.

2.056789 round to 2.06

Thanks Joe!

 

[Joe4]

No need to do either of these things, just format your calculated field as a Percentage with two decimal places. Just right click on the calculated field in Query Builder, select Properties, and set the Format and Decimal Place values accordingly.

If you really need to round the results (instead of just displaying them as rounded, use the ROUND function).

 

[aeddipa]

Thanks!

I'm decent with Excel, but a noob when it comes to Access. I don't know the simplest things. Thanks for showing me the Property area. It made things very easy!

 

[Joe4]

I'm decent with Excel, but a noob when it comes to Access

Welcome to the "other" side!

 

[aeddipa]

You mean the "dark" side? LOL Yes, Access is a whole new ball game. Really need to learn more about this because I can see the benefits.