CODEWITHSUNDEEP
ms-accessms-access-2010
Data_101
Data_101

Reputation: 953

Access query - Percentage

I have a table which tooks like this:

ID_1    Total   Active  Inactive
124507   4       1       3
124519   4       0       4
124521   4       2       2

I would like to add a column at the end, which would tell me which percentage of the the total are active?

like:

ID_1    Total   Active  Inactive     %
124507   4       1       3           25
124519   4       0       4            0
124521   4       2       2           50

Upvotes: 1

Views: 12806

Answers (2)

JuniperSquared
JuniperSquared

Reputation: 143

Format(((active) / (total)), Percent)

Reference: http://www.techonthenet.com/access/functions/numeric/format.php

Upvotes: 3

hypetech
hypetech

Reputation: 166

Make a query in query design. Include the table you already have. Drag all the fields from your table to the bottom of the screen where you can put fields to include them in the query. In the next empty box, put:

NameofNewField:Format((tablename.Active)/(tablename.Total), Percent)

This will create a new field in your query, with NameofNewField being the name (change it to whatever you want, but I wouldn't recommend "%"). Also be careful of reserved words, because "Percent" is a reserved word and you'll get an error if you wanted to make that the field name. "tablename" is just the name of your original table.

I included "Format()" in the formula because that way you can just make the query with no other editing, but the better way is to format the field you are making into a percentage into a field that only allows percent, because personally I have problems with Format() sometimes. You can change the field by putting your cursor in the box where you just wrote your formula, then it should show field properties on the right side of the screen where you can change the field to a percent field.

Upvotes: 0

Related Questions