Best Practices in MS Access

MS Access 2007 Users Guide ACCESS 2007 – BASICS Best Practices in MS Access Access 2007 - Basics Page i TABLE OF CONTENTS Introduction What is a Database? Overview 1 1 1 The Access Database Management System 1 Tables in Access 2007 2 Getting Started 2 Launching The Access Application 2 Creating a New Database 2 Access Work Area 3 The New Look in Access 2007 4 Using the Ribbon 4 Customizing the Quick Access Toolbar 4 Printing 6 Table in Datasheet View 6 Name the Fields 6 Enter the Data 7 Save the Table 7 Naming Conventions 8 The Leszynski Naming Convention 8 Object Naming Conventions 9 Access Table Exercise 9 Table Using Design View 10 Create a New Table 10 Entering Field Name, Data Type and Description 10 Saving a Table 11 Create a Query 11 Create a New Query 11 Enter Query’s First Criterion 12 Enter Query’s Second Criterion 12 Enter Query’s Third Criterion 12 Run and Save the Query 13 Create a Form Access 2007 - Basics 14 Page ii Creating a Form Create a Report 14 15 Producing a Report 15 Other Useful Functions 16 Insert a Column or Field in Datasheet View 16 Delete a Column or Field in Datasheet View 16 Move a Column in Datasheet View 16 Changing Column Width in Datasheet View 17 Changing Row Height in Datasheet View 17 Hiding a Column in Datasheet View 17 Unhiding a Column in Datasheet View 17 Removing Gridlines in Datasheet View 17 Selecting a Font in Datasheet View 17 Delete a Record in Datasheet View 17 Changing the Contents of a Cell 18 Sorting the Data in a Datasheet 18 Printing the Datasheet 18 Closing and Exiting Access 18 Closing a Database 18 Exiting Access 18 Class Exercises 19 Create a Table 19 How to Learn More 21 Microsoft on the Web 21 For Beginning Access Users 21 For Access Users New to 2007 21 Import Data from Excel to Access 22 Printed Material 22 Training and Support 22 IT Training 22 Troubleshooting and Support 22 Access 2007 - Basics Page iii INTRODUCTION This training guide will acquaint you with the fundamentals of Microsoft Access 2007. This guide provides a basic understanding of database functions and capabilities, and the tools necessary to begin your own exploration with Access 2007. What is a Database? A database is a collection of related data organized into fields, records and tables that has been created for a particular purpose. Data, such as a person’s last name or zip code, is stored in fields and related fields are organized into records. Identically structured records are then collected into tables. The data stored in this table can then be sorted and searched, and useful information in the form of reports can be produced. As an example, the table below contains fields for ID, First Name, Last Name, Phone #, SS# and Donation. Example A - Donation Table Note: This table is used throughout this guide to demonstrate the various functions of Microsoft Access 2007. OVERVIEW The Access Database Management System The concept of a database is more broadly defined within the Microsoft Access 2007 environment. An Access database not only consists of data, fields, records, and tables but also includes queries and reports created as a result of manipulating stored data—it is a complete database management system (DBMS). Additionally, the Microsoft Access 2007 DBMS can not only organize and manage a table of records (sometimes called a flat file database), but can also manage and organize many tables possessing common components into a relational database. Access 2007 - Basics Page i Tables in Access 2007 Below, is a table in the Access database (see Figure 1). It is comprised of rows and columns. The columns are called fields and the rows are referred to as records. The first column is labeled “ID” and the second column is temporarily labeled “Add New Field” which allows you to add more information in the record. The columns that follow are not labeled. Figure 1 - Table within the Access Database GETTING STARTED Launching The Access Application 1. Click the Start button on the taskbar located at the bottom, left-corner of your screen. 2. Point to All Programs. 3. Select Microsoft Office. 4. Select Microsoft Office Access 2007. The Access application will launch and open. Creating a New Database 1. After opening the Access application, select Blank Database (see Figure 1, below). Figure 1 - Selecting Blank Database Note: an alternate way to navigate would be to click on the Office button and then select New. 2. Under the Blank Database section, in the File Name field, type the file name My First Database (see Figure 2, next page). Access 2007 - Basics Page 2 Figure 2 - Creating a Database 3. Click on the Create button. Access Work Area Figure 2 - The Access Work Area 1. Quick Access Toolbar - contains shortcuts for the most commonly used tools and can be customized. 2. Microsoft Office Button - contains common file and system commands. 3. Tab Bar - contains tabs that display tools and commands in the Ribbon. 4. Ribbon - contains groups of tools for use with Access 2007. 5. Datasheet Area - displays the current database. 6. Shutter Bar - displays a list of database projects created. 7. Status Bar - contains database information and shortcuts. Access 2007 - Basics Page 3 THE NEW LOOK IN ACCESS 2007 The Office 2007 suite of software, which includes Excel, Word, Access and PowerPoint, has a very different look and feel. The traditional toolbars as we know them in previous versions have been replaced with what is now called a Ribbon. This section of the documentation will orient you to the new terminology used and where to locate necessary elements. Using the Ribbon The Ribbon contains commands grouped by task. Figure 1 - The Ribbon • • • • The Tab Bar - contains tabs that display tools and commands in the Ribbon. Each tab displays different functionalities. The Command Button - is the short cut icon on the Ribbon that performs a specific function. The Group - contains functions related to that specific grouping. The Launcher group. opens a dialog box that provides more options for the selected Figure 2 - Home Tab / Font Group Launcher Customizing the Quick Access Toolbar The Quick Access toolbar can be customized to meet your personal needs. 1. Click the Customize Quick Access Toolbar icon (see Figure 3). Access 2007 - Basics Page 4 Figure 3 - Quick Access Toolbar Icon 2. The Customize Quick Access Toolbar menu appears. Figure 4 - The Customize Quick Access Toolbar Menu 3. Select More Commands from the resulting menu. 4. The Quick Access Toolbar Access Options dialog box appears (see Figure 5, below). Figure 5 - Quick Access Toolbar Access Options Access 2007 - Basics Page 5 5. Click the down arrow on the Choose commands from dialog field box and select a category from the resulting menu 6. On the left, select the command you want to add and click the Add button. This will add the command to the box on the right. The commands that you see on the right, appear in the Quick Access Toolbar. 7. To remove a command from the toolbar, select a command on the right by clicking on it, and then click on the Remove button to remove it. 8. Click the OK button when you are finished. Note: to quickly add a default command button to the Quick Access toolbar, click the Customize Quick Access Toolbar button and select a command from the resulting menu. Printing 1. Select the Microsoft Office button located in the upper, left corner of the window. 2. Simply mouse over the Print command on the menu (see Figure 6, below). 3. Select the appropriate print command from the submenu. Figure 6 - Office Button / Print Command TABLE IN DATASHEET VIEW After starting Access and creating a new database, do the following to create a new table: Name the Fields Enter the field names (see Example A, from page 4 of this guide). 1. Double-click the current field name (see Figure 1, below). Access 2007 - Basics Page 6 Figure 1 - Add a New Field 2. Enter a new field name (see Figure 2, below) Figure 2 - Entering a First Name Field Note: field names can be up to 64 characters long. 3. Press Tab to move to the next field. 4. Enter the other field names. 5. When complete, press Enter. Enter the Data 1. Click a datasheet cell. 2. Enter the data (see Example A, page 4), starting with Tom Cruise. a. Press the Tab or Enter key to move to the next field. b. Hold the Shift key and tap the Tab key to move back to the previous field. c. When done with the first record, press Enter. d. Press Tab to move cursor so that it is under First Name. e. Notice that the ID automatically gets incremented. f. Finish entering all of the records. Save the Table 1. Click the Save button on the Quick Access Toolbar or go to the Office button then click Save. Access 2007 - Basics Page 7 Figure 3 - Office Button / Save As Option 2. In the Save As dialog box, type tblDemo in the Table Name field (see Figure 4). Figure 4 - Save As Dialog Box 3. Click on the OK button. NAMING CONVENTIONS Database development includes naming objects consistently. You do this by using the assigned naming conventions for each object within Access 2007. Naming conventions are not mandatory, but they do make your work consistent and organized. If you plan on sharing a database with others, it’s recommended to use them. The Leszynski Naming Convention The naming convention used in this document is called the Leszynski Naming Convention (LNC). This naming convention suggests that you precede object names with three letters or “tags”. This enables you to open, edit and troubleshoot without having to decode what is contained within each object. For example, you created a table called My Table. The LNC way to save the table is to start the file name with the tag tbl (see Figure 1, below). Please note, file names can be more than one word but underscores and spaces should not be utilized. Each word within the file name Access 2007 - Basics Page 8 should begin with a capital letter. Since databases are often shared with others, this format allows other users to easily identify the file. It saves you time when you begin to use some of the more advanced features that Access has to offer. Figure 1 - tblMyTable Object Naming Conventions The same format applies to the other objects in Access. The only thing that changes is the naming conventions for the object you are working with. Figure 2 contains a listing of the different naming conventions. Figure 2 - Leszynski Naming Convention Tags Access Table Exercise When creating your first table, the ID field will be created automatically as a unique identifying field, or primary key (see Figure 3). It uniquely identifies each record stored in the table. Figure 3 - Donation Table Access 2007 - Basics Page 9 TABLE USING DESIGN VIEW Create a New Table 1. Click the Create tab. 2. Click Table Design. 3. In the New Table dialog box, click Design View in the View group. 4. Click the OK button. Entering Field Name, Data Type and Description Refer to Figure 1. 1. 2. 3. 4. 5. 6. 7. 8. Click the first cell in the Field Name column. Enter the desired field name from Example A on page 4 of this guide. Press the Tab or Enter key. The insertion pointer is now in Data Type. Click the down arrow button to display the list of Data Types. Click the desired data type or press Tab to choose the default Text type. Press the Tab or Enter key. The insertion pointer is now in the Description column. Enter the description, if desired. Press Tab (or Enter) and repeat the process of entering field name from Example A, page 4 of this guide. Figure 1 - Table Properties Access 2007 - Basics Page 10 Saving a Table 1. 2. 3. 4. Click the Save button in the Quick Access Toolbar. In the Save As dialog box. Type the name tblDesign. Click on the OK button. In the Microsoft Office Access dialog box stating “there is no primary key defined,” click Yes to create a primary key field called ID (see Figure 2, below). Figure 2 - No Primary Key Defined Message 5. Close or minimize the Design View window. CREATE A QUERY A query permits you to select records from your database tables that meet certain conditions or criteria. For example, you may want to produce a report that contains only those records that have a zip code greater than 90000, or you may want to select those records where the state is California and the age of the individual is more than 50. These tests or criteria are specified and applied to the table by creating a query. In effect, the query creates a subset of the table containing only those records and fields specified in the query. Create a New Query 1. Click the Create tab. 2. In the Other group, click Query Design. The Show Table window appears (see Figure 1, below). 3. On the Tables tab, click tblDemo. 4. Click the Add button. 5. Click the Close button. Figure 1 - Show Table Window Access 2007 - Basics Page 11 Enter Query’s First Criterion 1. 2. 3. 4. 5. 6. Click in the first Field: cell. Click the down arrow button to display the list of field names (see Figure 2). Click the Donation field name. Click in the Criteria: row, first cell. Type <1 (indicating less than 1). See Figure 3 for query criteria. Figure 2 - Field List Figure 3 - First Query Criteria Enter Query’s Second Criterion 1. Click in the second Field: cell. 2. Click the down arrow button. 3. Click the Last Name field (see Figure 4, below). Figure 4 - Second Query Criteria Enter Query’s Third Criterion 1. Click in the third Field: cell. 2. Click the down arrow button to display the field names menu. 3. Click First Name (see Figure 5). Figure 5 - Third Query Criteria Access 2007 - Basics Page 12 Figure 6 - Query Window Run and Save the Query 1. From the Design tab, in the Results group, click Run (see Figure 7). Figure 7 - Design Tab / Run 2. 3. 4. 5. 6. Click the Save button on the toolbar. In the Save As dialog box, enter qryDemo for the name of the query. Click OK. The query results display (see Figure 8). Close or minimize the query. Figure 8 - Query Results Access 2007 - Basics Page 13 CREATE A FORM A form is an easy and efficient way to enter data into a table. Input forms are especially useful if the person entering the data is not familiar with Access 2007 and needs to have a guide in order to input data accurately into the appropriate fields. Access provides several predefined forms and provided wizards that walk you through the process of creating a form. You can also use the design tool to create your customized forms. Creating a Form 1. Click the Create tab. 2. In the Forms group, click More Forms. 3. Click Form Wizard. The Form Wizard window displays (see Figure 1, below). Figure 1 - The Form Wizard 4. 5. 6. 7. 8. Select Table: tblDemo from the Tables/Queries drop down menu. Click (>>) right double chevrons to add all available fields to the form. Click Next. Select Columnar. Click Next (see Figure 2, below). Figure 2 - Form Wizard / Columnar Option Access 2007 - Basics Page 14 9. Select the desired style. 10. Click Next. 11. Type frmDemo in the text box to add a title to the form (see Figure 3). Figure 3 - Form Title 12. Click Finish (see Figure 4 for results). Figure 4 - frmDemo Results CREATE A REPORT You have created the database, set-up a table, created a form, entered data into the table, and selected records from the table through a query. Now you can create a report to display the data as useful information. Access 2007 provides several predefined reports and has a Report Wizard. The Report Wizard will be used in the example below. You can also create customized reports by using Access 2007 design tools. Producing a Report 1. From the Create tab, in the Reports group, click Report Wizard. The Report Wizard opens. Access 2007 - Basics Page 15 2. Under Tables/Queries, click the down arrow. 3. Select Table: tblDemo. 4. Click the right double chevrons (>>) button to include all fields in the report. 5. Click Next. 6. Click Next to accept the default grouping. 7. Click Next to accept the default order. 8. Select Tabular Layout and accept Portrait orientation; click Next. 9. Select a report style and click Next. 10. In the box below “What title do you want for your report?” type rptDemo. 11. Click Finish to view the report (see Figure 1). 12. Close the report window. Figure 1 - Generated Report OTHER USEFUL FUNCTIONS Insert a Column or Field in Datasheet View 1. Click anywhere in the column to the right of where the new column is to be inserted. 2. Click on the Datasheet tab. 3. Click Insert. Delete a Column or Field in Datasheet View 1. Click anywhere in the column to be deleted. 2. Click the Datasheet tab. 3. Click Delete. Move a Column in Datasheet View 1. Click once on the field name of the column to be moved until it is highlighted. 2. Click again on the same highlighted field and drag the column to its location. Access 2007 - Basics Page 16 Changing Column Width in Datasheet View 1. 2. 3. 4. Click anywhere in the column to be changed. Right click on the field name. Click Column Width. In the Column Width dialog box, enter the desired width in the Column Width text box. 5. Click OK. Changing Row Height in Datasheet View 1. 2. 3. 4. Click to the left of the first field in the row to be changed. Click Row Height from menu. In the Row Height dialog box, enter the desired height in the Row Height text box. Click OK. Hiding a Column in Datasheet View 1. Right click the Field name of the column to be hidden. 2. From the menu, click Hide Columns. Unhiding a Column in Datasheet View 1. 2. 3. 4. Right click any Field name. Select Unhide Columns from menu. Place a check in the check box of the hidden columns window. Click the Close button to close the Unhide Columns window. Removing Gridlines in Datasheet View 1. From the Home tab, in the Font group, click the Gridlines command. 2. Click None to remove the gridlines. Selecting a Font in Datasheet View 1. Select the text to be changed. 2. From the Home tab, in the Font group, click the down arrow of the Font box to display font names. Delete a Record in Datasheet View 1. Right click to the left of the record or row to be deleted. 2. Click Delete Record. 3. In the Microsoft Access dialog box stating “You are about to delete 1 record(s)” click Yes to delete the record. Access 2007 - Basics Page 17 Changing the Contents of a Cell 1. Click in the cell to be edited. 2. Make the appropriate insertions or deletions. 3. Press the Enter or the Tab key to save the change. Sorting the Data in a Datasheet 1. Click anywhere in the column used to sort the datasheet. 2. Click the Ascending or Descending button from the Sort & Filter group. Note: to restore it to the original format, click the Clear All Sorts button. Printing the Datasheet 1. Click the Office button. 2. Select Print from the menu. 3. In the Print dialog box, click OK. CLOSING AND EXITING ACCESS Closing a Database 1. Click on the Office button in the upper left-hand corner of the window. 2. Select Close Database from the menu (see Figure 1, next page). Exiting Access 1. Click on the Office button. 2. Click on the Exit Access button on the lower right-hand corner of the window (see Figure 1, next page). Figure 1 - Close Database and Exit Access Commands Access 2007 - Basics Page 18 CLASS EXERCISES Create a Table 1. Create a table in Access that looks like the table below (Create tab > Table [in Tables group]). Figure 1 - Table Note: the ID field can be any unique number. When you’re entering data, the ID field automatically increments. a. Save the table as tblStudents. Note: To change the ID, go to Design View. In the Data Type field, change AutoNumber to Number, then Save (Go to View > Datasheet View > in ID column, manually type in 1, 2, etc., Save). 2. Create a query that displays the first name, last name and city of students from California (page 13 of this guide discusses queries) [Create > Query Design]. a. Save the query as qryCAStudents. 3. Create a form using the Form Wizard. Refer to page 16 if needed. [Create > More Forms in Forms group > Form Wizard). a. b. c. d. Use tblStudents to create the form. Include all the fields. Use the Columnar layout. Save (title) the form as frmStudents. 4. Create a report using the Report Wizard. Refer to page 17 if needed. [Create > Report Wizard in Reports group]. Access 2007 - Basics Page 19 a. Use tblStudents to create the report. b. Include the following fields: • First Name • Last Name • ID Number • Class Standing c. Sort the records by Last Name. d. Save the report (title) as rptStudentSummary. 5. You should have the following tables created in the Shutter bar when you’re done with the exercises. Figure 2 - Tables Created Access 2007 - Basics Page 20 HOW TO LEARN MORE Microsoft on the Web Microsoft on the Web (WWW.MICROSOFT.COM ) provides links to Web locations where you can learn more about Microsoft Office 2007. It is a great resource for learning with many free self paced online tutorials. You need Internet connectivity and a web browser to use of this feature. For Beginning Access Users Online training tutorial, quick reference cards and how-to’s: GETTING STARTED WITH ACCESS 2007 http://office.microsoft.com/en-us/access/HA100646161033.aspx GUIDE TO ACCESS 2007 USER INTERFACE http://office.microsoft.com/en-us/access/HA100398921033.aspx GET UP TO SPEED WITH ACCESS 2007 http://office.microsoft.com/training/training.aspx?AssetID=RC101933201033 QUICK REFERENCE CARD http://office.microsoft.com/training/training.aspx?AssetID=RP101933511033&CTT=6&Origin=R C101933201033 For Access Users New to 2007 Online training tutorials, quick reference cards and how-to’s: WHAT’S NEW IN MICROSOFT ACCESS 2007 http://office.microsoft.com/en-us/help/HA100241851033.aspx GUIDE TO THE ACCESS 2007 TEMPLATES http://office.microsoft.com/en-us/access/HA101175941033.aspx WHICH FILE FORMAT SHOULD I USE IN ACCESS 2007? http://office.microsoft.com/en-us/access/HA100908451033.aspx CONVERT A DATABASE TO THE ACCESS 2007 FILE FORMAT http://office.microsoft.com/en-us/access/HA100484061033.aspx Access 2007 - Basics Page 21 Import Data from Excel to Access Online training tutorial: http://office.microsoft.com/training/training.aspx?AssetID=RC102722321033 Printed Material There are many books available to help you learn to use Microsoft Access 2007. Here are just a few: • • Microsoft Office Access 2007 Bible by Grohn, Stockman, Powell, Prague, Irwin and Reardon (includes a comprehensive reference CD). Microsoft Office 2007 Bible by Walkenbach, Tyson, Wempen, Prague, Groh, Aitken, and Bucki. TRAINING AND SUPPORT IT Training IT Training & Development offers training in many different applications at various skill levels. See what is coming up over the next few months by checking our website at: www.csun.edu/it/training Contact Us: IT Training & Development Phone: (818) 677-1700 or x1700 (on campus) Email: training@csun.edu Troubleshooting and Support If you experience problems getting started with Office 2007 contact the Help Desk at x1400 or helpdesk@csun.edu. Access 2007 - Basics Page 22 NOTES Access 2007 - Basics Page 23 IT’s technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please cite source when using this material. V4 11/02/09 Access 2007 Basics