Microsoft Access 2010

1. MicrosoftAccess 2010 Chapter 1 Databases and Database Objects: An Introduction

2. Objectives • Design a database to satisfy requirements • Describe the features of the Access window • Create a database • Create tables in Datasheet and Design views • Add records to a table • Close a database • Open a database • Print the contents of a table • Create and use a query • Create and use a form • Create and print custom reports • Modify a report in Layout view CY Lin

3. Project – Database Creation CY Lin

4. General Project Guidelines Identify the tables Determine the primary keys Determine the additional fields Determine relationships between the tables Determine data types for the fields Identify and remove any unwanted redundancy Determine a storage location for the database Determine additional properties for fields Determine the best method for distributing the database objects CY Lin

5. Creating a Database With a USB flash drive connected, ensure the New tab is selected in the Backstage view and that Blank database is selected in the New gallery Click the Browse button in the right pane to display the File New Database dialog box Type the desired file namein the File New Database dialog box. Do not press the ENTER key after typing the file name Navigate to the desired save location Click the OK button Click the Create button in the right pane of the New gallery CY Lin

6. Creating a Database CY Lin

7. Modifying the Primary Key Right-click the column heading for the desired primary key field (the existing ID field) Type the new desired name for the field Click the white space immediately below the field name to complete the addition of the field Click the Data Type box arrow (Table Tools Fields tab | Formatting group) Click the desired data type CY Lin

8. Modifying the Primary Key Click the Field Size text box (Table Tools Fields tab | Properties group) to select the current field size, and type the new field size Click the Name & Caption button (Table Tools Fields tab | Properties group) Click the Caption text box, and then type the desired caption Click the Description text box, and then type the desired description CY Lin

9. Modifying the Primary Key CY Lin

10. Defining the Remaining Fields Click the Click to Add column heading Click the desired data type Enter the desired field name Click the white space below the field name to complete the change of the name. Click the white space a second time to select the field Change the field size, if necessary CY Lin

11. Saving a Table Click the Save button on the Quick Access Toolbar Type the desired file name Click the OK button CY Lin

12. Viewing the Table in Design View Click the View button arrow (Table Tools Fields tab | Views group) CY Lin

13. Closing the Table Click the Close button for the open table to close the table CY Lin

14. Adding Records to a Table Right-click the table in the Navigation Pane Click the Shutter Bar Open/Close Button to close the Navigation Pane Type the desired values in each field, pressing the TAB key to move to the next field CY Lin

15. Resizing Columns in a Datasheet Point to the right boundary of the field selector so that the mouse pointer becomes a two-headed arrow Double-click the right boundary of the field selector to resize the field so that it best fits the data Save the changes to the layout by clicking the Save button Click the table’s Close button to close the table CY Lin

16. Previewing and Printing the Contents of a Table Open the Navigation Pane and select the table you wish to preview and print Click File to open the Backstage view Click the Print tab in the Backstage view Click the Print Preview button Click the Print button to display the Print dialog box Click the OK button to print the table Click the Close Print Preview button to close the Print Preview window CY Lin

17. Creating a Table in Design View • Open the Navigation Pane • Click Create on the Ribbon • Click the Table Design button (Create tab | Tables group) • Type the desired field name in the Field Name column • Click the Primary Key button if the field is the primary key • Type the desired description in the Description column • Type the desired field size in the Field Size text box • Type the desired caption in the Caption text box CY Lin

18. Creating a Table in Design View CY Lin

19. Importing an Excel Worksheet • Click External Data onthe Ribbon • Click the Excel button (External Data tab | Import & Link group) • Click the Browse button to display the File Open dialog box • Navigate to and select the desired file • Click the Open button (File Open dialog box), which will return you to the Get External Data dialog box • Follow the remaining steps in the wizard to import the Excel worksheet • Choose where to import the data • Preview the data you are importing • Name the table (if importing to a new table) CY Lin

20. Using the Simple Query Wizard to Create a Query Open the Navigation Pane Select the table you wish to query Click Create on the Ribbon Click the Query Wizard button (Create tab | Queries group) to display the New Query dialog box Click the OK button (New Query dialog box) to display the Simple Query Wizard dialog box Follow the remaining steps in the wizard to finish creating the query CY Lin

21. CY Lin

22. Using a Criterion in a Query Right-click the query to open in the Navigation Pane Click Design View on the shortcut menu to open the query Click the Criteria row in the column for the field for which you want to specify the criteria, and then type the criteria CY Lin

23. Using a Criterion in a Query CY Lin

24. Creating a Form Select the table in the Navigation Pane for which you want to create a form Click Create on the Ribbon If the form appears in Layout view, click the Form View button on the Access Status bar Click the Save button on the Quick Access Toolbar Type the desired form name, and then click the OK button Click the Close button for the form to close the form CY Lin

25. Creating a Form CY Lin

26. Creating a Report Select the table in the Navigation Pane for which you want to create the report Click Create on the Ribbon Click the Report button (Create tab | Reports group) to create the report Click the Save button and then type the name of the report Click the OK button CY Lin

27. Creating a Report CY Lin

28. Modifying Column Headings and Resizing Columns Right-click the report in the Navigation Pane you wish to modify and then click Layout View on the shortcut menu If a Field list appears, click the Add Existing Fields button to remove the Field list from the screen Close the Navigation Pane Click the column heading you wish to modify two times Type the new column heading Point to the right/lower boundary of the field you wish to resize until the mouse pointer changes to a two-headed arrow and then drag the boundary to the desired position CY Lin

29. Modifying Column Headings and Resizing Columns CY Lin

30. Adding Totals to a Report Select the field you want to total Click Design on the Ribbon Click the Totals button (Report Layout Tools Design tab | Grouping & Totals group) to display the list of available calculations Click Sum to calculate the sum Click the Save button to save your changes to the report layout CY Lin

31. Adding Totals to a Report CY Lin

32. Changing Database Properties Click File on the Ribbon to open the Backstage view Clickthe Info tab in the Backstage view Click the ‘View and edit database properties’ link in the right pane of the Info gallery Enter the desired database properties Click the OK button to save your changes CY Lin

33. Backing Up a Database Open the database to be backed up Click File on the Ribbon, and then click the Save & Publish tab With Save Database As selected in the File Types area, click Back Up Database in the Save Database As area, and then click the Save As button Selected the desired location in the Save As box. If you do not want the name Access has suggested, enter the desired name in the File name text box Click the Save button to back up the database CY Lin

34. Compacting and Repairing a Database Open the database to be compacted Click File on the Ribbon to open the Backstage view, and then, if necessary, select the Info tab Click the Compact & Repair Database button in the Information gallery to compact and repair the database CY Lin

35. Additional Operations Opening another database Closing a database without exiting Access Saving a database with another name Deleting a table or other object in the database Renaming an object in the database CY Lin