ACC2: How to Determine a Percentage from a Column of Values

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q132142

SUMMARY

This article demonstrates how you can create three totals queries to calculate a percentage for each unique record in a column of information, and then shows you the results of running the queries. This example uses the Orders table from the sample database NWIND.MDB to determine the percentage of times an individual shipper was used for delivering invoice orders.

Back to the top

MORE INFORMATION

To calculate a percentage for each unique record in a column of information, follow these steps.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. Before testing the following example, you may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

To determine the total count of the orders shipped by each shipper, create the following totals query, and add the Orders table:

      Query Name: ShipperCounts
      -------------------------------
      Type: Select Query

      Field: [Ship Via]
         Table: Orders
         Total: Group By
         Sort: Ascending
      Field: ShipperCount: [Ship Via]
         Table: Orders
         Total: Count

NOTE: To turn on the Totals feature in a query, click Totals on the View menu.

To determine the total count of all the orders shipped, create the following totals query, and add the Orders table:

      Query: ShipperTotalCount
      ----------------------------
      Type: Select Query

      Field: TotalCount: [Ship Via]
         Table: Orders
         Total: Count

To produce the percentages from the previous two queries, create the following query, and add the ShipperCounts and the ShipperTotalCount queries:

      Query: ShipperPercentage
      ------------------------
      Type: Select Query
      Join: None

      Field: [Ship Via]
         Table: ShipperCounts
      Field: [Company Name]
         Table: Shippers
      Field: ShipperPercent: Format([ShipperCount]/[TotalCount],"Percent")

Back to the top

Running the Queries

Because there is no join between the two queries used in the third query, the third query creates a Cartesian product between the ShipperCounts and ShipperTotalCount queries. But, because the ShipperTotalCount query has only one value, the result query has only as many records as the ShipperCounts query, and the expression is evaluated for each record to obtain the desired results.

When you run the ShipperPercentage query, the following results are produced:

Ship Via   Company Name       ShipperPercent
--------------------------------------------
       1   Speedy Express     29.68%
       2   United Package     39.42%
       3   Federal Shipping   30.89%

Back to the top

REFERENCES

Microsoft Access "User's Guide," version 2.0, Chapter 11, "Designing Select Queries," page 246

For more information about expressions in queries, search for "expressions: in queries/filters," and then "Expressions in Queries and Filters" using the Microsoft Access Help menu.

Back to the top

APPLIES TO

Microsoft Access 2.0 Standard Edition

Back to the top

kbhowto kbusage KB132142

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top