How to Create a Formula in Access Queryby Darrin KoltowUpdated September 28, 2017
Microsoft Access is a database program with powerful tools for manipulating, organizing and analyzing data. A data field is simply the location within the database for a specific piece of information. Creating a formula for a data query inside Microsoft Access involves building expressions that can be used in two ways: First, as a calculated field, which creates a new field calculated from existing ones; or, second, as criteria that other fields must meet for a table row to be included in the query's results.
Create a sample table to illustrate the effects of applying a variety of formulas: open a new Access database and create a table with these fields:
game, of type "text" qtysold, of type "number" unitprice, of type "currency" buyercity, of type "text" buyerstate, of type "text"
Create sample data for the table, using the following data to correspond to the table fields in this order: game,qtysold,unitprice,buyercity,buyerstate
pacman,2,$4.98,billings,montana pacman,3,$4.98,trenton,new jersey asteroids,5,$2.50,detroit,michigan asteroids,1,$1.25,fresno,california
Save the database ("control-S"), typing "gamesales" when prompted for the table name.
Create a query to display the data by selecting "Create," then "Query design." Click "Add," then "Close" on the "Show table" dialog box.
Right-click on the first column's "Field:" row and select "build." Type the following expression (also known as a "formula") in the "Expression Builder" dialog box that appears:
buyeraddress: [buyercity]+", "+[buyerstate]
Group-select the fields "game" through "unitprice" from the "gamesales" window above the query grid. Drag those selected fields onto the first column of the query grid.
Run the query by pressing the "Run" icon on the toolbar (also known as the "Ribbon"), and notice the calculated field "buyeraddress."
Right-click on the query's tab and select "Design view." Create a new calculated field, using the instructions from Step 4. Enter this expression in the "Expression builder" dialog box:
Type the following filter criteria in the "Criteria" row for the new "totalsale" column:
Re-run the query by pressing the "Run" icon, and notice that only records with net sales under 10 dollars are shown.
Return to "Design view" and delete the criteria for the "totalsale" calculated field. Drag the "buyercity" field from the "gamesales" table onto a new column and uncheck the checkbox for that column. Type the following in the "criteria" field for the new "buyercity" column:
Re-run the query, noticing that only the sale in Billings, Montana is displayed.
Close the query by right-clicking its tab and selecting "close." Type "myquery" when prompted for the query's name.
Create a new field in the "gamesales" table by clicking the "gamesales" tab, right-clicking on any existing field, and selecting "Insert field."
Select "date and time" for the new field's type, and enter "datesold" for the field's name. Enter the following dates for the new field. (Don't worry about which specific record gets each date):
12/15/2009 12/1/2009 12/8/2009 12/24/2009
Type the following for the "Criteria" field of the "datesold" field:
Re-run query, noticing that only game sales made on or after Dec. 15, 2009, are displayed.
- Jupiterimages/Pixland/Getty Images