Access 10. Create a Query with Calculations
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Show/Hide the Total Row
- To turn on (or off) the Total row, click on the Totals icon.
- The default choice for each field is Group By but it can be changed.
View Navigation Pane by Tables and Related Views
- Right-click on the header of the Navigation Pane
- Choose View By > Tables and Related Views
- In Tables and Related Views, each table has its own group.
Lesson notes are only available for subscribers.