Access Module 3: Maintaining a Database

Split form

Database form that allows you to simultaneously view both simple form and datasheet views of the data.

Action query

Query that adds, deletes, or changes data in a table.

Make-table query

Query that allows you to add query results to a new table.

Append query

Query that allows you to add the results of a query to an existing table.

Delete query

Query that allows you to delete all the records satisfying some criterion.

Update query

Query that allows you to make the same change to all records satisfying some criterion.

Filter

Sorting method that allows you to find only the record or records that satisfy specific criterion.

Format symbol

Special symbol that determines a field's appearance.

How Do You Determine Whether to Use a Query or a Filter?

- If you think that you will frequently want to display records that satisfy this exact criterion, you should consider creating a query whose results only contain the records that satisfy the criterion. To display those records in the future, simply open the query. - If you are viewing data in a datasheet or form and decide you want to restrict the records to be included, it is easier to create a filter than a query. You can create and use the filter while you are viewing the data. - If you have created a filter that you would like to be able to use again, you can save the filter as a query.

Once You Have Decided to Use a Filter, How Do You Determine Which Type of Filter to Use?

- If your criterion for filtering is that the value in a particular field matches or does not match a certain specific value, you can use Filter By Selection. - If your criterion only involves a single field but is more complex (for example, the criterion specifies that the value in the field begins with a certain collection of letters) you can use a common filter. - If your criterion involves more than one field, use Filter By Form. - If your criterion involves more than a single And or Or, or if it involves sorting, you will probably find it simpler to use Advanced Filter/Sort.

What Kind of Decisions Should I Make in Determining Whether to Change the Format of a Datasheet?

- Would totals or other calculations be useful in the datasheet? If so, include the Total row and select the appropriate computations. - Would another gridline style make the datasheet more useful? If so, change to the desired gridlines. - Would alternating colors in the rows make them easier to read? If so, change the alternate fill color. - Would a different font and/or font color make the text stand out better? If so, change the font color and/or the font. - Is the font size appropriate? Can you see enough data at one time on the screen and yet have the data be readable? If not, change the font size to an appropriate value. - Is the column spacing appropriate? Are some columns wider than they need to be? Do some columns not display all the data? Change the column sizes as necessary.

To Specify Referential Integrity

1. - Click Database Tools on the ribbon to display the Database Tools tab. 2. Click the Relationships button (Database Tools tab | Relationships group) to open the Relationships window and display the Show Table dialog box. 3. - Click the Account Manager table (Show Table dialog box), and then click the Add button to add a field list for the Account Manager table to the Relationships window. - Click the Account table (Show Table dialog box), and then click the Add button to add a field list for the Account table to the Relationships window. - Click the Close button (Show Table dialog box) to close the dialog box. - Resize the field lists that appear so all fields are visible. 4. - Drag the Account Manager Number field in the Account Manager table field list to the Account Manager Number field in the Account table field list to display the Edit Relationships dialog box and create a relationship. - Click the 'Enforce Referential Integrity' check box (Edit Relationships dialog box). - Click the 'Cascade Update Related Fields' check box. 5. Click the Create button (Edit Relationships dialog box) to complete the creation of the relationship 6. - Click the Save button on the Quick Access Toolbar to save the relationship you created. - Close the Relationships window.

To Update the Contents of a Record

1. - Click in the Account Name field in the form for account LI268 immediately to the right of the n in Idsen. - Type a hyphen (-) and then type "Fleming" after Idsen. - Press the tab key to complete the change and move to the next field.

To Close the Datasheet Without Saving the Format Changes

1. Close the Account Manager table. 2. Click the No button in the Microsoft Access dialog box when asked if you want to save your changes.

To Use Advanced Filter/Sort

1. - Click the Advanced button (Home tab | Sort & Filter group) to display the Advanced menu, and then click 'Clear All Filters' on the Advanced menu to clear the existing filter. - Click the Advanced button to display the Advanced menu a second time. - Click 'Advanced Filter/Sort' on the Advanced menu. - Expand the size of the field list so all the fields in the Account table appear. - Add the Account Name field and select Ascending as the sort order to specify the order in which the filtered records will appear. - Include the Account Manager Number field and enter "58" as the criterion. - Include the Current Due field and enter ">3000" as the criterion in the Criteria row and "0" as the criterion in the or row. 2. - Click the Toggle Filter button (Home tab | Sort & Filter group) to toggle the filter so that only records that satisfy the criteria will appear. (Experiment: Select 'Advanced Filter/Sort' again and enter different sorting options and criteria. In each case, toggle the filter to see the effect of your selection. When done, change back to the sorting options and criteria you entered in Step 1). 3. - Close the Account table. When asked if you want to save your changes, click the No button.

To Use a Multivalued Lookup Field

1. - Click the Services Needed field on the first record to display the arrow. - Click the arrow to display the list of available services. 2. Click the Bck, Ben, Com, Pay, and Rsk check boxes to select the services for the first account. 3. - Click the OK button to complete the selection. - Using the same technique, enter the services for the remaining accounts. - Double-click the right boundary of the field selector for the Services Needed field to resize the field so that it best fits the data. 4. - Save the changes to the layout by clicking the Save button on the Quick Access Toolbar. - Close the Account table.

To Use an Update Query

1. - Create a new query for the Account table, and ensure the Navigation Pane is closed. - Click the Update button (Query Tools Design tab | Query Type group) to specify an update query, double-click the Account Type field to select the field, click the Update To row in the first column of the design grid, and then type SER as the new value. 2. Click the Run button (Query Tools Design tab | Results group) to run the query and update the records. 3. Click the Yes button to make the changes. (Experiment: Create an update query to change the account type to COM. Enter a criterion to restrict the records to be updated, and then run the query. Open the table to view your changes. When finished, create and run an update query to change the account type to SER on all records. Close the query. Because you do not need to use this update query again, do not save the query). Other Ways: 1. Right-click any open area in upper pane, point to Query Type on shortcut menu, click Update Query on Query Type submenu.

To Include Multiple Values on One Row of a Query

1. - Create a query for the Account table and close the Navigation Pane. - Include the Account Number, Account Name, Account Type, and Services Needed fields. 2. Run the query and view the results. 3. Save the query as m03q01.

To Add a New Field

1. - If necessary, open the Navigation Pane, open the Account table in Design view, and then close the Navigation Pane. - Right-click the row selector for the Amount Paid field, and then click Insert Rows on the shortcut menu to insert a blank row above the selected field. 2. Click the Field Name column for the new field to produce an insertion point. Type "Account Type" as the field name and then press the tab key. Other Ways: Click Insert Rows button (Table Tools Design tab | Tools group).

To Include Totals in a Datasheet

1. - Open the Account Manager table in Datasheet view and close the Navigation Pane. - Click the Totals button (Home tab | Records group) to include the Total row in the datasheet. - Click the Total row in the Salary column to display an arrow. - Click the arrow to display a menu of available calculations. 2. - Click Sum to calculate the total of the salary amounts. - Resize the Salary column to best fit the total amount. (Experiment: Experiment with other statistics. When finished, once again select the sum).

To Use a Subdatasheet

1. - Open the Account Manager table in Datasheet view and close the Navigation Pane. 2. Click the plus sign in front of the row for account manager 35 to display the subdatasheet. (Experiment: Display subdatasheets for other account managers. Display more than one subdatasheet at a time. Remove the subdatasheets from the screen). 3. - If requested by your instructor, replace the city and state for account manager 35 with your city and state. - Close the Account Manager table.

To Change Gridlines in a Datasheet

1. - Open the Account Manager table in Datasheet view, if it is not already open. - If necessary, close the Navigation Pane. - Click the datasheet selector, the box in the upper-left corner of the datasheet, to select the entire datasheet. 2. Click the Gridlines button (Home tab | Text Formatting group) to display the Gridlines gallery. 3. Click Gridlines: Horizontal in the Gridlines gallery to include only horizontal gridlines. (Experiment: Experiment with other gridline options. When finished, once again select horizontal gridlines).

To Change the Contents of a Field

1. - Open the Account table in Datasheet view and ensure the Navigation Pane is closed. - Click in the Street field for account BL235 immediately to the left of the letter, G, of Granger to produce an insertion point. - Change the name of the street from 100 Granger Hwy. to 100 South Granger Hwy. by typing "South" and a space, and then pressing the tab key. - Resize the Street column to best fit the new data by double-clicking the right boundary of the field selector for the Street field, that is, the column heading. - Save the changes to the layout by clicking the Save button on the Quick Access Toolbar. - Close the Account table.

To Use the Ascending Button to Order Records

1. - Open the Account table in Datasheet view. - Click the City field on the first record to select the field. 2. Click the Ascending button (Home tab | Sort & Filter group) to sort the records by City. 3. - Close the Account table. - Click the No button (Microsoft Access dialog box) when asked if you want to save your changes. - If desired, sign out of your Microsoft account. - Exit Access. Other Ways: 1. Right-click field name, click Sort a to z (for ascending) or Sort z to a (for descending). 2. Click the field selector arrow and click Sort a to z or Sort z to a.

To Update a Report to Reflect Changes in the Table

1. - Open the Navigation Pane, if necessary, and then right-click the Account Financial Report in the Navigation Pane to display a shortcut menu. - Click Layout View on the shortcut menu to open the report in Layout view. - Close the Navigation Pane. - Click the 'Add Existing Fields' button (Report Layout Tools Design tab | Tools group) to display a field list. 2. Drag the Account Type field in the field list into the report until the line to the left of the pointer is between the Account Name and Amount Paid fields on the form. 3. Release the mouse button to place the field. - Using the same technique, add the Total Amount field between the Current Due and Account Manager Number fields. - Click the 'Add Existing Fields' button (Report Layout Tools Design tab | Tools group) to remove the field list from the screen. - Click Page Setup on the ribbon to display the Report Layout Tools Page Setup tab. - Click the Landscape button (Report Layout Tools Page Setup tab | Page Layout group) to change the orientation of the report to Landscape. 4. - Click the Save button on the Quick Access Toolbar to save your changes. - Close the report.

To Use Filter by Selection

1. - Open the Navigation Pane. - Open the Account table, and close the Navigation Pane. - Click the City field on the second record to specify Granger as the city. 2. Click the Selection button (Home tab | Sort & Filter group) to display the Selection menu. 3. Click Equals "Granger" to select only those accounts whose city is Granger.

To Include Multiple Values on Multiple Rows of a Query

1. - Return to Design view and ensure that the Account Number, Account Name, Account Type, and Services Needed fields are included in the design grid. - Click the Services Needed field to produce an insertion point, press the right arrow key as necessary to move the insertion point to the end of the field name, and then type a period. - If the word, Value, did not automatically appear after the period, type the word "Value" after the period following the word, Needed, to use the Value property. 2. Run the query and view the results. 3. - Save the query as a new object in the database named m03q02. - Close the query.

To Add a Calculated Field

1. - Right-click the row selector for the Account Manager Number field, and then click Insert Rows on the shortcut menu to insert a blank row above the selected field. - Click the Field Name column for the new field. - Type "Total Amount" as the field name, and then press the tab key. - Click the Data Type arrow to display the menu of available data types. 2. Click Calculated to select the Calculated data type and display the Expression Builder dialog box. 3. - Double-click the Amount Paid field in the Expression Categories area (Expression Builder dialog box) to add the field to the expression. - Type a plus sign (+). - Double-click the Current Due field in the Expression Categories area (Expression Builder dialog box) to add the field to the expression. 4. Click the OK button (Expression Builder dialog box) to enter the expression in the Expression property of the Total Amount.

To Create a Split Form

1. - Run Access and open the database named PrattLast Associates from your hard disk, OneDrive, or other storage location. - Open the Navigation Pane if it is currently closed. - If necessary, click the Account table in the Navigation Pane to select it. - Click Create on the ribbon to display the Create tab. - Click the More Forms button (Create tab | Forms group) to display the More Forms menu. 2. - Click Split Form to create a split form based on the Account table. - Close the Navigation Pane. 3. Click the Form View button on the Access status bar to display the form in Form view rather than Layout view. (Experiment: Click the various Navigation buttons (First record, Next record, Previous record, Last record, and 'New (blank) record') to see each button's effect. Click the Current Record box, change the record number, and press the ENTER key to see how to move to a specific record). 4. - Click the Save button on the Quick Access Toolbar to display the Save As dialog box. - Type "Account Split Form" as the form name. 5. - Click the OK button (Save As dialog box) to save the form. Other Ways: Right-click tab for form, click Form View on shortcut menu.

To Specify a Required Field

1. - Select the Account Name field by clicking its row selector. - Click the Required property box in the Field Properties pane, and then click the down arrow that appears. - Click Yes in the list to make Account Name a required field.

To Specify a Format

1. - Select the Account Number field. - Click the Format property box, erase the current format (@), if it appears on your screen, and then type ">".

To Specify a Range

1. - Select the Current Due field by clicking its row selector, click the Validation Rule property box to produce an insertion point, and then type ">=0" and "<=10000" as the rule. - Click the Validation Text property box to produce an insertion point, and then type "Must be at least $0.00 and at most $10,000.00" as the text.

To Find Duplicate Records

1. Click Create on the ribbon, and then click the Query Wizard button (Create tab | Queries group). 2. When Access displays the New Query dialog box, click the 'Find Duplicates Query Wizard' and then click the OK button. 3. Identify the table and field or fields that might contain duplicate information. 4. Indicate any other fields you want displayed. 5. Finish the wizard to see any duplicate records.

To Find Unmatched Records

1. Click Create on the ribbon, and then click the Query Wizard button (Create tab | Queries group). 2. When Access displays the New Query dialog box, click the 'Find Unmatched Query Wizard' and then click the OK button. 3. Identify the table that might contain unmatched records, and then identify the related table. 4. Indicate the fields you want displayed. 5. Finish the wizard to see any unmatched records.

To Use a Form to Add Records

1. Click the 'New (blank) record' button on the Navigation bar to enter a new record, and then type the data for the new record. Press the TAB key after typing the data in each field, except after typing the data for the final field (Account Manager Number). 2. - Press the tab key to complete the entry of the record. - Close the form. Other Ways: 1. Click New button (Home tab | Records group). 2. Press ctrl+plus sign (+).

To Clear a Filter

1. Click the Advanced button (Home tab | Sort & Filter group) to display the Advanced menu. 2. Click 'Clear All Filters' on the Advanced menu.

To Use the Ascending Button to Order Records on Multiple Fields

1. Click the field selector at the top of the major key column to select the entire column. 2. Hold down the shift key and then click the field selector for the minor key column to select both columns. 3. Click the Ascending button to sort the records.

To Use an Append Query

1. Create a query for the Source table. 2. In Design view, indicate the fields to include, and then enter any necessary criteria. 3. View the query results to be sure you have specified the correct data, and then return to Design view. 4. Click the Append button (Query Tools Design tab | Query Type group). 5. When Access displays the Append dialog box, specify the name of the Destination table and its location. Run the query by clicking the Run button (Query Tools Design tab | Results group). 6. When Access indicates the number of records to be appended, click the OK button.

To Use a Make-Table Query

1. Create a query for the Source table. 2. In Design view, indicate the fields to include, and then enter any necessary criteria. 3. View the query results to be sure you have specified the correct data, and then return to Design view. 4. Click the Make Table button (Query Tools Design tab | Query Type group). 5. When Access displays the Make Table dialog box, specify the name of the Destination table and its location. Run the query by clicking the Run button (Query Tools Design tab | Results group). 6. When Access indicates the number of records to be inserted, click the OK button.

To Use a Delete Query

1. Create a query for the table containing the records to be deleted. 2. In Design view, indicate the fields and criteria that will specify the records to delete. 3. Click the Delete button (Query Tools Design tab | Query Type group). 4. Run the query by clicking the Run button (Query Tools Design tab | Results group). 5. When Access indicates the number of records to be deleted, click the Yes button.

To Change a Field Size

1. Open the Account table in Design view and close the Navigation Pane. 2. Select the Street field by clicking its row selector. 3. Click the Field Size property to select it, delete the current entry (20), and then type "25" as the new field size.

To Update a Form to Reflect Changes in the Table

1. Open the Navigation Pane, and then right-click the Account Form in the Navigation Pane to display a shortcut menu. 2. Click Delete on the shortcut menu to delete the selected form, and then click the Yes button in the Microsoft Access dialog box to confirm the deletion. 3. Click the Account table in the Navigation Pane to select the table. 4. If necessary, click Create on the ribbon to display the Create tab. 5. Click the Form button (Create tab | Forms group) to create a simple form. 6. Click the Save button on the Quick Access Toolbar to save the form. 7. Type "Account Form" as the form name, and then click the OK button to save the form. 8. Close the form.

To Modify Single Valued or Multivalued Lookup Fields

1. Open the table in Design view and select the field to be modified. 2. Click the Lookup tab in the Field Properties pane. 3. Change the list in the Row Source property to the desired list of values.

To Move a Field

1. Open the table in Design view. 2. Click the row selector for the field to be deleted. 3. Drag the field to the desired position. 4. Release the mouse button to place the field in the new position.

To Delete a Field

1. Open the table in Design view. 2. Click the row selector for the field to be deleted. 3. Press the delete key. 4. When Access displays the dialog box requesting confirmation that you want to delete the field, click the Yes button.

To Add a Multivalued Field

1. Right-click the row selector for the Amount Paid field, and then click Insert Rows on the shortcut menu to insert a blank row. 2. Click the Field Name column for the new field, type "Services Needed" as the field name, and then press the tab key. 3. Click the Data Type arrow to display the menu of available data types for the Services Needed field, and then click Lookup Wizard in the menu of available data types to start the Lookup Wizard. 4. Click the 'I will type in the values that I want.' option button to indicate that you will type in the values. 5. Click the Next button to display the next Lookup Wizard screen. 6. Click the first row of the table (below Col1), and then type "Bck" as the value in the first row. 7. Enter the remaining values from the first column in Table 3-1. Before typing each value, press the down arrow key to move to a new row. 8. Click the Next button to display the next Lookup Wizard screen. 9. Ensure that Services Needed is entered as the label for the lookup field. 10. Click the 'Allow Multiple Values' check box to allow the user to enter multiple values. 11. Click the Finish button to complete the definition of the Lookup Wizard field.

To Print a Report

1. With the Account Financial Report selected in the Navigation Pane, click File on the ribbon to open the Backstage view. 2. Click the Print tab in the Backstage view to display the Print gallery. 3. Click the Quick Print button to print the report.

To Use a Common Filter

1. - Click the City arrow to display the common filter menu. -Point to the Text Filters command to display the custom text filters. 2. - Click Begins With to display the Custom Filter dialog box. - Type "G" as the 'City begins with' value. (Experiment: Try other options in the common filter menu to see their effects. When done, once again select those accounts whose city begins with G). 3. Click the OK button to filter the records. 4. Click the Toggle Filter button (Home tab | Sort & Filter group) to toggle the filter and redisplay all records. Other Ways: Right-click field, click Text Filters on shortcut menu.

To Use a Lookup Field

1. - Open the Account table in Datasheet view and ensure the Navigation Pane is closed. - Click in the Account Type field on the second record (BL235) to display an arrow. - Click the arrow to display the drop-down list of available choices for the Account Type field. 2. - Click IND to change the value. - In a similar fashion, change the values on the other records.

To Search for a Record

1. - Open the Navigation Pane. - Scroll down in the Navigation Pane, if necessary, so that Account Split Form appears on your screen, right-click Account Split Form to display a shortcut menu, and then click Open on the shortcut menu to open the form in Form view. - Close the Navigation Pane. 2. - Click the Find button (Home tab | Find group) to display the Find and Replace dialog box. - Type "LI268" in the Find What text box (Find and Replace dialog box). -Click the Find Next button to find account LI268 and display the record in the form. 3. Click the Cancel button (Find and Replace dialog box) to remove the dialog box from the screen. Other Ways: Press ctrl+f.

To Specify a Collection of Legal Values

1. - With the Account Type field selected, click the Validation Rule property box to produce an insertion point and then type "=SER or =NON or =IND" as the validation rule. - Click the Validation Text property box, and then type "Must be SER, NON, or IND" as the validation text.

To Use Filter by Form

1. Click the Advanced button (Home tab | Sort & Filter group) to display the Advanced menu. 2. - Click 'Clear All Filters' on the Advanced menu to clear the existing filter. - Click the Advanced button again to display the Advanced menu a second time. - Click 'Filter By Form' on the Advanced menu. - Click the blank row in the Current Due field, and then type "<2000" to enter a criterion for the Current Due field. - Click the Account Manager Number (AM #) field, click the arrow that appears, and then click 35. 3. Click the Toggle Filter button (Home tab | Sort & Filter group) to apply the filter. (Experiment: Select 'Filter by Form' again and enter different criteria. In each case, toggle the filter to see the effect of your selection. When done, once again select those accounts whose Current Due amounts are less than (<) 2000 and whose account manager number is 35). Other Ways: Click the Advanced button (Home tab | Sort & Filter group), click Apply Filter/Sort on Advanced menu.

To Save the Changes and Close the Table

1. Click the Save button on the Quick Access Toolbar to save the changes. 2. Close the Account table.

To Save the Validation Rules, Default Values, and Formats

1. Click the Save button on the Quick Access Toolbar to save the changes. 2. If a Microsoft Access dialog box appears, click the No button to save the changes without testing current data. 3. Close the Account table.

To Create a Lookup Field

1. If necessary, click the Data Type column for the Account Type field, and then click the Data Type arrow to display the menu of available data types. 2. Click Lookup Wizard, and then click the 'I will type in the values that I want.' option button (Lookup Wizard dialog box) to indicate that you will type in the values. 3. Click the Next button to display the next Lookup Wizard screen. 4. - Click the first row of the table (below Col1), and then type "SER" as the value in the first row. - Press the down arrow key, and then type "NON" as the value in the second row. -Press the down arrow key, and then type "IND" as the value in the third row. 5. - Click the Next button to display the next Lookup Wizard screen. - Ensure Account Type is entered as the label for the lookup field and that the 'Allow Multiple Values' check box is NOT checked. 6. Click the Finish button to complete the definition of the lookup field.

To Delete a Record

1. With the Account Split Form open, click the record selector in the datasheet for account JM323 to select the record. 2. Press the delete key to delete the record. 3. - Click the Yes button to complete the deletion. - Close the Account Split Form. Other Ways: 1. Click Delete arrow (Home tab | Records group), click Delete Record on Delete menu.

To Change the Colors and Font in a Datasheet

1. With the datasheet for the Account Manager table selected, click the 'Alternate Row Color' button arrow (Home tab | Text Formatting group) to display the color palette. 2. - Click Brown in the upper-right corner of Standard Colors to select brown as the alternate color. - Click the Font Color button arrow, and then click the dark blue color that is the second color from the right in the bottom row in the Standard Colors to select the font color. - Click the Font arrow, scroll down in the list until Bodoni MT appears, and then select Bodoni MT as the font. (If it is not available, select any font of your choice.) - Click the Font Size arrow and select 10 as the font size. (Experiment: Experiment with other colors, fonts, and font sizes. When finished, return to the options selected in this step).

Subdatasheet

A datasheet shown within another datasheet used to display related records.

Foreign key

A field in one table whose values are required to match the primary key of another table.

Calculated field (computed field)

A field that can be computed from other fields is called this.

Legal values (allowable values)

A validation rule that specifies that the values in a field must come from a certain collection of possibilities.

Range of values

A validation rule that specifies that the values in a field must fall within a certain range.

Default value

A value that Access will display on the screen in a particular field before the user begins adding a record.

Cascade the delete

Allow a deletion, but then automatically delete all related records.

Cascade the update

Allow an update, but then automatically make the same update to all related records.

To Toggle a Filter

Click the Toggle Filter button (Home tab | Sort & Filter group) to toggle the filter and redisplay all records.

To Remove Totals from a Datasheet

Click the Totals button (Home tab | Records group), to remove the Total row from the datasheet.

Bck

Description: Background Checks. (Service Abbreviations and Descriptions).

Ben

Description: Benefits Administration. (Service Abbreviations and Descriptions).

Com

Description: Compliance (Regulatory). (Service Abbreviations and Descriptions).

Mgt

Description: HR Management. (Service Abbreviations and Descriptions).

Tch

Description: HR Technology. (Service Abbreviations and Descriptions).

Pay

Description: Payroll. (Service Abbreviations and Descriptions).

Rec

Description: Recruiting. (Service Abbreviations and Descriptions).

Rsk

Description: Risk Management. (Service Abbreviations and Descriptions).

Trn

Description: Training. (Service Abbreviations and Descriptions).

Wrk

Description: Workman's Compensation. (Service Abbreviations and Descriptions).

Required field

Field in which a user must enter data; neglecting to do so generates an error.

Lookup field

Field that allows the user to select from a list of values when updating the contents of the field.

Multivalued fields

Fields that can contain more than one value.

1. You can apply a filter to the results of a query just as you can apply a filter to a table. 2. Once you create a filter using Advanced Filter/Sort, you can save the filter settings as a query by using the 'Save as Query' command on the Advanced menu. 3. You can restore filter settings that you previously saved in a query by using the 'Load from Query' command on the Advanced menu.

Filters and queries are related in what three ways?

Common filters

Filters that are provided by Access to allow you to filter for specific values or for a range of values.

Searching

Looking for records that satisfy some criteria.

Maintaining the database

Modifying a database's data to keep it up to date by adding new records, changing the data for existing records, and deleting records.

One-to-many relationship

ONE record in the first table is related to, or matches, MANY records in the second table, but each record in the second table is related to only ONE record in the first.

Restructure the database

Part of database maintenance that involves adding new fields to a table, changing the characteristics of existing fields, and removing existing fields, as well as creating validation rules and referential integrity.

Validation rules

Rules that a user must follow when entering data to ensure valid data.

To Specify a Default Value

Select the Account Type field, click the Default Value property box to produce an insertion point, and then type "=SER" as the value

Format

Settings and options that affect the way data appears in a field.

Referential integrity

The property that ensures that the value in a foreign key must match that of another table's primary key.

Structure

The tables, fields, and field characteristics that comprise a database.