NJNYACRAO Annual 2003 Conference
Presented by: Chester N. Barkan Registrar, C.W. Post Campus – Long Island University October 22, 2003 St Johns’ University – Manhattan Campus
Microsoft Access XP – Introduction
INTRODUCTION Microsoft Access is a powerful tool that you can use to create relational databases for storing and retrieving information. A relational database is an application that allows you to organize, manipulate, query and view your data, while building links between related topics. In the same way that a word processor helps you create a document, Access helps you create a database. A database is a collection of information relating to the same topic just like how a rolodex keeps a collection of names and addresses. DATABASE DESIGN Before you can begin to use Microsoft Access successfully, having an idea of what you want your database to look like is essential. You should know the type of information your database will store before you create it. Access stores similar information in the form of tables which contain records. Each record is made up of several pieces of data separated in fields. Designers of databases use a process called normalization when putting data together. This process helps to keep the data complete and consistent. The four most important rules of normalization are: 1. All fields should be broken down into the smallest pieces of information (i.e. First Name and Last Name). 2. All tables should have one field that is the primary key. The primary key is a piece of information that distinguishes each record from another (i.e. order number or social security number). With the primary key you can prevent duplication. 3. All fields in a record must refer to a record’s primary key. All fields must be related (i.e. SSN, Student’s First Name, Last Name, Address). 4. No record in a table should be related to another record in the same table. (i.e. record one should not be related to record nine). The other part in successful database design is considering data output. How would you like to retrieve your data? Different methods consist of retrieving data with a form, report, query or filter. STARTING ACCESS In Windows, click on Start, Programs and Microsoft Access or Office XP, Microsoft Access. DATABASE WIZARD
2
When you first open Access, the New File task pane appears on the right side of the window, giving you a choice of things to do. You can create a new database from scratch, or use the database wizard to guide you through the setup. The easiest way to start a database is through the use of the Wizard. You can pick from a variety of wizards to create a specific type of database. In the New File task pane, click on General Templates. In the Templates dialog box, select the Databases tab. Select the type of database you’d like to use. Click OK. Enter a name for your new database and then click on the Create button. Follow the onscreen instructions. CREATING A NEW DATABASE WITHOUT THE WIZARD 1. To create a database when you first start Access, select Blank Database from the New File task pane. 2. Enter a name for your new database and then click on the Create button. CLIPPIT – OFFICE ASSISTANT Clippit, the Office Assistant, appears the first time you open Microsoft Access or when you activate it to ask a question. If the Office Assistant gets in your way you can easily hide it by right-clicking and selecting Hide. To get him back, click Help from the Menu Bar and Microsoft Access Help. Tips are indicated by a light bulb. Click on it to receive a tip on whatever you are working on. ELEMENTS OF AN ACCESS DATABASE WINDOW 1. Menu Bar - Shows the menus available in Access. 2. Toolbar - Displays buttons (icons) which give instant access to the more frequently used commands. 3. Database Window - Controls the operating mode of Access and selects the active document window’s current function. Also displays the name of the database. 4. Database Window Toolbar – Allows you to open or design the selected database object. 5. Objects Bar – A list of shortcuts to database objects. 6. New Objects Shortcuts – A list of shortcuts to create a new database object. GETTING HELP 1. Click on the Help menu and choose the appropriate option for your needs. 2. To identify an unfamiliar object, choose What’s This? and then click on the unfamiliar object or menu item. A brief explanation of the object will appear. OPENING A DATABASE FILE 1. Click on File, Open… (Choose New if you wish to open a new database.) 2. Select the appropriate drive, subfolder and Files of type. 3. Select the file in the File names box then click on Open. There are different options to choose from when opening a file. Click on the down arrow next to Open.
3
Open Open Read-Only – you won’t be able to save changes made to data or object definitions in this database Open Exclusive – you prevent others from opening the database over the network while you are using it Open Exclusive Read-Only
VIEW OPTIONS 1. Design View – Allows you to create and modify tables, queries, forms, and reports. 2. Datasheet View – Allows you to see data in a table, form, query, view or stored procedure in a row and column format. CREATING A TABLE A Table is a collection of data on a specific topic, such as student information or vendor purchases. Using a separate table for each topic means that you store that data only once which makes your database more efficient and reduces data-entry errors. Access allows you to create tables in a number of ways: 1. Create table in Design view – allows you to create table in Design view by entering field names and properties 2. Create table by using wizard – the wizard assists you with creating a table 3. Create table by entering data – by default, Access creates 10 fields allowing you to enter data Creating a table in Design View Double-click on Create a table in Design View. Enter the primary key as the first field name. The primary key is a field that contains a unique value such as a SSN or customer ID. Click the field and then select the primary key button from the toolbar. Enter the name of the next field (use an abbreviation or short word). Select the Data Type. Enter a description of the field name. Enter any additional information in the Field Properties section. (For an explanation of each Field Property, perform a search using Microsoft Access Help on Field Property Reference). Repeat all steps for each field name. Then save table. INSERTING/DELETING ROWS IN DESIGN VIEW To insert or delete rows, right-click and then select Insert Rows or Delete Rows from the pop-up menu. SAVING CHANGES TO YOUR FILE Click on the File menu and choose Save or press Ctrl+S. It’s a good idea to save often, especially if the information you are composing does not exist anywhere else.
4
PRINT PREVIEW To see a preview of what the printed page(s) will look like before actually printing, select Print Preview from the Tool Bar or from the Menu select File, Print Preview. CLOSING A DATABASE Click on the File menu and choose Close. EXITING FROM ACCESS Select File, Exit from the menu.
Shortcut Keys to Navigate in Datasheet View
F5 TAB or RIGHT ARROW END SHIFT-TAB or LEFT ARROW HOME
DOWN ARROW CTRL-DOWN ARROW CTRL-END UP ARROW CTRL-UP ARROW CTRL-HOME PAGE DOWN PAGE UP CTRL-PAGE DOWN CTRL-PAGE UP
To move to the record number box; then type the record number and press Enter To move to the next field
To move to the last field in the current record, in Navigation Mode* To move to the previous field
To move to the first field in the current record, in Navigation Mode To move to the current field in the next record To move to the current field in the last record, in Navigation Mode To move to the last field in the last record, in Navigation Mode To move to the current field in the previous record To move to the current field in the current record, Navigation Mode To move to the first field in the first record To go down one screen To go up one screen To go right one screen To go left one screen
*Note: Navigation mode is the mode in which an entire field is highlighted and the insertion point is not visible.
5
Formatting and Useful Tools
ADJUSTING COLUMN WIDTHS & ROW HEIGHTS To adjust column width, select the Format menu and Column Width… Either enter the number of field characters or select Best Fit to adjust the width to the field with the most characters. You can also click and drag the right column border to the desired width or double-click the right border for Best Fit. To adjust the row height, select Format, Row Height…. Enter the number of points (measures the height of the row) or click and drag the bottom row border to the desired height. DELETING A RECORD OR COLUMN To delete a record, select the record, choose the Edit menu, and select Delete. Choose Yes to confirm the deletion of the record. To delete a column, select the column. select the Edit menu, Delete Column. Choose Yes to confirm the deletion of the column. Note: Access asks you to confirm whether or not you want to delete the column or record. Once you select yes, you can not Undo the command. SORTING DATA 1. Place the insertion point in the column you would like to sort. 2. Select the Records menu and point to Sort. 3. Choose either Sort Ascending or Sort Descending. EDITING EXISTING DATA Select the field you would like to edit, then type in text, delete or backspace. You can also use the F2 key on your keyboard as a shortcut key. CUT/COPY & PASTE 1. Select and highlight the data you want to cut or copy. 2. To copy the data, click on the Edit menu, Copy or to move the data, click on Cut. 3. To replace the current value of the field to be replaced, highlight the whole field. 4. To insert the data within the existing data, place the insertion point where you want to paste. 5. Select Edit, Paste from the menu. CREATING A SELECT QUERY You use queries to view, change and analyze data in different ways. You can also use queries as the source of forms, reports and data access pages. Access allows you to create queries using the Wizard or in Design View. A Select Query retrieves data from one or more tables by using criteria you specify and then displays it in the order you want. 1. Select the Queries button from the Objects bar. 2. Double-click on Create query in Design View.
6
3. Select the table(s) and/or other queries you’d like to use in your query. Click Add and then Close after adding each object. 4. From the field list, double-click or drag the field names that you want to appear, set criteria on, group by, update or sort in your query. 5. In the Criteria cell, you can add criteria to retrieve certain records. Enter the value from one of the records in the criteria cell of that field and hit the Enter key. (You can enter more than one criteria in the same field by using And or Or. You can also enter criteria in another field.) 6. Click on the Query menu, then Run. 7. Now you can view, make changes or print out your query results. 8. To close the query and return to your database, close the window or select File, Close from the Menu bar. MORE ON SELECT QUERIES Other Operators that can be used when setting criteria: AND, OR, LIKE*, >=, " button to move them to the right screen for "Selected Fields." A faster method is to press the ">>" button, which will move all the fields over. When this is done click the "Next" button.
You now need to choose the layout for your form. You can choose from columnar, tabular, datasheet, and justified. When you click on each one, an image of the layout will appear.
Select the "columnar" layout, unless you prefer a different one Click the "Next" button.
In the next window, you need to choose the style for your form. Click on each of the style options to preview them.
Make your style selection Click the "Next" button.
In the final screen of the Form Wizard, you need to name your form:
The default name is the same as the name of the table you selected. Keep the default name. 15
On this window, you are also asked whether you want to modify the design of the form or to open the form to view or enter information. Choose to "open the form to view or enter information," then click the "Finish" button. Your new form will be open in the Access window. The sample form for this lesson uses a "columnar" layout and "standard" style and looks like the following:
To open this form from the main menu, you would click on the "Form" tab or section of the main Access window, select the form name, then click "Open." Once open, the form is ready for you to view the currently entered records or for you to enter new data. As you can see, only one record is displayed in the above form. The buttons at the bottom of the form allow you to scroll through the records or add a new record. The buttons are described below: View first record View previous record View next record View last record Add new record
Modifying a form (Access XP)
The form you just created with the Form Wizard may not be perfectly suited to your preferences. You can make changes to the form by going to the "design" view. If the form is open, go to the View menu and select "Design View" as follows (or click the "design button" mentioned previously):
16
If the form is not open, go to the Form tab or section in the main Access window, select the form you wish to modify, then click on the "Design" button. Either method will produce the following screen:
For your assignment, you are asked to 1. change the length of an entry field. 2. change the visible property of three entry fields (fill colour, font colour, and a special effect), and 3. change the descriptive label of a field. The instructions on how to complete these requirements are provided below. Changing the length of an entry field
17
Click on the entry field you wish to lengthen or shorten. Move the pointer to the edge of the field (on top of a "handle" shown circled below), so that the pointer changes to a double-headed arrow. Next, click and drag the edge of the field to the desired length.
Change entry field properties To change the properties of an entry field, right click on the entry field to get the following pop-up menu:
Build Event and Change To will not be covered in this module. Tab Order - lets you change the order in which you tab from one field to another Align - lets you align a field to the grid that appears in the design view. Size - lets you resize a box to to fix the contents or to the grid lines. Fill/Back Color - lets you change the background color of an entry field Font/Fore Color - lets you change the font that appears in the entry field Special Effect - lets you add an effect, such as a shadow, to an entry box Conditional Formatting - allows you to change the formatting of a given field if it contains a certain value, focus, etc. Properties - lets you change the field format, data, and more
The Tab Order option allows you to change the order in which you tab from one field to another. The order is initially determined by which fields are created first. If you move the fields around in the design view, you may want to change the tab order to keep the tab moving from the top box to the bottom box in the form. The tab order window looks like the following:
18
As the instructions say, you need only to drag and drop the field order to suit your needs. The "Properties" window mentioned briefly above will look like the following:
Most of the fields will likely not have any meaning to you right now. You can explore the list, though, by scrolling down in the above window.For more information on what each of these properties mean, go to the Help menu and search "Field Properties Reference." Relocating a field To move an entry field to a new location in the design view, simply use the click and drag procedure and drop the field in the location you want. Expanding the descriptive label of a field
19
To shorten the descriptive label of a field, say from "Parent Phone Number" to "Phone," click on the descriptive label (this is the box to the left of the entry field) and simply revise the text as needed. Be sure not to change the name of the entry field source as that will break the connection between the entry field and the table data. To return to the form view, click on the View menu and select "Form View."
Sorting and filtering records (Access XP)
Sorting records The datasheet view of the table you created will always appear sorted in ascending order of the data in the primary key field. In a previous lesson, you set the field for the last name as the primary key field. If you want to view the table or form sorted by a different field, click on any cell in the desired field then click either the ascending or descending sort button in the toolbar, which look like the following:
Filtering records A filter allows you to apply a set of selection criteria and/or sorting instructions to the records in a table. The act of the filter is temporary, so the next time you view your data, you will have to repeat the filter process to view selected records a second time. In either the table or form view, you may apply a filter to the records. To do this,
go to the "Records" menu select "Filter" to view the following options:
Using "Filter by Form," you can click on any of the fields to get a drop-down menu of the data you have entered. In that drop-down menu, choose a value you wish to search for.
20
Next, you can click on the "OR" tab to add another value from that same field. This can be helpful if you have entered "allergic by peanuts" in more than one way (i.e., "allergic to nuts).
When you have finished making your selection, go to the "Filter" menu Select "Apply Filter/Sort" (as shown below)
Remember, that EACH TIME you apply a filter, you must undo that action in order to again view all of your records. To do this, select "Remove Filter/Sort" from the "Records" menu.
You may also access the filter options by right-clicking on the field you wish to search. You can select "Filter For" and type in the text you wish to search, then press Enter. You will then be shown the records that contain that value in the field you selected. Remember to select "Remove Filter/Sort" to return to viewing all records in your database. The Advanced Filter/Sort option will allow you to combine fields and identify more complex search criteria. There is no filter or sort assignment for this module. For more information on Filters and Sort, go to the Help menu and search for these topics.
Creating a query (Access XP)
Queries are used to view, change, and analyze data in different ways. You can also use them as the source of records for forms and reports. Queries can bring together data from records from multiple tables and sort them in a particular order. They can also perform 21
calculations on groups of data. There are many different types of queries in Access. There are select queries, parameter queries, crosstab queries, action queries, and SQL queries. The most simple and common one is the select query. For information on the differences between filters and queries, you may want to visit the following website. It is an older tutorial, but one of the most succinct. UBC Commerce 391 Microsoft Access Tutorial I - Creating Filters and Queries http://mis.commerce.ubc.ca/courses/comm391/tutorials/access1/access1.htm A query can be created using a wizard or in the design view. You can create a simple query using the Simple Query Wizard.
Go to the Query tab or section of the main Access window Choose "Create Query by Using Wizard."
Queries are most useful in searching for information from more than one table. Since we are working only with one table for the assignment, we will not go into any great depth on queries. To learn more about queries, search for "select query" in the contents of your Help menu. For your assignment, you will save a simple filter as a query. For example, you may need to know quickly what students have a certain medical condition. If you had a "medical info" field in your database, you could do the following:
Choose "Filter by Form" (found under the Records menu) in the table or form view. Click on the cell beneath the "Medical Info" field, so that a drop-down menu appears. From the values, choose "Allergic to Peanuts" (use the "OR" tab if you have more than one way that you have typed in this allergy). Before applying the filter, go to the File menu and select "Save as Query" - or click the special "Save As Query" button in the toolbar:
A pop-up window will prompt you to give this query a name. Type in "Allergy to Peanuts." 22
This query would then show you which students in your database had allergies to peanuts. By saving this query, I could skip the filter step and simply open the "Allergic to Peanuts" query in the "Queries" section of the main Access window. This query will then produce an up-to-date list of all students allergic to peanuts.
For your assignment, save a filter as a query. You may do this for "Allergic to Peanuts" for a "Medical Info" field as shown in the sample or create a different query of your own. Please continue to the last section on Creating a Report.
Creating a report
Last but not least, you will probably want to create a report of your database. Reports provide a nicely formatted display of data for printing out your records or even viewing them online. If you started a CD collection database, you may need to make a report of your inventory for your insurance company. For student records, you may need to make a report of all your students for the school office files. For your assignment, you will need to create a report for your database. To create a report,
Click on the "Reports" tab in the main Access window. Click "Create Report by Using a Wizard." This wizard will work much like the one you used to create a form. Select the fields you want to include in your report then click the Next button. The next few screens will cover "grouping options," "sort order," "layout," and "style." For the assignment, you do not need to change any of these settings from the default, but you are welcome to explore and change these settings to suit your preferences. The final screen asks you to give a name to your report, then you can choose to preview your report and click the Finish button. Our sample report, which has three records, is shown below. As you can see, I opted not to include the fields relating to the parent's information.
23
After previewing your report, close it. You will be able to access it again under the Report section of the main Access window. If you want to change the design of the report, you can do so by opening it in "design" view by clicking on the "design" button after selecting the report name. You may need to modify field lengths, so that the longer text in data fields is not cut off. You may also display the report sorted in a different order. For example, the above report could be sorted by birthday instead of by last name. To do this, open the report in design view, go to the View menu and select "Sorting and Grouping." You can also click on its button on the toolbar, which looks like the following:
The Sorting and Grouping window will then appear as follows:
There is a drop-down menu in each cell. Under the "Field/Expression" column, you can choose the field you would like the table sorted by. Under the "Sort Order" column, you can select ascending or descending from the drop-down menu. Once you have set your preferences for sorting, close this window and preview your report again. You will notice the data has been rearranged according to your sort preferences.
24
When working in the design view, you may find yourself wanting to add a field to the report design that was not initially included (or perhaps you deleted the field and need to replace it). To add a field not previously included in the report,
Go to the View menu Select "Field List." You may also click on the Field List icon on the toolbar, which looks like the following: The Field List window is small and will look like the following for our sample database:
To add a field in the report design, simply click on the field name in the Field List window and drag it to the report window. The field and its description label will appear. Nice and easy!
25
26