MS Access —

Review: Planning a Database

To Create a Database Using the Database Wizard: Click the New button on the toolbar or select File » New from the menu.

Click the On my computer link in the task pane, click the Databases tab in the Templates dialog box, then double-click the type of database you want to create. Follow the onscreen instructions and specify what you what to appear in your database.

Creating a Blank Database

To Create a New Blank Database: Click the New button on the toolbar, or select File » New from the menu, or press Ctrl + N. Select Blank Database from the task pane, then navigate to the drive and folder where you want to save the new database. Type a name for your new database in the File name box and click Create.

Creating a Table Using the Table Wizard

To Create a New Table Using the Table Wizard: In the database window, click the Tables icon in the Objects bar, double-click Create table by using wizard, then select the type of table you want to create. Follow the onscreen instructions and specify what you what to appear in your database.

Modifying a Table and Understanding Data Types

To Display a Table in Design View: Open the table and click the View button on the toolbar, or, in the database window, click the Tables icon in the Objects bar, select the table, and click the Design button.

To Change the Data Type for a Field: Display the table in Design view, click the field's Data Type box, click the list arrow, and select the data type.

To Save Changes to a Database Object: Click the Save button on the toolbar, or select File » Save from the menu, or press Ctrl + S.

Creating a New Table from Scratch

To Create a Table from Scratch: In the database window, click the Tables icon in the Objects bar and then double-click Create table in Design view. Type a field name in the Field Name column, press Tab, click the list arrow in the Data Type box, and select a data type for the field. Repeat the preceding steps as necessary to add additional fields. When you're finished, close the table window, click Yes to save the table, enter a table name, and then click OK.

Creating a Query in Design View

To Create a Query in Design View: In the database window, click the Queries icon in the Objects bar and then double-click Create query in Design view. Select the table you want to add to the query and click Addrepeat as necessary to add additional tables or queries. When you're finished, click Close. In the field list, double-click each field you want to include in the query or drag the field from the field list onto the design grid. In the design grid, enter any desired search criteria for the field in the Criteria box and uncheck the field's Show box if you don't want it to be displayed in the query results. Close the query window, click Yes to save the query, enter a query name, and then click OK.

Modifying a Query

To Add a Field to a Query: In the field list, double-click each field you want to include in the query or drag the field from the field list onto the design grid.

To Delete a Query Field: Click the top of the field you want to delete and press Delete.

To Rearrange Fields: Position the pointer over the field and then click and drag the field to a new location.

Sorting a Query Using Multiple Fields

To Sort a Query Using Multiple Fields: Open/display the query in Design view, and, if necessary, add the field you want to use to sort the query to the design grid. Click the Sort box list arrow for the first field you want to use to sort the query and select a sort order. Repeat for each additional field you want to use to sort the query, bearing in mind that the fields will be sorted from left to right.

Developing AND and OR Operators

To Create AND/OR Criteria: Open/display the query in Design view, enter your criteria in the appropriate field's first Criteria box. Enter additional criteria as follows: AND: Enter additional criteria for one or more fields in the appropriate field's Criteria box. All AND criteria should appear on the same row. OR: Enter additional criteria for one or more fields in the appropriate field's Criteria box, using a different row for each OR criteria.

Creating a Form with the Form Wizard

To Create a Form Using the Form Wizard: In the database window, click the Forms icon in the Objects bar and then double-click Create form by using wizard. Select the table or query you want to use to create your form, select the fields that you want to appear on the form, and click Next when you're finished. Select the type of form you want to create and click Next. Select a format for your form and click Next. Give your form a name and click Finish.

Creating a Report with the Report Wizard

To Create a Report Using the Report Wizard: In the database window, click the Reports icon in the Objects bar and then double-click Create reports by using wizard. Select the table or query you want to use to create your report and select the fields that you want to appear on the report. Click Next when you're finished. Select a field to group the report by and click Next (optional), then specify the field(s) you want to use to sort the report and click Next (also optional). Select a format for the report and click Next. Give your report a name and click Finish.

Creating Mailing Labels with the Label Wizard

To Create Labels: In the database window, click the Reports icon in the Objects bar, click the New button on the toolbar, select Label Wizard from the list and click OK. Select the table or query you want to use to create your labels, and click OK. Select the font you want to use for your labels and click Next. Select the product number for your labels and click Next. Select the fields that you want to appear on the report, enter any text, and click Next when you're finished. Double-click the field you want to use to sort your labels and click Next. Give your report a name and click Finish.

Database Object Management

To Cut/Copy and Paste a Database Object: Select the database object, click the Cut button or Copy button on the toolbar, click the Paste button on the toolbar, enter a name for the new object, and click OK.

To Rename an Object: Right-click the object, select Rename from the shortcut menu, and enter a new name for the object.

To Delete an Object: Select the object and press the Delete key.

File Management

Basic File Management in the Open Dialog box: Open the Open dialog box by selecting File » Open from the menu, right-clicking the file, and selecting the desired command.

To Change How Files Are Displayed: Click the Views button list arrow and select a view.

Compacting and Repairing a Database

To Compact and Repair a Database: Select Tools » Database Utilities » Compact and Repair Database from the menu.

Converting an Access Database

To Convert an Access Database: Close the database you want to convert and select Tools » Database Utilities » Convert Database and select the appropriate file format from the menu. Browse to and double-click the Access database file you want to convert. Type a name for the converted Access database file and click Save. Click OK to acknowledge the warning message.

Quiz

  1. Which of the following is NOT a step in planning a database?

    1. Determine the fields you'll need and their data type.

    2. Determine the tables you'll need.

    3. Use the Database Planning Wizard to help determine the structure of your database.

    4. Determine the purpose of the database: the information you want to put into it and the reports you want to come out of it.

  2. Which of the following statements is NOT true?

    1. The Database Wizard steps you through the process of creating a database and provides you with ready-to-use tables, forms, queries, pages, and reports.

    2. Datasheet View lets you view and modify the structure of any database object.

    3. The Table Wizard asks you a series of questions about what you want to appear in a table and then creates the table for you.

    4. You can add criteria to a query to determine which records are displayed.

  3. Which of the following is NOT a data type?

    1. Text.

    2. Number.

    3. Picture/Graphic.

    4. Date/Time.

  4. You can add a field to a query without displaying it in the query results. (True or False?)

  5. Which of the following statements is NOT true? (Select all that apply.)

    1. To add a field to a query, double-click the field from the field list.

    2. Selecting the asterisk (*) in a query is the same as selecting all of a table's fields.

    3. You can only specify one set of criteria for each queryfor example, to display customers from Texas AND from Minnesota you would have to create two separate queries.

    4. You can sort a query's records by clicking the Sort box list arrow for the field you want to use to sort the query and select a sort order.

  6. The fastest and easiest way to create a form or report is with the Form Wizard or the Report Wizard. (True or False?)

  7. Microsoft Word is required in order to print mailing labels with Microsoft Access. (True or False?)

  8. Which two of the following statements are NOT true? (Select all that apply.)

    1. Just as you can with files, you can cut, copy, paste, rename, and delete Microsoft Access database objects.

    2. Whenever you don't know how to do something to a database object, right-click the object. A shortcut menu listing everything you can do to the object will appear.

    3. When entered in the criteria row of a query design grid, the expression "MN" would display only those records equal to "MN."

    4. Avery labels are a nonstandard product and should never be used for mailing labels.

  9. What is the maximum length a text field can be?

    1. 512 characters.

    2. There is no limit to how long a text field can be.

    3. 50 characters.

    4. 255 characters.

  10. What is the memo data type field used for?

    1. To add an electronic Post-It� Note reminder to any record.

    2. For long text entries of one or more sentences.

    3. For short text entries of no more than 255 characters.

    4. To store objects created in other programs such as a graphic or Microsoft Word document.

  11. Which of the following criterion would find records whose Personality field does not equal "Nice"?

    1. Nice.

    2. NOT Nice.

    3. IS NOT Nice.

    4. "Nice".

  12. What happens when you add the asterisk (*) from any Field List to a query?

    1. The query uses the records from the table without displaying them.

    2. The query sorts the table's records in the order you specify.

    3. The query will include every field from the table.

    4. The table will not include any fields from the table.

  13. You want to sort a query by a table's Last Name field. In order to do this, the Last Name field MUST appear in the displayed results of the query. (True or False?)

  14. Where do reports and forms get their information from? (Select all that apply.)

    1. Tables.

    2. Queries.

    3. Forms.

    4. Modules.

  15. What is the first step in creating a form or report with the Form Wizard or Report Wizard?

    1. Selecting how the form or report should be formatted.

    2. Selecting the underlying table or query on which you want to base the form or report.

    3. Reading several screens of mostly useless information and clicking Next.

    4. Selecting the fields that you want to appear in the form or report.

Homework

  1. Start Microsoft Access.

  2. Create a new blank database named Homework 2.

  3. Create a new table in Design View that contains the following fields:

    Field Name Data Type
    Last

    Text

    First

    Text

    Phone

    Text

    Age

    Number

  4. Save the table as Phone Numbers.

  5. Use the Forms Wizard to create and save a columnar form named InsuranceClaim, using the Phone Numbers table as the data source.

  6. Use the Reports Wizard to create and save a tabular report named InsuranceClaim, using the Phone Numbers table as the data source.

Quiz Answers

  1. C. Since there isn't a Database Planning Wizard, you can't use it to help determine the structure of a database.

  2. B. Design View lets you view and modify the structure of any database object. Datasheet View lets you view the records in a table, query, or form in a grid format.

  3. C. There isn't a Picture/Graphic field in Microsoft Access, although OLE fields can store pictures and graphics in addition to other files created with external programs.

  4. True. You can include a field in a query without displaying it in the query results by unchecking its Show box.

  5. C. You can specify additional AND / OR criteria in a query by entering them in the appropriate AND / OR criteria rows.

  6. True. Once you create a form or report using the Form Wizard or the Report Wizard, you can modify it to better suit your needs.

  7. False. The Label Wizard, included with Microsoft Access, makes creating mailing labels a snap.

  8. C and D. The expression "MN" would display only those records that are not equal to "MN," and Avery is the standard of the label industry.

  9. D. A text field can have a maximum length of 255 characters.

  10. B. The memo data field type is used for long text entries of one or more sentences.

  11. D.

  12. C. Adding the asterisk (*) from a Field List is the same as adding every field from the table.

  13. False. You can sort the results of a query without displaying the field you used to sort the query.

  14. A and B. Reports and forms get their information from tables and queries.

  15. B. The first step in creating a form or report with the Form Wizard or Report Wizard is to select the underlying table or query for the form or report.