Working with queries and calculated fields.

1a. Find, modify and delete records in a table.
1. Learn how to use the Query window in Design view
2. Create, run, and save queries
3. Update data using a query datasheet
4. Create a query based on multiple tables.
5. Sort data in a query
6. Filter data in a query
7. Specify an exact match condition in a query
8. Change the font size and alternating row color in a datasheet.
9. Use a comparison operator to match a range of values
10, Use the And and Or logical operators
11. Create and format a calculated field in a query
12. Perform calculations in a query using , aggregate functions, and record group calculations
13. Change the display of database objects in the Navigation Pane.

Important Points.
 
1. Updating or maintaing a database is the process of adding, modifying, and deleting records in tables.
2. Navigation mode- selects an entire field value.
3. Editing mode - you can insert or delete characters in a field value.
4. Find command allows you t search a table or queery datasheet. or a form to locate a specific field value or part of a field value.
5. Delete a record- click the row selector and then click the delete button.
6. The + is the expand indicator to display the releated records in the subdatasheet. 
7 There are two types of Queries: Select Queries and Action Queries - Select queries do not change the data in the table whereas action queries do change the data in the table.
8. Rules of Data Normalization - 2sd Rule - Eliminate Redundant Data - If an attribute (field) depends on only part of a multi valued key, remove it to a separate table.
9. When you use query by example (QBE), you give Access an example of the information you are requesting.
10. The field list contains the fields for the table you are querying.
11. In the design grid, you include the fields and record selection criteria for the information you want to see.
12. Sorting is the process of rearranging records in a specified order or sequence.
13. The sort key is the field used to determine the order of records in the data sheet.
14. Ascending Sort - A to Z, Lowest to Highest.
15. Descending Sort - Z to A, Highest to Lowest
16. A sort key is unique if the value of the sort key for each record is different.
17. A sort key is non unique if more than one record can have the same value for the sort key field.
18. A filter is a set of restrictions you place on the records in an open data sheet or form to temporarily isolate a subset of the records.
19. Autofilter enables you to quickly sort and display field values in various ways.
20. Alternate Fill/Back Color button allow you to cange the backgorund color of datasheet rows.
21. Filter by Selection lets you select all or part of a field value in a data sheet or form, and then display only those records that contain the select value in a field.
22. Filter by Form, allows you to select a value from the list arrow that appears when you click any blank field to apply a filter that selects only those records containing that value.
23. A comparison operator asks Access to compare the value in the database field to the condition value and to select all the records for which the relationship is true.

> greater than A > B
< less than A < B
>= greater than or equal to A >= B
<= less that or equal to A <= B
= equal to A = B
<> not <> A
Between..And between two values (inclusive) Between 50 and 325
In( ) in a list of values In("Hall","Seeker")
Like matches a pattern that includes wildcards *, ? like "707*"

24. An exact match occurs when the value in the specified field must match the condition exactly in order for the record to be included in the query results.
25. Logical Operators are used to combine two or more conditions.

Logical Operators
AND, OR, XOR, NOT

AND

A B C Result
True True True True
False True True False
False False True False
False False False False

OR

A B C Result
True True True True
False True True True
False False True True
False False False False


XOR

A

B C Result
True True True False
False True True True
False False True True
False False False False


Not

A Result
True False
False True

26. A calculated field is a field that displays the results of an expression and can be used in a query, form or report. The calculate field does not appear in the database.
27. An expression can contain a combination database fields, constants, numeric and logical operators.

Operator Precedence used in evaluating expressions

High ^ Arithmetic
Unary + or -
* / \ mod
+ -
<, >, <=, >=, =, <> Relational
NOT Logical
AND
Low OR and XOR

28. Parentheses () can be used to change operator precedence.
29. The Zoom box is a large text box for entering text.
30. The Expression Builder is an Access tool that contains an expression box for entering the expression, buttons for common operators, and one or more lists of expression elements, such as table and field names.
31. Aggregate functions perform arithmetic operations on selected records in a database.

Aggregate Function Determines Data Type Supported
AVG Average of field values Autonumber,Currency, Date/Time, Number
Count Number of records selected Autonumber,Currency, Date/Time, Number, Memo, OLE,Text, Yes/No
Max Highest field value Autonumber,Currency, Date/Time, Number,Text
Min Lowest field value Autonumber, Currency, Date/Time, Number, Text
Sum Total of field values Autonumber, Currency, Date/Time, Number
StDev Standard Deviation of Variance Number, Currency
Var Variance of Data Number, Currency

32. The Group By operator divides the selected records into groups based on the values in the specified field.

Best Practice:

Access2007 will let you convert to Access2000, 2003, or Access97. Remember that when you convert the database from 2007 to 2003, 2000 or 97, features supported by the 2007 version will not be  available in the 2000 or 97 version.