Microsoft Access not only stores and displays information, it performs equations as well. Queries allow you to manipulate the data that is stored in your tables. If you have two fields that contain number-based data types, such as numbers and currencies, you can calculate those fields in a query, form or report. Calculations performed through Access are more efficient than manual calculations and have less chance of error. The same basic principle for calculating an expression in a query is true for forms and reports as well.
Step 1
Open your Access query in Design View by right-clicking the title in the Navigation Pane and selecting "Design View." If you don't already have a query, select your table and click on the "Create" tab of the ribbon at the top of the page. Select the "Query" button to generate a query from your table.
Video of the Day
Step 2
Scroll to the right of your field listings grid until you locate a blank column. In the new field, type in a title for your calculated field, such as "Total Sum" and place a colon after the field name. Using a custom field title will prevent ambiguous titles such as "Field1" in your query.
Step 3
Type in the calculation expression using the table titles, field names and an operator. For example, your completed expression may state "Total Sum:[Orders].[OrderPrice][Inventory].[InventoryQuantity]" which calculates the order price from the Orders table by the inventory quantity in the Inventory table. If you have only one table associated with your query, you can simply input "Total Sum:[OrderPrice][OrderQuantity]".
Step 4
Switch to Datasheet View by clicking the "View" button on the ribbon at the top of the page. Review the calculated field to make sure the correct numbers were calculated. Save the query by clicking on the "Save" icon at the top of the screen.
Video of the Day