| Table of Contents | | Previous | | Next |
In this course we will be using Microsoft Access 2000. Access is a relational database. All of the data is stored in flat tables and then the tables can be connected to one another to effectively work with 3D data. The data base consists of tables, forms, queries and reports. Data bases can be used to store, find, analyze and print information. Typical applications include mailing lists, customer orders, inventory and payroll. Databases can be created using the Database wizard or you can create your own database.
Many Access functions can be performed in multiple ways. The three primary ways of accessing commands are
After opening a database, the Database window appears. The Database window can be used to open and work with all of the objects in a database and to perform basic operations such as renaming or deleting database objects (right-click).
To plan a database you must decide what information needs to be included and determine
Tables. Gather all the information that needs to be included in the database and divide the information into separate tables. The same information should not appear in more than one table since it makes it much more difficult to keep the information up to date.Fields. Break the information down into its smallest components such as First Name and Last Name.
Primary key. A primary key is the one or more fields that uniquely identifies each record in a table. Each table in a database should have a primary key. As an example a table containing information about an employee could use the social security as the primary key.
Relationships. Relationships allow you to connect related information in a database. The usual method for creating relationships is to relate the primary key in one table to a matching field in another table (social security numbers for example).
A table stores information about a specific topic. You can have more than one table in a database. A table is composed of fields and records. The fields are basically the column heading of the table and the records are the rows.
Create. To create a table in datasheet view, click Tables on the Database window. Double-click Create table by entering data. Modify the field names. Enter the data using the Tab and Enter keys to move between fields. Save the table. After saving the table, a dialog box appears stating that the table does not have a primary key. To have Access create a primary key for you, click Yes. To create a table using a wizard, click on Tables in the Database window and double-click on Create table by using wizard.
Edit. To move through the records use the First record, Previous record, Next record and Last record buttons on the bottom of the table. The scrollbars may be used to scroll the records and fields. The arrow keys and the Page Up, Page Down, Tab and Enter keys may also be used to move through data. To select a field, position the mouse over the name of the field and click. To select multiple fields, position the mouse over the name of the first field and drag until you highlight all of the fields you wish to select. To select a record, position the mouse over the area to the left of the record you wish to select. To select multiple records, position the mouse over the name of the first record and drag until you highlight all of the records you wish to select. TO zoom into a cell, press and hold the Shift key as you press the F2 key. To hide a field, select the field and use the Format/Hide Columns command. To freeze a field, use the Format/Freeze Columns command (freezing allows you to see important data as you scroll through a large table).
Modify. To adjust the width o f a column, position the mouse pointer over the right edge of the column heading and drag the column edge to the desired position or double-click to have Access automatically set the column width according to the longest entry. To rename a field in a table, select and right-click on the column heading to open up a shortcut menu and select rename. To rearrange fields, position the mouse point over the field name and drag to its new location. To add a field use the Insert/Column menu. To delete a field, select the field and use the Edit/Delete Column menu. To add a record, click on the Add Record button near the bottom of the table. To delete a record, position the mouse over the area to the left of the record and click the Delete Record button.
Design. There are two views of a table. The Datasheet view allows you to display, enter and edit records. The Design view allows you to change the structure of the table by setting data types and field properties. To display the field properties for a particular field, click on the field in design view. To toggle between the Datasheet and Design views, click on the View button or use the View menu. Here is a list of modifications that can be made to a table in Design view.
Relationships. To set the primary key, open the table and switch to Design View. Click the field you want to set as the primary field and click the key icon. To select more than one field as the primary field use the Ctrl key to select more than one and then click the key icon. If the key icon is not present go to Tools/Customize/Commands/Table Design and drag the key icon to the toolbar. To create relationships between tables, use the following steps.
In this section we explain how to find, sort and filter data in tables, forms and queries.
Sorts. To sort the records in a table, click anywhere in the field you wish to sort and click on either the Sort Ascending or Sort Descending icons. Remove a sort using the Records/Remove Filter/Sort command. To sort using two fields, place the fields side by side in the order you wish to sort on. Select the fields and use one of the sorting icons.
Searches. Click in the field containing the data you want to find. Click the search icon to open the Find and Replace dialog box. Type in the data you want to find and set match to: Any Part of a Field, Whole Field or Start of a Field. Click Find Next to start the search. Click Find Next repeatedly to locate further matches.
Filter by selection. Click on the data you wish to filter on. For example if the field is Department then click on the cell containing Advertising. Click on the Filter icon to display all the employees in the Advertising Department. To redisplay all of the employees use the UnFilter icon or use the Records/Remove Filter/Sort command.
Filter by form. Click the Filter by Form icon. The Filter by Form window opens. Click on the Delete Criteria icon to remove the criteria from previous filters. Enter the criteria (<100, =Texas, Between 100 And 200) in the appropriate field. Access displays only the records that meet the specified criteria. To redisplay all of the records use the UnFilter icon or use the Records/Remove Filter/Sort command. To filter using multiple criteria, simply enter more than one criteria. Access will only display records meeting all the criteria. This is logical and. To use the logical or, enter the first criteria, click the Or tab and then enter the second criteria.
Forms provide a method of entering information into a database. Forms usually display one record at a time using boxes to show where the information is supposed to go. If the form uses data from more than one table, relationships must between the tables. The three views of a form are
AutoForm. To create a form using AutoForm, click Forms in the Database window. The New Form dialog box appears. Click on the type of form you wish to create (columnar - displays one record at a time, tabular - displays many records at a time, datasheet - displays many records at a time and resembles a table in datasheet view. Use the drop box to select the table containing the data for the form. Click OK to create the form. The form appears displaying the names of the data fields for the selected fields. Click the Save icon to save and name the form.
Form Wizard (one table). To create a form using the Form Wizard, click Forms in the Database window. Double-click Create form by using wizard. Use the Form Wizard to select the tables and fields to be included in the form. To add all of the fields at once click on the >> button. To remove fields from the form, double-click on the field in the Selected Fields text box. Click Next. Choose the layout for the form: columnar, tabular, datasheet or justified. Click Next. Choose a style. Click Next. Choose a name for the form. The form appears.
Form Wizard (multiple tables). Similar to the previous procedure except you use the Form Wizard to select fields from more than one table. Select the way you want the data to appear on the form. The data from the tables can appear in separate sections or together in one section. If you want the data from the tables to appear in separate sections, you must choose how you want to organize the sections.
Using forms. Click Forms in the Database window. Double-click the form you want to open. Move through the records using the First Record, Previous Record, Next Record and Last Record buttons. To move to a specific record, type in the number of the record. Access automatically saves the changes you make while using the form. To add a record, click on the Add Record icon. To delete a record, click on a field in the record and click the Delete Record icon. Note that both forms and subforms are listed in the Database window. You must open the main form to work with the contents of either the main form or any subforms.
AutoFormat. To format a form, click Forms in the Database window. Click on the form you wish to format. Click Design to open the form in Design view. Click on the little square in the upper left hand corner of the form to select it. Click on the AutoFormat icon to open the AutoFormat dialog box. This procedure must be repeated for subforms since they are not automatically formatted when the main form is formatted.
Queries allow you to obtain specific information from a database. This is done by specifying specific criteria such as find all the customers who live in California. It is possible to create a query that uses information form several tables but the tables must be related. The three views of a query are
Create (Design view). Click Query in the Database window. Double-click Create a query in design view. The Select Query window and Show Table dialog box appear. Click on a table you want to include in your query. Click Add to add the table to the query. A box appears in the Select Query window displaying the fields of the selected table. Repeat to add all of the tables you want to include in the query. Double-click on the fields you want to include in the query. Click the save icon to name and save the query. To run the query click on the exclamation point (!). To delete a table from a query, click on the table and press the Delete key.
Create (Wizard). Click Query in the Database window. Double-click Create a query by using the wizard. The Simple Query Wizard appears. Use the drop box to select the tables containing the fields you want to include in the query. Double-click each field you want to include in the query. To add all of the fields at once click on the >> button. To remove a field, double-click on the field in the Selected Fields text box. Click Next. If the query contains data that Access can summarize, you can select how you want to display the result of the query. Click on Summary Options to open the Summary Options dialog box. Click the check boxes for the calculations you want to perform (Sum, Ave, Min, Max, Count Records). Decide whether or not to count the number of records in each group. Click OK. If a field in the query contains dates, you can select how to group the dates (Unique Date/Time, Day, Month, Quarter, Year). Type a name for the query. Click Finish to create the query. Note you can use Design view to modify the query.
Modify. Design view is the view used to modify the query. To open a query, click on Queries in the Database window. Double-click on the query you want to open. To rearrange the fields in a query, position the mouse over the field you want to move and drag the field to its new location. Click on the Save icon to save the changes. To delete a field, position the mouse directly above the field you want to delete to obtain the down arrow pointer and then press the Delete key. To sort the results of a query, click the Sort area for the field you want to use to sort the results of the query. A drop box appears. Choose Sort Ascending or Sort Descending. Click on the exclamation point (!) to run the query. Return to Design view if necessary. You can always add another table to a query at any time by clicking on the add table icon.
Criteria. Click the Criteria area for the field you want to use to find specific records. Type in the criteria such as >500 and then press the Enter key. Access may add quotation marks (" ") or number signs (#). Click the exclamation point (!) to run the query. To create a query with multiple criteria you must enter more than one criteria. Some of the possibilities include: Or with one field (Type the first criteria in the Criteria area and the second criteria in the Or area), Or with two fields (Type the first criteria in the Criteria area of the first field and the second criteria in the Or area of the second field.), And with one field (In the Criteria area type the first criteria And the second criteria. For example >100 And <200.) and And with two fields (Type the first criteria in the Criteria area of the first field and the second criteria in the Criteria area of the second field).
Calculations. The basic operators are + (Add), - (Subtract), * (Multiply), / (Divide) and ^ (Exponents). To create a calculation, click the field area in the first empty column. Type the name for the field that will display the results of the calculation followed by a colon (:). Press the spacebar to leave a blank space and type an expression for the calculation you want to perform. If a field exists only one table you do not need to enter the table name. I the field exists on more than one table, type the name of the table containing the field in square brackets followed by an exclamation point (!). For example Total Price: [Quantity]*[Price] or Total Price: [Orders]![Quantity]*[Orders]![Price]. Click on the exclamation point (!) to run the query. The results of the query appear.
Summarize data. One field. Create a query that contains only the field you want to summarize. Click on the sum icon to display the row total. The Total Row appears. Click the total area field and use the drop box to select the calculations you want to perform (Sum, Ave, Min, Max, Count, stDev, Var, First, Last). Click the exclamation point (!) to run the query. Grouped records. Create a query that contains only the field you want to summarize and the field you want to group your records. Click on the sum icon to display the row total. The Total Row appears. Click the total area field and use the drop box to select the calculations you want to perform. Access will use the field that displays Group By to group the records. Click the exclamation point (!) to run the query. You can group the records using more than one field.
Reports are documents which allow you to summarize data in the database and to perform calculations on the data. To change the data in a report, you must change the data in the table used to create the report. The three views of a report are
Create (Wizard). Click Reports in the Database window. Double-click Create report by using the wizard. The Report Wizard opens. Select the tables and fields you want to use in the report. If you selected fields from more than one table, you can choose the table you want to use to group the data in the report. Click Next. To use a specific field to group the data, double-click on the field you want to use. Click Next. To sort the records, sue the drop box to select a field you want to sort on. Click on one of the sorting icons. To sort using a second field, repeat the process using a second field. To perform calculations in your report, click Summary Options. The Summary Options dialog box appears. Click the check boxes for the calculations you want to perform. Choose an option from (i) Detail and Summary (ii) Summary Only and (iii) Calculate the percentage of the total that each group represents. Click Ok. Click Next., Click the layout you want to use for the report. A sample of the selected layout appears. Click on the page orientation you want. Click Next. Type in a name for the report. Click Finish to create the report. Note that each time you open a report, Access displays the most current data in the database.
Create (AutoReport). Click Reports in the Database window. Click New. Click the type of AutoReport you want to create. Use the drop box to select the table containing the data you want to include in the report. Click OK to create the report. The report is displayed. Click on the Save File icon to name and save the report.
Use. To open a report, click on Reports in the Database window and double-click on the report you want to open. To move through the pages on a report use the First Page, Previous Page, Next Page or Last page buttons or enter the page number. To zoom out, position the mouse over the page and click to view the entire page. To zoom in, click on the area you want to magnify.
To preview before printing, click on the type of object you want to preview in the Database window and click on the Print Preview icon. Use the First Page, Previous Page, Next Page or Last page buttons to move from page to page. To change the Page Setup use the File/Page Setup menu to open the Page Setup dialog box. Some of the available options include margins, print headings, print data only and orientation. To print information, click on the type of object you want to print in the Database window and double-click the object you want to print. Use the File/Print menu to open the Print dialog box. You can choose to print (i) all of the records, (ii) specified pages or (iii) only the selected records.
| Activities
Create a database containing two tables. The first table is a list of costumers containing the fields Last Name, First Name, SSN, Age, Street Address, Zip Code, Phone Number and Annual Income. Set the primary key to be the SSN. Create a second table with the fields Item, Price and SSN. Form a relationship between the SSN fields of the two tables. The tables should use data validation, lookup tables and input masks. Format the price field to currency. Create a form for data entry. Create queries to extract information from the database. Create reports to print the data extracted from a database. |
| Table of Contents | | Previous | | Next |