Top of
the Page| Discovering Computers | Windows XP | Word 2002 | PowerPoint 2002 | Excel 2002 | Access 2002 | Site Map |
| Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 |
MS Access 2002: Creating a Database using Design and Datasheet View
A database is a storage system, similar to a filing cabinet, that allows users to keep their important information in one central location that is accessible by many people at the same time. A database management system (DBMS) gives users a way to manage their data on a computer by allowing them to create databases and new tables, forms, queries and reports within those databases. Primary Keys are also a feature of a DBMS and allow for controlling duplication of data. Access is a type of DBMS.
to get a better understanding of how Access uses tables to build a database. Tables are where the actual information is stored. The Customer Table is made up of many rows (records) and columns (fields). Sometimes, tables are created that have a primary key or unique identifier. Look at the Customer Number field in the Customer Table. All of the records in that field have different numbers. Because of this, the Customer Number field could be used as the primary key of the table. If you decided to make the Customer Number field the primary key, you would not be able to store a duplicate customer number in any of the other records in the table.
A user may also write macros to automate processes and build modules using the programming language Visual Basic for Applications (VBA). As you progress through this course, you will have the opportunity to use most of these features and gain a better understanding of the power within a DBMS.
Speech Recognition allows users to work with Access by using voice commands instead of typing on the keyboard.
Figure 1-4 in your book shows you what the Language Bar looks like. It also displays the Language Bar components in the tray area of the Status Bar. For the Office Speech Recognition software to work correctly, you must make sure that the software option is installed on your computer and that you have a microphone to use so that the computer will be able to interpret your verbal commands.
If we look closer at the options available from the Language Bar, we see that there are many tools at our disposal. The Voice Command option lets us verbally access toolbars, menus, menu commands, list items and many other features within the Access software; the Dictation option lets us verbally insert fields into a table. The Language Bar also displays the most recent voice command in a text box that is to the right of the Voice Command option.
There are tools available that let you customize the way the Speech Recognition Software works. These options can be found by clicking on the Tools option from the Language Bar. If your computer has speakers, you can setup the software to dictate to you. A nice feature available with this is the ability to set the voice type to female or male.
While you may not use the Speech Recognition feature often, it is a nice addition to this version of Access and should be helpful to many people as the use of computers and Access increases. For more information on Speech Recognition in Access, please visit the Microsoft Speech Recognition website. Please see the Microsoft Speech Site URL listed in the LINKS section of this manual.
Once your computer has started and Windows is running, you can use Access. To use the software, you will first have to start it. To do this, begin by clicking the Start Button on the Task Bar. A menu will appear with many different options, such as Programs, Documents, Settings, Search, Help and Run. Select the Programs option to view a submenu. The submenu will contain shortcuts to most of your installed programs. There should be an option for Microsoft Access. Click on the Microsoft Access option to start the program. Access should start and the task pane should be visible. Figure 1-2 in your bookshows how this process should look.
After Access has started, the Access window will appear similar to Figure 1-3 in your book. The menu, database toolbar and task pane are visible and give you many options to begin working with Access. The task pane is a very significant feature when first starting Access. It gives you many shortcuts to begin working with existing database files, opening new database files and applying templates to new database files.
The Access Desktop consists of many useful features that help to make the operation of Access much simpler. Examples of these features include the title bar, the close button, the database toolbar, the taskbar, the status bar and the database window. See Figure 1-7 in your book to view the Access Desktop with the Database window open.
There are many toolbars available that can be seen by clicking on the View menu and choosing the Customize option. Once the Customize dialog box opens, click on the Toolbars tab and click the checkboxes next to the toolbars that you would like to see on the Access Desktop. Once you are done, click on the Close button to close the dialog box. Your new settings should be in effect on the desktop.
An Access database file contains all of the objects that your database will use, including tables, forms, reports, queries, macros and modules. There are many ways to create a new database. These include manually creating the objects within the new database, using the Database Wizard to walk you through the creation of new objects and using an existing template.
Before you create a new database, it is important to understand that Access always saves a new database before it will allow you to add new objects. There is no way around this and you will be forced into this when you create a new database. Figure 1-5 in your book shows how the save/create process should occur.
The File New Database dialog box will appear and you will have to give your new database a name and choose a location for the database to reside. If you cancel this dialog box, your new database WILL NOT be created.
As mentioned above, database creation can occur many different ways. Access comes with an abundant supply of templates that will allow you to create a new database based on an example of one that is commonly used. The templates are stored within the Access program and are also available from the Microsoft Web Site. Please see the Microsoft General Site URL listed in the LINKS section of this manual. Examples of templates include Event Management and Resource Scheduling. The Database Wizard is a very powerful feature and gives new Access users the ability to quickly create new databases with tables, forms and reports. All that you have to do is answer questions as you step through the wizard and Access does the rest. Once you have created a new database with the wizard, you can use Access to customize it or change it as necessary. You should explore the Database Wizard and the templates offered to get a feel for how easy Access makes the creation of new databases.
Tables are an essential part of an Access database. They store the information that you will use in your database when creating forms, reports, queries, macros and VBA modules. Figure 1-8 in your book shows the structure of a table and also how data looks when it is stored in a table. As you can see from the example, the key components of a table are fields.
The fields, or columns, in a table can contain various data types. These include things like Text, Numeric Data, and Date/Time. Fields are made up of three parts: Field Name, Data Type and Description. There are various rules that need to be followed when defining new fields. These include the following:
You can create tables and fields using various techniques. Access includes a Table Wizard that walks you through the process of creating a new table and fields. This wizard is similar to the Database Wizard. If you would prefer to manually create tables and fields, this is also an option. You can create a new table by either opening it in Design View or Data mode. Figure 1-9 in your book shows the three different options available for creating a new table. Figures 1-10 and 1-11 in your book display what the Design View looks like. You add fields by clicking in the Field Name column and typing in the name of the new field. Once you have input the name, you can use the Tab key to move to the Data Type column and select a data type from the drop-down list. To enter additional fields, repeat this process until you are finished. You also have the option of creating a primary key in the Design View. To make a field the primary key of the table, click anywhere in the Field Name of the field you would like to select and then press the Primary Key button on the Table toolbar. Figures 1-13 and 1-14 in your book show how to perform this operation.
As you create more tables and fields, you will discover additional options. When you are creating fields, you have the opportunity to set several Field Properties. Examples of these are Field Size, Format, Required, etc. Figure 1-15 in your book gives you a better idea of the types of properties that are associated with fields. Another way to learn more about the field properties is to use the Access Help System.
To work with a table in Access, you must first open it. You have the choice of viewing it in design mode or datasheet mode. To open the table, you can double-click on it from the Database window or you can right-click on it and select the open or design options.
Once the table is open you can change the table structure or enter data into the table. To change the table structure, you should be in Design View. You can switch between views by clicking the View button from the Table Toolbar and selecting any of the four options. Select the Design View option to make structural changes. Once you have made these changes, you can switch back to Datasheet View by clicking on the View button again and selecting the Datasheet View option. If you make structural changes while in Design View and try to switch back to Datasheet View, Access will force you to save your changes first. If you decide to cancel the save, your structural changes will be lost.
One other important thing you should know about working with an existing table is that if you make structural changes while you already have data entered into your table, you run the risk of damaging or losing data. An example of this would be if you have a field that contains last names and the Field Size is initially set to 30 characters. If you decided to change the Field Size to 25 characters and one of the rows of data has a last name that is greater than 25 characters, you will lose the last part of the name. Access will warn you before you perform this operation, but if you decide to go ahead with it, there is absolutelyNO way to get back the data that you lost unless you have a backup copy of your database file.
Once a table is created, you can add records to it. If you have not created a form for data entry, you must open the table before you can add records to it. You can open the table using any of the following methods:
After you have opened the table, you can begin adding records to it. The cursor should be positioned in the first field of the first record. After you have entered data into the first field, use the Tab key to move the cursor to the next field. Continue doing this until you have entered all the data in the record. After you have entered data in the last field of the record, use the Tab key to move to the next record in the table. The cursor will be positioned in the first field and Access will be ready to accept data for the new record. Figures 1-22 through 1-27 in your book illustrate this concept.
After you have entered data in the last field of the record, use the Tab key to move to the next record in the table. You can also enter new records using any of the following methods:
Note: If any of the fields in your table are required, you will not be able to add a new record to the table until you fill in all the required fields in the existing records.
Once you are done working with a table, you should close it. You can use any of the following methods to close a table in Access:
If you have not saved the table, Access will display a message asking whether or not you want to save the changes to the table. If the table is new, Access will also prompt you for a table name. When you close the table, the Table window should close and the Database window should reappear.
When you are finished working with a database you should close it. If you want to keep Access open, you should close the database by using any of the following methods:
If you would also like to quit working with Access, you should close your database and Access by using any of the following methods:
To work with a database in Access, the Access program must be running. Once you start Access, you can use any of the following methods to open a database:
Using any of the above options will display the Open dialog box as shown in Figure 1-29 in your book.. From the Open dialog box you can select the database file that you would like to open. You can use the Look in box to select the Drive and Directory/Folder location of the database file that you want to open. Once the file has been selected, click the Open button. The Open dialog box will close and the Database window should appear.
Access gives you the capability of previewing and printing the contents of a table. To display the contents of the table in a preview window, right-click on the name of the table from the Database window and select the Print Preview menu option. This procedure is shown in Figure 1-36 in your book.
Once the report preview is visible, there are many options available to you to enhance the look of your report before you actually print it. Let's discuss some of them by looking at Figures 1-37 and 1-38 in your book.
The preview in Figure 1-37 shows the whole report in the window. If you look at the Print Preview Toolbar, you will see the word Fit in the Zoom drop-down box. If you would like to get a better look at the report contents in the window, you can change the Zoom option. If you click on the Zoom drop-down box, you will see Zoom values ranging from Fit to 1000%. You can also type in a numeric value for a custom size zoom. Figure 1-38 shows the report at a zoom of 100%. Another way to move from Fit to 100% is by clicking anywhere on the report preview. If your mouse pointer is over the report, it should change from an arrow to a magnifying glass. When it is in the shape of a magnifying glass, you can click and the Zoom of the report preview will switch between Fit and 100%.
Other options of interest to you will be in the Page Setup dialog box. To open this box, use any of the following options:
Figure 1-40 in your book shows the options that are available on the Margin tab. This is where you can change the size of the Margins to make the report fit better on the printout. Margins might become an issue when you have a lot of data and not enough room with the default margins. The defaults are generally 1 inch on all sides of the paper. Most printers will let you make them as small as .25 inches, but this is not a steadfast rule. Some printers will let you make them smaller and other printers will adjust them to the smallest size they will allow. The Print Headings check box is usually checked by default. By being checked, the table name, current date and page number are displayed on the report. If you would prefer not to have these items appear on the report, uncheck this box.
Figure 1-41 in your book shows the options that are available on the Page tab. The most important option here is the Orientation. The two types of printer layout orientations available are portrait and landscape. Both allow you to view the table contents, but in different ways. Portrait displays with the contents flowing across the width of the page while landscape shows the contents flowing across the length of the page. To close the Page Setup dialog box, click the OK button. This will save any changes that you have made. If you don't want to save your changes, click the Cancel button.
Once you are done changing the report layout, you can actually print the preview. To do this, use any of the following methods:
After your report has printed, close the Print Preview window by using any of the following methods:
Forms give us a professional looking mechanism to view the data in our tables. A slight disadvantage to a form is that you can only see one record at a time. This is not really a hindrance though, because a form allows us to view all of the fields of the record at once. This is something we can't always do in the Datasheet view, especially if we have a large number of fields within our table. A form also gives us all of the navigational tools that we need to move through multiple records. To get a better idea of what a form looks like in Access, let's look at Figure 1-51 in your book.
The form in Figure 1-51 was created using the AutoForm option. AutoForm automatically creates a basic form for whichever table happens to be selected in the Database window. It gives you a good starting point to make further customizations to your forms. We won't talk much here about creating forms, since this is a very large subject and will be studied more in-depth as you progress through this class. Instead, let's spend some time focusing on how the form is used to display data.
Look at Figure 1-56 in your book. On the status bar at the bottom of the Form window, there are navigational tools that make it very easy to maneuver through the records in your table. The following is a description of what each navigational button does:
Another nice feature on the status bar is the record numbering. Access displays the number of the current record and also the total number of records that are in the table.
You may have noticed that there is no delete button on the status bar. This is okay though, because it is possible to delete records while in form view. You can use any of the following methods:
Custom Reports give us many options for determining how our data should be presented to our users. Creating a custom report can get very involved, depending on the different types of things you want to do. One of the nicer features of Access is the capability of using the Report Wizard to quickly create a custom report. Figure 1-63 in your book shows the different options for creating a report. Report Wizard is one of 6 options that you can use when creating a report. The following gives a brief description of the other options:
Unless you have a specific report need, using the Report Wizard is the safest and quickest way of creating a quality report. It allows you to select the fields for the report, determine the sort order (if any) of the records, include record groupings, choose a specific layout, determine the page orientation (portrait or landscape), and pick a style.
The last page of the Report Wizard asks you for a title for the report. Keep in mind that this title will also be used as the name of the new report. You also have the option of previewing the report or opening it in design mode to make changes to it. Once you have named the report and decide how to open it, click the Finish button. It doesn't matter which display option you chose -- you can switch to other views by clicking the View button on the Report Toolbar and selecting any of the options from the drop-down menu. As you make changes to the layout of the report, you can switch back to print preview to see how the report will look with data in it. This is a wonderful feature since you don't have to save the changes to see how they will affect your report.
The Access Help system is a very powerful feature of the Access DBMS. It contains detailed information on every feature within Access. Many people use Access without realizing that they have help available right at their fingertips. There are many ways to use the Help system, with one of the most popular features being the Ask a Question option. See Figure 1-73 in your book to view the Ask a Question box.
You can type in any question that you want in the box and then press the Enter key. Access returns a topic list and you can click on any topic in the list to get more information. Once you do that, the full Access Help System will appear as shown in Figure 1-76 in your book.
From the Help System, you can read more information about your original question or you can use the other features to look up other topics. The Contents tab shows information that is grouped by general categories and the Index tab shows information like an index in a book would appear.
The above explains some of the Help features of Access. Look at Table 1-2 in your book for a more in-depth explanation of all of the Help options within the Help System. As with any type of system, the best way to understand how the Help System works is to use it. It contains many examples and provides step-by-step instructions for performing specific tasks.
Redundancy can cause serious problems when dealing with large amounts of data in a database. Proper design is the key to avoiding many of the common pitfalls seen in poorly designed databases. Normalization is one of the most popular techniques for database design. It allows you to setup relationships between tables so that you can associate data between the tables. Figure 1-77 in your book shows you a customer table that contains customer information. Stored along with the customer information is the information for the drivers that make deliveries to each customer. Notice that there are many records where the driver information is duplicated. This duplication, or redundant use of data, is wasting space in this table. Now that we know the problem, how do we fix it?
Look at Figure 1-78 in your book. There are now two tables that contain information instead of one. One table is devoted to driver information and the other contains only customer information. Even though we've separated the driver information from the customer information, we can still track which driver makes deliveries to specific customers by leaving one common field in each table. In this case, you will see that the driver number field exists in both tables. Access, as with all relational-based databases, gives us the ability to link the common fields in these two tables so that we can pull out the corresponding information for reports and other uses.
Being able to link tables and omit redundancy helps greatly with the performance of a database. Redundancy is a bad thing and enables the following things to occur to your database:
Keep the above things in mind and always strive to avoid them when designing a database.
| Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 |
| Discovering Computers | Windows XP | Word 2002 | PowerPoint 2002 | Excel 2002 | Access 2002 | Site Map |