Create a Query with Calculations

Locked lesson.

Upgrade

About this lesson

Further explore the Navigation Pane and learn how to use a query to calculate data.

Exercise files

Download this lesson’s related exercise files.

Services_10_START.accdb
1.3 MB Services_10_Navigation Pane END.accdb
1.2 MB

Quick reference

Access 10. Create a Query with Calculations

Application Terminology

Criteria

Specify criteria to limit the rows of information that are displayed in the results.

If you put criteria under a field, data will only show if the criteria is true.  If you have criteria listed under more than one cell, all criteria must be true on the same row for that information to be displayed in the results.

If there is criteria on multiple rows, all criteria on each row must match OR all criteria on the next row, and so on.

Datasheet View

The Datasheet View of a select query shows the results.  To best fit columns, double-click the right border in the column header.  If multiple columns are selected, all columns will be best-fit.  Unlike Excel, which can best-fit what is not showing, Access best-fits just what shows.

F2 = Toggle Edit Mode and Navigation Mode

Press the F2 key to toggle between Edit Mode, which shows and insertion point, and Navigation Mode, which selects the entire value.

In Edit mode, pressing Home moves the insertion point to the beginning of the value, and pressing End moves the insertion point to the end of a value.  The Left and Right arrow keys move one character in the respective direction.

Field

The Field cell shows an example of what is going to be in a column of a query. It can be a field from one of the tables that the query uses, or an equation.  If the Field cell contains an equation, it must be given a name for the column that is different than any field in the fieldlists that the query uses.

Field Alias

You can specify names for fields and calculated fields by prefacing the expression in a field cell with the column name followed by colon.  Do not assign names that are other field names in the source for the query.

For instance:

MyNewFieldname: expression

Fields

To choose fields to show in the query, you can double-click a field to put it on the on the query design grid.  You can also drag a field, or selection of fields from a fieldlist to the grid.  You can also drop the list of choices in the Field cell on the grid and pick something.  You can also type a field or expression.

Point-in-Time Value

 As a general rule, calculations should not be stored in a database unless you are creating a report database.  However, A Point-in-Time value, which is usually a calculated value that won't change, can be stored because it becomes part of a new record.  For instance, invoices amounts may be stored so they do not have to be calculated each time, or to avoid errors if the prices for invoice items change in the future.

Query

Queries enable you to choose data from multiple tables.  You can Sort by specified fields and filter records to show by specifying criteria

Resize Panes in the Query Design

To resize panes in the query design, move your mouse pointer to the line dividing the top and bottom panes.  When the shape of the pointer changes to a horizontal line and double-headed vertical arrow, click and drag to resize.

Shift-F2 = Zoom

To Zoom the contents of a cell to see it better, press Shift-F2. Usually Zoom is also on the right-click shortcut menu.

Show

The Show row in the query design has a checkbox in each field to check or clear depending if you want that column to show in the results.  For instance, you may choose to sort or specify criteria in a column but not show it.

Sort

You can specify the fields that a query will sort on.  If more than one field is specified, order will go from left to right how they are defined.

Show Table

The Show Table dialog box provides a list of tables and queries.

Tables and Queries can also be dragged from the Navigation pane into a query.

Tables and Related Views

In the Tables and Related Views category of the Navigation Pane, each table has its own group.  As objects are created in a database, it may appear in more than one table group if it is based on more than one table.  When objects other than tables are imported into a database, they may be displayed in the Unrelated Objects group even if they are based on tables in the database.

This view is helpful to show which tables are required for objects.

Total Row

The Total row of a query enables you to choose how information will be aggregated.

To turn on (or off) the Total row, click on the Totals icon.

When an aggregate function is used, the names of the calculations will be CountOfField, SumOfField, and so on.

View

For a query, you can choose Datasheet View to see results, Design View to visually modify the definition of a query, SQL View to modify the SQL statement.

Steps

Show/Hide the Total Row

  1. To turn on (or off) the Total row, click on the Totals icon.
  2. The default choice for each field is Group By but it can be changed.

View Navigation Pane by Tables and Related Views

  1. Right-click on the header of the Navigation Pane
  2. Choose View By > Tables and Related Views
  3. In Tables and Related Views, each table has its own group.
Login to download
  • 00:04 This is Access 2013, Lesson 10.
  • 00:08 We will talk about the Navigation Pane a bit more
  • 00:11 because it is important to be able to get around
  • 00:13 and find what you want.
  • 00:15 Hi, this is Crystal
  • 00:17 I will also show you how to use a query to calculate data.
  • 00:21 When you first open an Access database,
  • 00:24 the Navigation Pane Category may be
  • 00:26 set to Tables and Related Views.
  • 00:31 In Tables and Related Views, each table has its own group.
  • 00:37 Below the Customers table, we see an invoice report listed.
  • 00:41 This same report is also shown under the Service Types table,
  • 00:46 the Invoices table, and the Work table.
  • 00:50 I find this view confusing but it does have its good points.
  • 00:55 This view is helpful when you want to see
  • 00:57 which tables are used in other objects.
  • 01:00 Let's look at the objects in the Invoices table group.
  • 01:04 The Invoices table was imported from another Access database
  • 01:08 and is linked to Customers though CustID.
  • 01:13 InvoiceID is an AutoNumber field because
  • 01:15 it has (New) for a New Record.
  • 01:19 There are fields for Invoice Number,
  • 01:21 Date for the Invoice, Amount for Services, Tax,
  • 01:25 Adjustment (Amount), Amount for the Invoice, …
  • 01:28 There is a yes/no field indicating
  • 01:30 if the invoice is locked for editing.
  • 01:33 In the Invoices table group,
  • 01:35 there is a form to manage invoice information,
  • 01:39 and an invoice report to print and give to customers.
  • 01:44 Queries enable you to see data
  • 01:46 from more than one table, sort, and filter.
  • 01:51 Create a new query that shows the number of invoices
  • 01:54 and the total amount billed for each customer.
  • 01:57 Click on the Create ribbon,
  • 01:59 and click on Query Design.
  • 02:01 I like this method best for creating a new query.
  • 02:05 In the Show Table dialog box, you can
  • 02:07 pick tables and queries you want to get information from.
  • 02:11 One way to choose a table is by double-clicking it.
  • 02:15 Double-click Customers
  • 02:16 and double-click Invoices.
  • 02:18 Then Close the Show Table dialog box.
  • 02:22 Alternately, we could have dragged those
  • 02:24 two tables from the Navigation Pane,
  • 02:27 which is usually how I do it.
  • 02:29 Resize the fieldlists to see more fields.
  • 02:33 In the Customers fieldlist, double-click on the Customer field.
  • 02:38 From Invoices, double-click on the primary key field, InvoiceID
  • 02:43 and double-click the amount of the invoice, InvAmt.
  • 02:48 Access puts the fields you choose on the grid.
  • 02:52 Note the Rows on the grid.
  • 02:54 Field defines what is going to be in that column
  • 02:58 Table specifies where it (the field) comes from
  • 03:01 Sort gives you choices to sort in Ascending
  • 03:04 or Descending (order), or Not Sorted.
  • 03:07 Show gives you the option to show or not show that column.
  • 03:12 You might just have it (the column) on there as criteria,
  • 03:14 and Criteria so that you can limit the rows.
  • 03:19 On the Design ribbon, click the Totals button
  • 03:22 which is denoted by the Greek letter Sigma.
  • 03:25 Notice the grid has another row labeled Total.
  • 03:29 In the Total cell for each column, choose what to do.
  • 03:33 We will Group By Customer
  • 03:34 so each customer name shows one time.
  • 03:38 Under InvoiceID, choose Count
  • 03:41 and under the InvAmt, choose Sum.
  • 03:44 Click on the Datsheet View to see the results.
  • 03:48 I best-fit columns by selecting them
  • 03:51 and then double-clicking the right border line
  • 03:53 of one of the fieldnames.
  • 03:56 Because Access does not know
  • 03:58 what to call the calculated fields,
  • 04:00 it uses CountOf and SumOf in front of the fieldnames.
  • 04:05 We can change these labels.
  • 04:08 Go back to the Design View
  • 04:10 by clicking on the icon with the triangle, ruler, and pencil.
  • 04:14 Click in the field for InvoiceID.
  • 04:18 Press Shift-F2 to ZOOM it.
  • 04:22 Click on Font button in the lower right corner,
  • 04:24 and set the size to 16.
  • 04:28 Click at the end of the field,
  • 04:30 press the Home key to go to the beginning
  • 04:33 and type #Invoices:
  • 04:39 (click) OK or (press) ENTER.
  • 04:41 Press TAB to move to the Amount (AmtInv) field
  • 04:44 and press Shift-F2 to Zoom.
  • 04:47 Press F2 to toggle to an insertion point,
  • 04:50 press Home to go to the beginning, type Total: [Enter for OK]
  • 04:58 Now when you look at the Datasheet View,
  • 05:00 the column labels are easier to understand.
  • 05:04 Some of the customers
  • 05:06 haven't had their invoices calculated yet.
  • 05:09 Normally it is not a good idea to store calculations
  • 05:13 but for something like an invoice,
  • 05:15 where you don't want amounts to change
  • 05:17 after the invoice is submitted,
  • 05:19 it is ok to store point-in-time values.
  • 05:23 Click the Save button on the Quick Access Toolbar
  • 05:26 and name this query qCustomerInvoiceStats
  • 05:31 If we switch the Navigation Pane
  • 05:33 to Tables and Related Views,
  • 05:35 the new query shows up in the Customers table group
  • 05:39 and in the Invoices table group.
  • 05:43 Aside from a naming convention
  • 05:45 you use to know what objects are,
  • 05:47 you can also recognize them by their icons.
  • 05:51 Datasheet View icon represents a table
  • 05:55 A query is represented by interlocking datasheets.
  • 05:59 The Form icon is a form.
  • 06:01 The Report icon is a report.
  • 06:03 A macro is a script icon.
  • 06:05 The icon for Modules is colored shapes with connecting lines.
  • 06:10 The bottom group is Unrelated Objects
  • 06:13 that are not based on any table.
  • 06:16 These queries obviously are based on tables.
  • 06:20 They were, however, imported.
  • 06:22 Had they been created in this database,
  • 06:25 Access would know what tables they were based on,
  • 06:28 just like it knew for the Customer Invoices query
  • 06:31 that we created.
  • 06:34 If you click with the left mouse button
  • 06:36 on the Navigation Pane header,
  • 06:38 you get a menu with Categories at the top
  • 06:40 and Filters on the bottom.
  • 06:43 Let's go back to viewing by Object Type
  • 06:46 and expand all the groups.
  • 06:48 Right-click for a shortcut menu.
  • 06:52 The best thing about the Navigation Pane
  • 06:54 is the Search Bar.
  • 06:57 Press Ctrl-F to turn it on
  • 06:59 when the focus is in the Navigation Pane.
  • 07:02 As you type (inv),
  • 07:04 object names that have those letters are displayed.
  • 07:07 Give thought to your names.
  • 07:11 In the next lesson, we learn more about
  • 07:13 objects in our Access database,
  • 07:15 and make use of the Search Bar to quickly find things.

Lesson notes are only available for subscribers.

Gift this course