Access 2003 - Basics & Beyond Guide: What Is A Database?
Access 2003 - Basics & Beyond Guide: What Is A Database?
Access 2003 - Basics & Beyond Guide: What Is A Database?
This table will be used in throughout this training guide to demonstrate the various functions of Microsoft Access 2003.
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
but can also manage and organize many tables possessing common components into a relational database.
Getting Started
Starting Access 2003
1. Click the Start button on the Windows Taskbar. 2. Point to Programs. 3. Click Microsoft Access from the submenu.
The "Database Name: Database" dialog box is then displayed (see Figure 2, below).
2
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
Naming Conventions
Database development includes naming object files consistently. You do this by using the assigned naming conventions for each object within Microsoft Access 2003. Naming conventions are not mandatory, but they do make your work consistent. If you plan on sharing a database with others, then it is a particularly good idea to use them.
3
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
Let's say you have just created a table called "My Table". The LNC way to save that table is to start the filename 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 and each word within the file name should begin with a capital letter. Since databases are often shared with others, this format allows other users to easily identify the file and it saves you time when you begin to use some of the more advanced features that Access has to offer. Figure 1: Save As dialog box example
As you can see from Figure 1, the file name is preceded by "tbl" for table, and the file name does not contain any spaces or underscores.
4
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
1. Click Tables in the Objects section of the "Database Name: Database" dialog box. 2. Next, click the New button. 3. In the "New Table" dialog box (see Figure 1, below) click Design View. 4. Then click on the OK button. Figure 1: Dialog boxes showing Tables choice and New Table options
Data Types
Before entering data into the fields in the Design View of your new table you should become familiar with the kinds of data you can store in these fields. By selecting the proper Data Type for a field you can optimize its usefulness when you perform queries, sorts and produce reports. Data Type Text Memo Number Date/Time Currency Description Text and/or numbers that don't require calculations up to 255 characters Any text and/or numbers up to 65,535 characters Numeric data used in calculations Date and time values for years 100 through 9999 Currency values used in calculations 5
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
AutoNumber Unique sequential number assigned automatically to each new record Yes/No OLE Object Hyperlink Values can be only Yes or No, True or False, or On or Off An object such as a spreadsheet, document, graphic, etc. WWW hyperlink address
1. Click the mouse in the first blank cell in the Field Name column and enter the desired name for that field. 2. Tap the TAB key to move the insertion pointer to the Data Type column. 3. The default Data Type is Text. To change the Data Type click on the down-arrow box in the cell and then click on the desired Data Type from the drop list menu. 4. Tap the TAB key again and the insertion pointer moves to the Description column. 5. Enter in any text you want to describe this field. 6. Tap the TAB key again to repeat this process for the next field. As you create fields notice the two tabs General and Lookup in the Field Properties portion of the Design View window. By clicking on the General tab you can customize the properties of the fields by defining such characteristics as Field Size, Format, Decimal Places, etc. To modify one of these field properties click on the property, click on the down arrow box and then click on the desired value from the drop list menu. 6
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
7. When finished defining the fields for the table click on the Save button in the toolbar (or click File on the menu bar and then click the Save option in the File drop-down menu). Now you're ready to enter data into your database.
Click the View button on the toolbar, or Click View on the menu bar and then click the Datasheet View option in the View menu.
The Datasheet View allows you to visualize your data, fields and records in the form of a spreadsheet (see Figure 1, below). Each column in the Datasheet View will contain a particular field (such as a person's first name or the city in which they live). Each row in the Datasheet View will contain those related fields to form an individual record. Figure 1: Datasheet View
The contents of the fields and records of the table and the appearance of the Datasheet View can be manipulated in the following ways:
7
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
Double click on the current field name. Enter a new field name up to 64 characters. Tap the ENTER key (or click mouse anywhere outside of field name).
To delete a column or Click anywhere in the column to be deleted. From the field: Edit menu click Delete Column. In the dialog box asking, "Do you want to permanently delete the selected field(s) and all the data in the field(s)?", click Yes. To insert a column or add a field: Click anywhere in the column to the right of where the new column is to be inserted. From the Insert menu click Column. Click on the field name of the column to be moved. Click on the column again and continue to hold the mouse button down while dragging the column to its new location. Click the mouse button on the datasheet cell. Enter the desired data. Tap the ENTER or TAB key to move to the next field on the right. Hold down the SHIFT key and tap the TAB key to move back to the previous field. Click anywhere in the column that needs to be changed. From the Format menu click Column Width. In the Column Width dialog box, enter the desired width in the "Column Width": text box and click OK. Or choose Best Fit to let Access determine the best width for the data that has been entered. Click anywhere in the row that needs to be changed. From the Format menu click Row Height. In the Row Height dialog box enter the desired height in the "Row Height": text box and click OK. Click anywhere in the column to be hidden. From the Format Menu click Hide Columns. From the Format menu click on Datasheet. In the Datasheet Formatting dialog box click on Horizontal and/or Vertical in the "Gridlines Shown" area to turn 8
Prepared By Steve Dan, User Support Services October 2006
To move a column:
To hide a column:
To remove gridlines:
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
gridlines on or off. To select a font: From the Format menu click Font. In the Font dialog box select the desired Font, Font Style, and Size then click OK. Click on the new record navigation button (located at the bottom of the screen) and enter data for the record into the appropriate fields. Click anywhere in the record or row to be deleted. From the Edit menu click on Delete Record. The dialog box will read, "You are about to delete 1 record(s). Are you sure you want to delete these record(s)?", click Yes. Click in the cell to be edited. Make the appropriate insertions or deletions, and then tap the ENTER or TAB key. Click the column that you would like to sort. From the Records menu, click Sort and choose Sort Ascending to go from A-Z, or Sort Descending to go from Z-A. From the File menu click Print. In the Print dialog box click OK. Click the Save button on the Table Datasheet toolbar. In the "Save As" dialog box, enter the name of your table in the Table Name text box and click OK. (For more information about naming your file, please see the section entitled "Naming Conventions".) In the Microsoft Access dialog box stating, "There is no Primary Key defined. Do you want to create one now?", click Yes. A Primary Key field called ID will be created.
To delete a record:
Creating 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 9
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
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. To create a query click Queries in the Objects section of the "Database name: Database" dialog box and then click on the New button (see Figure 1, below).
Figure 1: Dialog boxes showing Queries choice and New Query options
In the "New Query" dialog box (see Figure 1, above), click Design View and then click the OK button. The "Show Table" dialog box appears (see Figure 2, below). Click on the Tables tab and then click on the name of the table containing the data to be queried. Click the Add button and then click the Close button.
10
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
1. From within the "Query 1: Select Query" window, click on the first Field cell. ( A sample "Select Query" window is shown in Figure 4, below). 2. Click the down arrow button to display the drop list of field names. 3. Click on the field name that you want to set criteria for. 4. Click on the Criteria cell and enter the criteria description. 5. Tap the ENTER key. 6. Repeat this process for any additional criteria using other fields. Note: If more than one field is tested by a criterion the only records selected will be those that meet the conditions of the first criterion and any successive criteria. If more than one criterion is defined for an individual field, those records that meet the first criterion or any successive criteria will be selected. Any reports using a particular query will only display those records meeting the collective query criteria and will only display those fields specified in the query. To include fields that are not being tested by any criteria but need to be part of a report, include that field in the "Select Query" window by clicking on the next Field cell and click on the desired field name, and leave the Criteria cell blank. Make sure the check box in the Show cell is checked.
11
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
Once the query criteria has been defined you will need to run the query by clicking the Run button an exclamation mark (!) on the toolbar, or by clicking Query on the Menu Bar and choosing Run. A sample of query results is shown in Figure 4, below. This sample query was created to determine who is over 50 years in age. Figure 4: Sample Query results
Save the query by clicking the Save button on the toolbar (or by clicking on the File menu item on the menu bar and selecting Save As). In the Save As dialog 12
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
box enter the name of the query and click OK. (For more information about naming your file, please see the section entitled "Naming Conventions".) This query can now be used to produce reports containing only those records meeting the query criterion.
In the "New Forms" dialog box (see Figure 1, above), click Autoform: Columnar to select this predefined form. Click on the down-arrow button to show the list of tables and click on the table name to be used with this form. Click the OK button. 13
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
Save the form by clicking on the Save button on the toolbar (or by clicking on the File menu item in the menu bar and then clicking Save As). Enter the name of the new form and then click the OK button. (For more information about naming your file, please see the section entitled "Naming Conventions".) To use the form once it has been defined click on the Forms tab in the "Database name: Database" dialog box and choose (click) the desired form name. Then click the Open button to get to the data entry dialog box (see Figure 2, below).
Figure 2: Dialog boxes showing Forms selection and data entry text boxes
The input form has now been created. You can begin to enter data at this point, or you can use the navigational toolbar located at the bottom of the input form. Click < to move backward, or click >* to enter a new record. Note: Moving to the next record will ensure that the previous record has been saved.)
Producing a Report
Reports are what Microsoft Access is all about. Once you have created the database, set up a table, created an input form, entered data into the table, 14
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
selected records from the table through a query, you now want to create a report to display the data as useful information. As in the case with forms, Microsoft Access provides several predefined reports and has a Report Wizard. The Report Wizard will be used in the example below. You can also create your own customized reports by using Microsoft Access report design tools. To create a report using the Report Wizard click on Reports in the Objects section of the "Database name: Database" dialog box and then click the New button (as illustrated in Figure 1, below). Figure 1: Dialog boxes showing Reports selection and New Report options
In the "New Reports" dialog box (see Figure 1, above) click Report Wizard and then click the OK button. In the first Report Wizard dialog box you are asked which fields you would like to include in your report. You can highlight the available field you want to work with and then click the > button, or you can choose all the fields listed by clicking the >> button. Click on the Next button to go to the next Wizard dialog box. Continue to click on the Next button in the successive dialog boxes while making the necessary changes along the way (shown in Figures 2 through 7, below). In the last dialog box enter the title for your report, choose "Preview the report" or "Modify the report's design", and then click Finish. The end result is displayed (as illustrated in Figure 8).
15
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
Note: For information about naming your report file, please see the section entitled "Naming Conventions". Figure 2: Report Wizard Selected Fields
16
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
17
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
18
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
you need help with the task the "Office Assistant" thinks you are trying to accomplish. To hide or disable the Office Assistant, please see Office Assistant Options below. You can close the dialog bubble but leave the Office Assistant on the screen by clicking the Close button. To end your Office Assistant session, click the Close button [X] in the upper right-hand corner of the little Office Assistant window.
If the Office Assistant is hidden or disabled, then on the main menu bar click Help. Choose Show the Office Assistant. When the Office Assistant is active, you may temporarily hide the assistant by right clicking on it and choosing Hide from the pop-up menu. If you would like to disable the Office Assistant (so that it doesn't automatically pop up while you're working), then follow these steps: 1. 2. 3. 4. Right click on the Office Assistant while it is active. Choose Options. Click on the Options tab if it is not already highlighted. Uncheck Use the Office Assistant by clicking on the box to the left of it. 5. Click OK at the bottom of the screen. This will disable the Office Assistant until you choose to activate it again by clicking on Help and then Show the Office Assistant.
Microsoft Access Help If you disable the Office Assistant (as described above), you can take additional advantage of the Microsoft Access Help feature. Click on the Microsoft Access Help button or go to Help on the main menu and then click on Microsoft Access Help, you will be able to look at a list of Contents, use the Answer Wizard, or access an Index of alphabetized topics. To use any of these features, simply click the appropriate tab. Double click on the topic that you are interested in or type the topic or question when prompted. On the right-hand side of the help screen, you will see your topic in more detail. If you would like to print the information for later use, simply click on the Print button at the top of the screen. Once you have obtained the information you need, click on the Close [X] button in the upper right-hand corner to close Help.
20
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.
What's This? Click Help on the main menu bar and choose What's This? if you are curious about a particular button or menu item. Once you have chosen What's This? you can point to any item on your screen to get a brief description of what it is. If you click on the item, you receive a bit more information and instructions for its use. When you have read the instructions, click anywhere in the worksheet window to return to normal operation. Office on the Web Click Help on the main menu bar and choose Office on the Web. This feature provides links to Web locations were you can find out more about Microsoft Office products. You need Internet connectivity and a Web browser in order to make use of this feature.
Printed Material
There are numerous books available to help you learn how to use Microsoft Access 2003. Students can purchase these and others at most bookstores.
Microsoft Access 2003 Step by Step (self study kit, including exercise CD, by Microsoft Press) Access 2003 Bible (comprehensive reference, by Cary N. Prague, Michael R. Irwin, Jennifer Reardon)
21
Prepared By Steve Dan, User Support Services October 2006
ITRs Technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.