CHƯƠNG 2 Creating and Working with a Database

1. CHƯƠNG 2Creating and Working with a Database

2. 1.Planning a Database Example:The same information stored in a poorly designed table and in a well-designed table.

3. 1.Planning a Database Use multiple tables so that you don't duplicate information: The same information stored in a badly designed table and in a well-designed table

4. 1.Planning a Database Here are some guidelines for creating a well-designed database: Determine the Purpose of the Database: This will help determine the tables and fields to include in your database. Determine the Fields You Need:Thisshould be an easy step once you have determined the purpose of your database and have sketched some sample reports and lists.

5. 1.Planning a Database Determine the Tables You Need:Each table in the Database should be based on only one subject Determine the Primary Key: Each record in a table should have a primary key that uniquely identifies it Determine the Relationship between Tables Sketch a Diagram of Your Database

6. 1. Planning a Database Each field or column should contain the same type of information Try to break up information as much as possible Use multiple tables so that you don't duplicate information in the same table Don't use duplicate field names

7. 2.Creating a Database Using the Database Wizard To create a database using the database wizard: Click the new button on the toolbar or select filenew from the menu. Click the on my computer link in the task pane.

8. 2.Creating a Database Using the Database Wizard Click the databases tab in the templates dialog box. Double-click the type of database you want to create. Follow the onscreen instructions and specify what you what to appear in your database

9. 3. Creating a Blank Database To create a new blank database Click the New button on the toolbar SelectBlank Database from the task pane

10. 3. Creating a Blank Database Navigate to the drive and folder where you want to save the new database, then type a name for your new database in the file name box Click create.

11. 4. 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 and then double-click create table by using wizard. Select the type of table you want to create. Follow the onscreen instructions and specify what you want to appear in your database.

12. 4. Creating a Table Using the Table Wizard

13. 5. Modifying a Table and Understanding Data Types A table in Design view

14. 5. 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 from the list.

15. 5. Modifying a Table and Understanding Data Types

16. 5. Modifying a Table and Understanding Data Types

17. 5. Modifying a Table and Understanding Data Types

18. 6. Creating a Query in Design View The Query window in Design view The Query window in Design view

19. 6. Creating a Query in Design View The design grid 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 add. Repeat step 2 as necessary for additional tables or queries, and click close.

20. 6. Creating a Query in Design View 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. 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.

21. 6. Creating a Query in Design View

22. 7. 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.

23. 8. Sorting a Query Using Multiple Fields Example: Creating a sort that sorts by LastName, then by FirstName

24. 8. Sorting a Query Using Multiple Fields To sort a query using multiple fields: Open/display the query in design view. 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, then select a sort order. Repeat steps 2-3 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

25. 9. Developing AND and OR Operators Creating an AND statement Creating an OR statement

26. 9. 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.

27. 10. 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 and select the fields that you want to appear on the form. 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.

28. 10. Creating a Form with the Form Wizard

29. 11. 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 by which to group the report and click next (optional). Specify the field(s) you want to use to sort the report and click next (optional). Select a format for the report and click next. Give your report a name and click finish.

30. 12. Creating Mailing Labels with the Label Wizard 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.

31. 12. Creating Mailing Labels with the Label Wizard Select the product number for your Avery labels

32. 12. Creating Mailing Labels with the Label Wizard Select the fields that you want to appear on the report and enter any text. Click next when you're finished.

33. 13. Database Object Management To cut/copy and paste a database object: Select the database object. Click the cut or Click the 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.

34. 14. Compacting and Repairing a Database Compact Database: Compacting a database rearranges how the database is stored and reduces its file size. Repair Database: Over time, normal wear and tear causes errors to appear in your database, thus affecting its performance Select toolsdatabase utilitiescompact and repair database from the menu