Hide Notes 

Slide 1: MS AccessTraining www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 2: Section One: Database Basics & Overview SECTION GOALS UNDERSTAND WHAT ACCESS IS AND WHAT ITS USE IS. GET FAMILIAR WITH OUTLOOK 2007 INTERFACE INVESTIGATE ‘MINI TOOLBAR’ QUICKLY LEARN HOW TO CREATE AND USE ACCESS. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 3: Why Use Access?  Microsoft Access is a tool to build, create, and manage databases. What is a database? A database can be thought of as a logical collection of interrelated information, managed and stored as a cohesive unit. A simple example of a database is a table or a spreadsheet (which is a larger table). In short, it is information that is usually organized in rows and columns. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 4: Access is good at handling large versions of this type of information. An example of the kind of information Access can handle would be storage of customer information for a business. Column #1 Column #2 Information Information Example of a simple database structure www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 5: With this example in mind, Access can help make the following easier:  Data Entry – Using Access as a “backbone” or data      foundation, you can create forms to make entry of data/information easier. Error Checking – Access can check for duplicate entries and other errors Reports and Queries – Access allows easy creation of reports to print and view. Imports Data – Access can import data easily from other sources, such as Excel. Multiple Table Management – Access organizes various tables within the database that share common information. Many other functions… www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 6: Opening Access for the first time As with anything Microsoft-related, there are many ways to execute a command. Here are some common ways to open MS Access on your computer machine:  OPTION 1 – Open All Programs 1. Click the START BUTTON 2. Choose All Programs > Microsoft Office > Microsoft Office Access 2007  OPTION 2 – Start Menu ‘Quick Line’ (use if you’ve opened Access recently) 1. 2. Click the START BUTTON Choose Microsoft Office Access 2007 Locate an existing Access file Double click the file’s icon. MS Access will automatically open. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe  OPTION 3 – Existing Database 1. 2.
Slide 7: When you start Access 2007, you see the new Getting Started page. Here you'll find that this version of Access has already done a lot of your work for you. Categorized templates let you start your job in the middle. Templates are pre-built, taskfocused databases that you can download and use right away. The Getting Started page offers database templates for you to download. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 8: When you create or open an Access 2007 database, here's what you see. These are your new friends, the keys to success with Access 2007. Take a minute and learn their names: -The Ribbon -The Quick Access Toolbar -The Microsoft Office Button - The Navigation Pane Identifying Access Interface www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 9: The Navigation Pane shows you just what's in the database you're using. Tables, forms, reports, queries. All of this is similar to the ‘new’ look of all MS Office 2007 products. Access 2007 makes it really easy to create a new database. Templates provide pre-built databases designed to suit your needs. Select a category on the Getting Started page, and you'll see a range of possibilities. Navigation Pane www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 10: Create a table from a template  Suppose you want to add a table to your database. Access 2007 makes that easy. Clicking the Create tab shows you groups of commands including the Tables group. Access 2007 has templates for tables, too. Click Table Templates, and there they are. Click a table template, and in a moment your database contains another table, with fields and data types appropriate to its purpose. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 11: To create a new form in Access 2007, click the Create tab and then click Form. Simple. Access bases this new form on the existing database object that you already have open, or that you have selected in the Navigation Pane. The new form opens in Layout view, and the Ribbon offers you Form Layout Tools with two tabs, Format and Arrange. The commands on those tabs make changing the appearance of controls and labels so easy. Best of all, you preview the changes as you make them. If you don't like a change, undo it: CTRL+Z or the Quick Access Toolbar button . Create a form, format a form www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 12: To create a new report in Access 2007, click the Create tab and then click Report. Access bases the new report on the existing object that you already have open, or that you have selected in the Navigation Pane. Create a report, format a report www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 13: Create Report (cont’d)  The new report opens in Layout view, and the Ribbon offers you Report Layout Tools with three tabs, Format, Arrange, and Page Setup. (These tabs appear on the Ribbon only when they can be used.) Use the commands on those tabs to change the appearance of controls and labels, or to select margins, paper size, and other printing options. The innovative 2007 version allows you to preview the changes as you make them. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 14: If you'd like to make a copy of an older database that will be more secure, more compact, and easier to recover if necessary, here's all you have to do. In Access 2007, click the Microsoft Office Button and use the Open command to open that older database. Click the button again and point to Save As. Click Access 2007 Database. Microsoft has made attempts to make this process obvious and easy. Upgrade previous/older databases www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 15: Share in an older format  You may need to share an Access 2007 database with someone who is working with a previous version of Access. As you might expect, the way to do that is to create a copy in an older file format. And the method of making that copy is very like the method of upgrading an older database. Open the Access 2007 database and click the Microsoft Office Button . Point to Save As and then click the option that applies. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 16: Oops! – Can’t do that!  Some new features in Access 2007 require the new file format. If your database uses those new features, and you try to make a copy of the database in an older format, Access tells you why that doesn't happen. If you know that you'll be sharing a database with people who don't have Access 2007, plan your work accordingly. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 17: Section Two: Creating Database in Access SECTION GOALS FIND THE BEST DATABASE TEMPLATE FOR WHAT YOU ARE TRYING TO DO. USE A DATABASE TEMPLATE TO CREATE A NEW ACCESS DATABASE QUICKLY AND EASILY. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 18: Introduction  With Access 2007, all you need to know about databases is that you can use a template to create and design a comprehensive database. Database templates include all that you need to get started and allow you to concentrate on entering data — immediately. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 19: Create a database with a template  Access 2007 has made it easier for you to create databases. In the past, before you could start entering data, you had to design a database and create tables to store the data. Then, to enter data easily, you would need to create a form. And then, to use the data that you entered, you would need to design reports. These tasks could be daunting and time consuming.  Now that Access has database templates, all you need to do is choose a template that works for you. A template already has the tables, forms, and reports that you need to get started. All you need to do is enter the data. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 20: Access 2007 includes lots of free database templates. You can organize your contacts, manage issues, log what you eat, keep track of books in your library, and record assets, to name a few. The database template that you choose depends on what you want to do. Template Reminder www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 21: Where are the database templates?  You can find database templates from the Getting Started with Microsoft Access window in Access 2007. As soon as you open Access, you see this window.  Templates are categorized under Local Templates and From Microsoft Office Online. Local templates are included with Access 2007 and are stored on your computer. Online templates are located at the Microsoft Office Online Web site, from where you can download them. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 22: Online templates are divided into categories such as Business, Education, and Personal. Because you are looking for a business-related template, you'd click the Business category. The Getting Started with Microsoft Access window. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 23: No guessing  The Business category has many different database templates to choose from, and Access makes it easy for you to find the right template. First, Access 2007 allows you to preview each template: as you move your pointer over the picture, a template description and picture appears.  For example, when you rest your pointer across the Contacts picture, you can read the database template description. The description looks like it might work for the stationery store. When you’re ready to continue, click the Contacts picture to select it. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 24: More info on template info… Point over the template picture to see a description. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 25: Select a database template  After you've selected the Contacts database template, look to the right side of the screen to see the task pane. As shown in the picture, this pane lists the template's category, the template's name, and the template's description. It also displays a suggested file name and location for the new database that you’ll create with the database template.  The suggested file name, Contact Management database, is not very descriptive. You might want to change the name to better reflect the database’s purpose. For example, you may end up with more than one contact database. A more descriptive name would be Stationery Customer Contacts. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 26: Select a database template (cont’d)  The location is equally important — you may have a specific area where you want to store the information, such as putting it in a shared directory that all employees could access. If you click the folder to the right of the file location, you can browse to the location where you want to store the database.  Once you are happy with the location and file name, click the Download button.  Note If you're using a local template, this button would say "Create" instead of "Download." www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 27: Information about the database template appears when you select the template. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 28: Download a database template  After you click Download, a few things need to happen before you can use the template.  First, the Microsoft Office Genuine Advantage dialog box pops up on your screen. Access 2007 database templates can be downloaded only to computers running genuine Microsoft Office software. After the validation is complete, the database template is downloaded to your computer. This is when the template becomes your database. Software validation occurs every time you download a template from Microsoft Office Online. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 29: The Microsoft Office Genuine Advantage dialog box. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 30: You may see a small Access Help window on top of the database that was just created for you. This help article describes the function of the database template and provides some database instruction. When you are working on a new project, you may want to have this help available. As shown in the picture, you can click on the title bar and drag the window to another location, or you can close the window by clicking the Close button in the window's right corner. The Access Help window on top of a template. © 2008 aNm – Michael Sheyahshe www.alterNativeMedia.biz
Slide 31: In your new database, you’ll most likely see the security shield and a security warning in the Message Bar, the area between the Ribbon and the database. (If you've saved your database to a trusted location, you will not see the security alert.) Access disables content that could be dangerous to your computer, such as macros. Most Access database templates contain at least one macro. Click Options in the Message Bar and then click Enable this content each time you open the template. Or, you can set up a trusted location. The Microsoft Office Security Options dialog box. © 2008 aNm – Michael Sheyahshe www.alterNativeMedia.biz
Slide 32: Adding Data  Now that you’ve enabled the content, you can concentrate on what you see. Your new Contacts database opens to a form so that you can start adding your contacts. You don’t need to do anything else. Access has created all the fields and even given them names.  You can click anywhere in the row that has New as its ID to start adding data. Then start by adding a first and last name and other contact information in the fields provided. As soon as you begin typing, Access creates an ID number and then adds another new row with "New" as its ID. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 33: A database has lots of different parts. You don't have to know what they are, but you can start exploring these different parts with the navigation pane. When you click the Shutter Bar Open/Close button, this opens the navigation pane. The pane lists all of the different database parts. You can view each part by clicking on the different database parts (in Access speak, these parts are called objects). More Thoughts - Click the Shutter Bar Open/Close to open the navigation pane. © 2008 aNm – Michael Sheyahshe www.alterNativeMedia.biz
Slide 34: Section Review 1. What’s the most efficient way of determining which database template is right for what you want to do? 2. What happens if you don’t name a database when you first create it from a template? 3. To save the data in your database file, you must click Save on the Ribbon. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 35: Questions?  Familiar with Templates? www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 36: Section Three: Editing Data SECTION GOALS CHANGE A COLUMN NAME IN A DATABASE. ADD A COLUMN TO A DATABASE TABLE. ADD NEW COLUMN TO A FORM AND CHANGE ITS TITLE. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 37: Overview  You've just created a new database using a template, but you've decided that the database needs a few modifications. What do you do? It's easy — you can make modifications to the database that you created from a template. This course walks you through a few common modifications. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 38: Back up your data  Access makes it easy for you to modify a database. But, what if you     make a change and then decide it's not what you want? Although you can use the Undo command to reverse some changes, certain actions cannot be reversed. That's why it's a good idea to back up the database. Click the Microsoft Office Button Point to Manage to open the Manage this database submenu. From there, click Back Up Database. When you back up a database, Access saves the database and then saves a copy of the database file. For the copy, Access adds the date to the existing database name, but you can change it to any name. Likewise, Access stores the backup in the same location where the original database is stored, but you can change it to any location. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 39: After you back up the database, you can start modifying it. The Manage this database submenu. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 40: Let Access do some of the work  Your Access database is made up of tables, forms, reports and other parts that rely on each other. If you make a change to a field name in a table, the other database parts that call on that field might not work as they did. Access provides Name AutoCorrect options that help keep your database parts in sync. Most of the time these options are already checked, but it's a good idea to confirm it. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 41: To check that the Name AutoCorrect Options are on: Click the Microsoft Office Button. Then click Access Options. In the options for Current Database, scroll down to the Name AutoCorrect Options and make sure that there are checkmarks next to Track name AutoCorrect info and Perform name AutoCorrect. AutoCorrect Options www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 42: Rename a field  You want to rename a field name from "Mobile Phone" to "Wireless      Phone". Changing a field name is a common modification you can make to any database table. But right now, you have the "Contact List" form open. Before you rename your field name: You must open the Contacts table by using the Navigation Pane. Then you must close all the other forms and reports that may be open. Finally, you select the field name and type in the new name. Note If you haven't closed all the other forms, you won't be able to change the field name. You'll receive an error that says that you can't modify the structure of the table because it is in use by another person or process. So, make sure those forms are closed. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 43: Once you've made the change, you know that Access has made changes to the reports and forms that use this field because you know that the Name AutoCorrect Options are on. Now we're ready to add an entire new field to a table. Renaming the Mobile Phone field. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 44: Add a field to a table  Now, you're ready to add a new field for Mr., Ms., Mrs.,      and other honorifics. You'll begin at the Contacts table, which is already open. As shown in the picture, On the Home tab, click View, and then click Datasheet View. Tell Access where you want the new field by clicking a field heading to select it. Access will insert the new field to the left of the field you've selected. Click the Datasheet tab. Click Insert, then type the field name by doubleclicking the field name and typing “Title”. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 45: Because you changed an underlying structure of a table, you need to save this change. Click the Microsoft Office Button and then click Save. Now you're ready to add that same field to a form. Note You can also delete fields from a table. The Delete command is right below the Insert command. Deleting a field has far more implications than adding a field. See the Quick Reference Card for details. Adding a field to a table. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 46: Add a field to a form  Once you have created a new field, you add the new field to a form. An easy way to do this is in Layout View. This view is only available if you have a form or a report open.  In Layout view, you can drag a field from the Field List pane to your form. You can also move the new field to where you want it by clicking and dragging the field name. You'll need to save your changes again. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 47: Because you are already in Layout view for the form, you can easily make one last change. Because this database contains customer names for a stationery store, change the form title to Customer List. You can click the Format tab and then click Title in the Controls group. This will highlight the current title, as shown in the picture. All you need to do then is type the new title of the form. In this case, you type "Customer List." Remember to save after making this change. Change the form title www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 48: Section review  Reasons to back up a database are:  Layout view is:  Use a Field List pane to: www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 49: Questions?  Familiar with the different sections of Access? www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 50: Section Four: Entering Data SECTION GOALS CREATE A NEW, BLANK DATABASE WITH A NEW TABLE. ENTER DATA IN THE NEW TABLE. SET OR CHANGE FIELD (COLUMN) NAMES. SET AND CHANGE DATA TYPES, SETTINGS THAT HELP YOU ENTER THE CORRECT TYPE OF DATA IN EACH COLUMN. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 51: Make Access do your will…  The new database opens and displays a single table that contains one row and two columns, or fields. In Access, tables are a lot like the worksheets in Excel — a grid of cells that store your data. Tables are the backbone of any database, and all databases contain at least one table and usually more.  The next figure shows the process of creating a new database and the table in that database. Remember that in a database, columns are called fields. Keep going to learn how to enter data in a table. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 52: After you start Access, click Blank Database. Accept the default name, or enter your own, and then click Create. Access opens the new database and displays the new table. Datasheet view + data = a new table. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 53: Easy data entry  If you've entered data in Excel, you'll have no problem entering data in Access. Just select the first blank cell and start typing. When you finish, press the RIGHT ARROW key. When you do that, Access writes a name in the field header above the cell and automatically creates a new, blank field.  What's more, you can copy data from sources such as Excel worksheets, other Access tables, and Word tables, and paste it into a new Access table. When you do that, you can paste data or data and column headers. Just select and copy your data, click the first blank cell in the new table, and click Paste. A small dialog box asks you to confirm the operation, so click Yes, and you're done! www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 54: Two basic ways to enter data: Enter data in the first blank cell and press RIGHT ARROW … … then enter data in the next blank cell. Or … … copy data from another source — Excel or tables in Access, Microsoft Office Word, or a Microsoft Office SharePoint Server site — click that first blank cell, and paste. Adding Data www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 55: While you're entering data, Access names your fields: Field1, Field2, and so on. You use those names, or you change them. Naming your fields is easy. Just double-click a field header and type away, as in the figure above. Your names can be up to 64 characters long, and entering or changing a name doesn't affect the rest of your data. If the new column is too narrow to display the entire field name, just click the column header, rest your mouse pointer on the right side of the column until the pointer changes to a double arrow, and then click and drag the edge of the column to the right. Renaming Fields www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 56: Data types  While you enter data in a new table — either manually or by pasting     it in — Access sets a data type for each new column. A data type is a setting that controls the kind of data you can enter in each column of a table. For example, if you want to store dates and times in one column and cost figures in another, you set the first column to the Date/Time data type, and you set the second column to the Currency data type. The figure above illustrates this idea. All databases use data types, because they help keep your data accurate. For example, you can't enter names in a field set to contain dates or accidentally include a phone number in your sales figures. Data types also help you manage the size of your database by controlling the size of each field. For example, in a field set to contain text data, you can enter up to 255 characters in a cell, and no more. Now, here's the good news: When you follow the steps we've discussed so far, Access sets data types for you. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 57: A field set to the Text data type. A field set to the Date/ Time data type. A field set to the Currency data type. Better accuracy and storage. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 58: Data types, automatically  When you enter data in the first cell of a new field, Access looks at the kind of data you enter and infers a data type for the field, based on what you enter. For example, if you enter a currency value, Access infers the Currency data type for that field. If you enter a date, Access infers the Date/Time data type. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 59: But not all the data types  You can't set all the available data types by entering data into a cell. For example, if you want to display photos of your employees in a column, you can't paste a photograph into a new field. You have to set the Attachment data type manually.  The following figure shows the data types you can set by entering data and the data types you must set manually. To set a data type, you click the Datasheet tab, and in the Data Type & Formatting group, you select an option from the Data Type list. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 60: You can set these data types by entering data in a new table. You set these data types manually. Access can't infer everything. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 61: You can change data types as needed  At times, you may also need to change a field's data type manually. For example, you paste data into a table, and Access infers the Number data type for a column of financial data. In that case, you follow the procedure we described earlier. Click the Datasheet tab, and in the Data Type & Formatting group, select an option from the Data Type list.  NOTE: be careful when you change a data type, because Access may delete data when you change a type. You can't recover that data unless you have a backup copy of your database. Also, other components in your database may stop working because they depend on a specific data type. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 62: When you change a data type, pay attention to any warning messages, and stop if you aren't sure of what you're doing. You can change data types manually. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 63: Section Review 1. As you enter data, Access sets data types because: To change the text in a column header (a field name), you: You need to be careful when changing data types because: To change a data type, you need to _____ www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe 2. 3. 4.
Slide 64: Section Five: Sorting & Filtering Data, More Forms, & Mail Merge SECTION GOALS SHOW THE TOTALS ROW IN A DATASHEET SUM, COUNT, AND AVERAGE THE DATA IN A COLUMN SORT AND FILTER THE DATA IN ONE OR MORE COLUMNS USE SEARCH TO FIND DATA IN A TABLE UNDERSTAND MORE WAYS TO CREATE FORMS. KNOW HOW TO USE ACCESS FOR MAIL MERGE. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 65: Let's say your boss needs to know how much the company has spent on computers during the past four years. The Totals row makes it easy to find that kind of information in your Access database. In the Navigation pane, double-click your table. That opens it in Datasheet view automatically. On the Home tab, in the Records group, click Totals. That shows the Totals row at the bottom of your datasheet. In the Totals row, select the field where you want to perform the calculation, then select a function from the list. For this example, you want to add the numbers in a column, so you select the Sum function, and you're done! Math, sorts, and searches www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 66: Now that you've calculated what the company spends on computers, your boss says she needs to see which machines come from each supplier. That's another easy one: All you have to do is sort the data by the names of your suppliers. In the column you want to sort, click the arrow at the right side of the column header, and then click Sort A to Z. This sorts your column in alphabetical order. An arrow appears in the sorted column. If you sort from A to Z, the arrow points upward. If you sort from Z to A, it points downward. You can always undo a sort. Just go to the Home tab, and in the Sort & Filter group, click Remove all sorts. Sorting www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 67: Using Filters Next, your boss wants to see how much the company spends on each supplier. You can do that by filtering data, another quick and easy process. 1. In the column you want to filter, click the arrow at the right side of the column header, and then clear the Select All check box. 2. Select the items that you want to see in your datasheet. You can select as many as you need. Access displays the data that matches your selections and hides the rest. Don't worry, you aren't removing any data, just hiding it. 3. After you click OK, an icon tells you the column is filtered. If you point at the icon, Access shows your filter criteria, and … 4. … as you filter data, the calculations in the Totals row stay in synch with your changes. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 68: To remove the filter, open the Filter/Sort menu again and click the Clear filter from command. That puts your datasheet back in its original state. Filter with a few simple steps. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 69: Search for specific records  Sometimes, you need to find data quickly, but you aren't sure where it is in your datasheet. That makes it hard to sort or filter effectively, so instead you can use the Search box, located at the bottom of the Access screen. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 70: Enter part or all of a word, phrase, date, or number in the Search box and press ENTER. Access highlights the corresponding characters in the first record that matches your search. To find the next matching record, press ENTER again. Access finds all records that match your search data, anywhere in your datasheet. Keep in mind that Access returns only exact matches, so you have to enter data that is actually contained in your table. Otherwise, there are no results. Search returns records that match your input. © 2008 aNm – Michael Sheyahshe www.alterNativeMedia.biz
Slide 71: Create forms  A form is a database object that you can use to enter, edit, or display data from a table or a query. You can use forms to control access to data, such as which fields or rows of data are displayed. For example, certain users might need to see only several fields in a table with many fields. Providing those users with a form that contains just those fields makes it easier for them to use the database. You can also add buttons and other functionality to a form to automate frequently performed actions.  Think of forms as windows through which people see and reach your database. An effective form speeds the use of your database, because people don't have to search for what they need. A visually attractive form makes working with the database more pleasant and more efficient, and it can also help prevent incorrect data from being entered. Microsoft Office Access 2007 gives you new tools to help you create forms quickly, and provides new form types and features that improve the usability of your database. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 72: Create a form by using the Form tool You can use the Form tool to create a form with a single mouse-click. When you use this tool, all the fields from the underlying data source are placed on the form. You can begin using the new form immediately, or you can modify it in Layout view or Design view to better suit your needs. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 73: In the Navigation Pane, click the table or query that contains the data you want to see on your form. On the Create tab, in the Forms group, click Form. Use the Form tool to create a new form www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 74: New Form Tool (cont’d)  Access creates the form and displays it in Layout view. In Layout view, you can make design changes to the form while it is displaying data. For example, you can adjust the size of the text boxes to fit the data, if necessary.  If Access finds a single table that has a one-to-many relationship with the table or query that you used to create the form, Access adds a datasheet to the form that is based on the related table or query. For example, if you create a simple form that is based on the Employees table, and there is a one-to-many relationship defined between the Employees table and Orders table, the datasheet displays all the records in the Orders table that pertain to the current Employee record.  You can delete the datasheet from the form if you decide you do not need it. If there is more than one table with a one-to-many relationship to the table that you used to create the form, Access does not add any datasheets to the form. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 75: Create a form that displays multiple records by using the Multiple Items tool When you create a form by using the Simple Form tool, the form that Access creates displays a single record at a time. If you want a form that displays multiple records but is more customizable than a datasheet, you can use the Multiple Items tool. 1. In the Navigation Pane, click the table or query that contains the data you want to see on your form. 2. On the Create tab, in the Forms group, click Multiple Items. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 76: Create a form that displays multiple records by using the Multiple Items tool (cont’d)  Access creates the form and displays it in Layout view. In Layout view, you can make design changes to the form while it is displaying data. For example, you can adjust the size of the text boxes to fit the data.  When you use the Multiple Items tool, the form that Access creates resembles a datasheet. The data is arranged in rows and columns, and you see more than one record at a time. However, a Multiple Items form gives you more customization options than a datasheet, such as the ability to add graphical elements, buttons, and other controls. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 77: Create a form by using the Form Wizard  To be more selective about what fields appear on your form, you can use the Form Wizard instead of the various form-building tools previously mentioned. You can also define how the data is grouped and sorted, and you can use fields from more than one table or query, provided that you specified the relationships between the tables and queries beforehand. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 78: Create a form by using the Form Wizard (cont’d) On the Create tab, in the Forms group, click More Forms, and then click Form Wizard. 2. Follow the directions on the pages of the Form Wizard. NOTE If you want to include fields from multiple tables and queries on your form, do not click Next or Finish after you select the fields from the first table or query on the first page of the Form Wizard. Instead, repeat the steps to select a table or query, and click any additional fields that you want to include on the form. Then click Next or Finish to continue. 3. On the last page of the wizard, click Finish. 1. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 79: Create a form by using the Blank Form tool 1. 2. 3. On the Create tab, in the Forms group, click Blank Form. Access opens a blank form in Layout view, and displays the Field List pane. In the Field List pane, click the plus sign (+) next to the table or tables that contain the fields that you want to see on the form. To add a field to the form, double-click it or drag it onto the form. To add several fields at once, hold down CTRL and click several fields, and then drag them onto the form at the same time. NOTE The order of the tables in the Field List pane can change, depending on which part of the form is currently selected. If you are not able to add a field to the form, try selecting a different part of the form and then try adding the field again. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 80: Create a form by using the Blank Form tool 1. Use the tools in the Controls group on the Format tab to add a logo, title, page numbers, or the date and time to the form. If you want to add a wider variety of controls to the form, switch to Design view by right-clicking the form and then clicking Design View . You can then use the tools in the Controls group on the Design tab. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 81: Understand Layout view and Design view  Layout view Layout view is the most intuitive view to use for form modification, and it can be used for nearly all the changes you would want to make to a form in Office Access 2007. In Layout view, the form is actually running, so you can see your data much as it will appear in Form view. However, you can also make changes to the form design in this view. Because you can see the data while you are modifying the form, this is a very useful view for setting the size of controls or performing almost any other task that affects the appearance and usability of the form.  Certain tasks cannot be performed in Layout view and require switching to Design view. In certain situations, Access displays a message telling you that you must switch to Design view to make a particular change. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 82: Understand Layout view and Design view  Design view Design view gives you a more detailed view of     the structure of your form. You can see the Header, Detail, and Footer sections for the form. The form is not actually running when it is shown in Design view, so you cannot see the underlying data while you are making design changes; however, there are certain tasks you can perform more easily in Design view than in Layout view. You can: Add a wider variety of controls to your form, such as labels, images, lines, and rectangles. Edit text box control sources in the text boxes themselves, without using the property sheet. Resize form sections, such as the Form Header or the Detail section. Change certain form properties that cannot be changed in Layout view (such as Default View or Allow Form View). www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 83: Fine-tune your form in Layout view After you create a form, you can easily fine-tune its design by working in Layout view. Using the actual form data as your guide, you can rearrange the controls and adjust their sizes. You can place new controls on the form and set the properties for the form and its controls. To switch to Layout view, right-click the form name in the Navigation Pane and then click Layout View . Access shows the form in Layout view. You can use the property sheet to modify the properties for the form and its controls and sections. To display the property sheet, press F4. You can use the Field List pane to add fields from the underlying table or query to your form design. To display the Field List pane, do one of the following:  On the Format tab, in the Controls group, click Add Existing Fields.  Press ALT+F8. You can then drag fields directly from the Field List pane onto your form.  To add a single field, double-click it or drag it from the Field List pane to the section on the form where you want it displayed.  To add several fields at once, hold down CTRL and click the fields that you want to add. Then drag the selected fields onto the form. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 84: Fine-tune your form in Design view  You can also fine-tune your form's design by working in Design view. You can add new controls and fields to the form by adding them to the design grid. The property sheet gives you access to a large number of properties that you can set to customize your form.  To switch to Design view, right-click the form name in the Navigation Pane and then click Design View .  Access shows the form in Design view.  You can use the property sheet to modify the properties for the form and its controls and sections. To display the property sheet, press F4. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 85: Fine-tune your form in Design view (cont’d) You can use the Field List pane to add fields from the underlying table or query to your form design. To display the Field List pane, do one of the following:  On the Format tab, in the Controls group, click Add Existing Fields.  Press ALT+F8. You can then drag fields directly from the Field List pane onto your form. To add a single field, double-click it or drag it from the Field List pane to the section on the form where you want it displayed. To add several fields at once, hold down CTRL and click the fields that you want to add. Then drag the selected fields onto the form. To add a single field, double-click it or drag it from the Field List pane to the section on the form where you want it displayed. To add several fields at once, hold down CTRL and click the fields that you want to add. Then drag the selected fields onto the form. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 86: Some controls are created automatically, such as the bound control that is created when you add a field from the Field List pane to your form. Many other controls can be created in Design view by using the tools in the Controls group on the Design tab. NOTE Many of the tools in the Controls group are accessible only while your form is open in Design view. To switch to Design view, right-click the form name in the Navigation Pane and then click Design View . Add controls to your form www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 87: Save your work  After you save your form design, you can run the form as often as you want. The design stays the same, but you see current data every time you view the form. If your needs change, you can modify the form design or create a new form that is based on the original. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 88: Save your form design Click the Microsoft Office Button and then click Save, or press CTRL+S. 2. Alternatively, click Save on the Quick Access Toolbar, or press CTRL+S. 3. If the form is untitled, type a name in the Form Name box, and then click OK. 1. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 89: Save your form design under a new name Click the Microsoft Office Button , and then click Save As. 2. In the Save As dialog box, type a name in the Save Form To box, select Form in the As list, and then click OK. 1. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 90: Use a table or query as a mail-merge data source  You can create a mail merge operation by using the Microsoft Word Mail Merge Wizard. This wizard is also available from Microsoft Office Access 2007, and lets you set up a mail merge process that uses a table or query in an Access database as the data source for form letters, e-mail messages, mailing labels, envelopes, or directories. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 91: Use a table or query as the data source 1. 2. 3. 4. 5. 6. 7. Open the source database, and in the Navigation Pane, select the table or query that you want use as the mail merge data source. On the External Data tab, in the Export group, click More , and then click Merge it with Microsoft Office Word . The Microsoft Word Mail Merge Wizard starts. Select whether you want to create the link in an existing document or in a new document, and then click OK. If you chose to link to an existing document, in the Select Microsoft Word Document dialog box, locate and select the file, and then click Open. Word starts. Depending on your choice, Word opens either the document you specified or a new document. In the Mail Merge pane, under Select document type, click Letters and then click Next: Starting document to continue to step 2. In step 2, click Next: Select recipients. In step 3, you create the link between the data source in Access and the Word document. Because you started the wizard from Access, this link is created automatically. Under Select recipients, note that Use an existing list is selected, and the name of your data source is displayed under Use an existing list. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 92: Click Edit recipient list if you want to customize the contents of the table or query. You can filter, sort, and validate the data. Click OK to continue. Click Next: Write your letter to continue. Follow the remaining instructions in the Mail Merge pane, and in step 5, click Next: Complete the merge. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 93: Other ways to use a table or query as the data source  You can specify a table or query as a data source in additional ways. For example, you can export the table or query from Access to an ODBC database, a Microsoft Office Excel 2007 file, a text file, or any other file format that is compatible with Word, and then link to the resulting file by using the Word Mail Merge Wizard. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 94: Other ways to use a table or query as the data source (cont’d) 1. 2. 3. 4. 5. 6. If you have not already exported the table or query , do so. In Access, in the Navigation Pane, select the table or query that you want to use, and on the External Data tab, in the Export group, click the format you want to export to, and then follow the instructions. In Word, if the Mail Merge pane is not displayed, on the Mailings tab, in the Start Mail Merge group, click the arrow under Start Mail Merge, and then click Step by Step Mail Merge Wizard. The Mail Merge pane appears. In step 3 of the Mail Merge pane, under Use an existing list, click Browse or Edit recipient list. In the Select Data Source dialog box, specify the data file that you created in Access, and then click Open. Follow the instructions in any dialog boxes that follow. In the Mail Merge Recipients dialog box, review and customize the contents of the file. You can filter, sort, and validate the contents before you continue. Click OK, and then click Next: Write your letter in the Mail Merge pane. For more instructions on customizing your mail merge, see Word Help. www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 95: Section Review 1. To show the Totals row, you: 2. To sort data, you: 3. Filters remove unwanted data from your datasheet. 4. To completely remove a filter, you: www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe
Slide 96: Questions?  Thanks! www.alterNativeMedia.biz © 2008 aNm – Michael Sheyahshe