‹header›
‹date/time›
Click to edit Master text styles
Second level
Third level
Fourth level
Fifth level
‹footer›
‹#›
Microsoft Access is the fourth major application in the Microsoft Office.  It is used to create and manage a database so you can add, edit, and delete records, run reports and queries, filter records, and allows for the use of multiple tables.  Many businesses use Access because it is a great database to store many different records and is very easy to use once you learn it.
Since we are dealing with computers, you must know the language of information systems.  Therefore, here is some vocabulary you should be familiar with when you are using Microsoft Access.  A field is a basic fact or data element and an example is a book title or a telephone number.  A record is a set of fields.  A table is a set of records, and a database consists of one or more tables.
This is a quick view of the database window.  It displays various objects just as tables, queries, forms, reports, pages, macros, and modules.  It also shows you the options of creating tables in design view, creating tables by using wizard, and creating tables by entering data.
It is also necessary to know the following descriptions of these objects.  A table stores data about a person, place, or thing and is the basic element of any database.  A table appears to be columnar in appearance, with each record in a separate row of the table and each field in a separate column.  A form provides a convenient way to enter, display and/or print the data in a table.  A query answers questions about the database.  A report presents the data in a table or query in an attractive fashion on the printed page.  A page is an HTML document that can be posted to the Web and can be viewed in a web browser.
The final two objects used are macros and modules.  A macro consists of commands that are executed automatically one after another to automate the performance of any repetitive task.  A module provides an even greater degree of automation through programming in Virtual Basic.
There are two ways to view a table in Microsoft Access.  The first way is to use the design view.  The design view is used to design the table initially, to specify the fields, and to modify the table definition. 
This is just a quick view of the design view.  You can see the different rows and columns.
The datasheet view is the other way to view a table in Access.  With the database view, you can add, edit, or delete records.  The first row in the table contains the field names and each additional row contains records (the data).  Each column represents a field (one fact about the data). 
This is a view of the datasheet view.  You can see the first row is made up of the field names.  There are also some symbols which are explained in the next slide. 
You should understand what each of the symbols mean.  A triangle icon means that the record has been saved to the disk.  A pencil means that you are currently working on the record but the record has not been saved.  An asterisk appears next to the blank record at the end of every table.  An insertion point is a flashing vertical bar which appears at the point where text is being entered.  The primary key is a field or combination of fields that is unique for very record in the table. 
Let’s start with the very basics of Microsoft Access.  To open Access, click start in the lower left hand corner of the screen.  Move the cursor over the programs and select Access.  A screen will prompt the user to start a blank database or open an existing file.  Click the circle next to “open an existing file” and click the file name you wish to open.  Click OK to open this database.
You will need to add a new record when you are entering data into your table.  You add a new record by pulling down the insert menu and clicking “new record”.  The new record will then appear at the bottom of the datasheet.
When you want to exit Microsoft Access, the first thing you do is close all of the tables and databases you are working on.  Then pull down the file menu and click close.  Answer yes to save any changes.  Then pull down the file menu again and click exit.  You will now be out of the Microsoft Access Program.
The Microsoft Access database can contain as many as seven different types of objects.  In this lesson, the student will be familiarized with three of these objects, forms, queries, and reports.  The student will be:
1. Introduced to forms, queries, and reports
2. Taught how to add, edit, and delete a record
3. Able to demonstrate data validation
Forms provide interfaces that are easier to understand and use than tables.  The status bar at the bottom of the form indicates which record you are currently viewing.
A query consists of a question and an answer.  The results of a query resemble those in a table with the difference being that the query contain selected records and the selected fields for those records.  Thus, a query may list the records in a different order than does a table.
A report presents the records of a table or query in a professional output.  Because they are in a more presentable fashion, they are preferable to printing tables or queries.
The Find command is very useful when attempting to find a particular record within a large quantity of records.  To use the find command, first open a desired table.  Click the field in which you wish to search within.  This will highlight all the records in the column.  Pull down the Edit menu and click Find.
In the Find What text box, enter a phrase that describes the record you are looking for.  You can narrow down the results by choosing the field to look within.  Clicking the Find Next command, will prompt Access to find this particular record within all the records. 
As with all the other Microsoft Office programs, Access provides many shortcut buttons on its toolbar.  These convenient buttons increase work speed and thus heighten productivity and efficiency.  An example of these shortcut buttons is the find button.  The fastest way to find a record is to click the shortcut find button located on the Microsoft Access toolbar represented by a pair of binoculars.  Clicking this button will cause the Find dialog box to pop up without having to access the command from the edit menu. 
The replace command incorporates the find command and allows you to not only to locate specific records but also to alter them.  It is very useful when it is necessary to make a change to a large quantity of identical records.  To demonstrate this, click on a field to highlight its records.  Click edit and click Find.  Click on the Replace tab.  Type in a phrase next to the Find what text box and the phrase you want to replace it with in the Replace with text box.  By clicking OK, all the words in the Find what text box will be changed to the ones in the Replace with text box. 
Mistakes arise and can be reversed by using the Undo command.  To use this function, pull down the edit menu and click on undo typing.  This will allow you to undo the last command.  While the Undo command is common to all Microsoft Office applications, Access enables the undo of only the most recent command.  The fastest way to access the undo command is to click the shortcut undo button also located on the Microsoft Access toolbar.
The delete command allows the user to remove any unwanted records in a particular database.  To use this function, click on any record to select it, the record should be highlighted.  Pull down the edit menu by clicking edit.  Click on select record to selected the entire record.  Press the delete key on the keyboard to delete this record.  A prompt similar to the one shown above will appear confirming the deletion of the record.  Keep in mind that Microsoft Access only allows the most recent events to be reversed by using the undo command. 
Data validation is a crucial part of any system. It anticipates errors that the user may make and rejects those errors prior to accepting data.  This can be demonstrated by typing a non-numerical value into a record that requires a numerical entry (i.e. zip code, copyright date).  Microsoft Access will detect this as an invalid value and a dialog box will appear indicating this. 
Reports are used to put information from a table into a presentable fashion.  Creating reports is made fairly simple with the report wizard that comes with Microsoft Access.  Click the reports button in the database window.  Click new report and select the report wizard.  Add all the fields from the table by clicking the double arrow button.  Click finish and you will have made a report in Microsoft Access.
When all else fails, Microsoft Access provides its own interactive Office Assistant.  To access the assistant, click on the help menu and click on show the office assistant.  Click on the assistant represented by a paper clip.  From this point, the user can select a topic from the menu or type in a question to be answered.
Microsoft Access, as with many other Microsoft programs, is suitable for both slow workers and those who have to work in a hurry.  When there is no time to make a report, luckily there is an easier and faster way to make obtain information from a database.  In this chapter, the student will be familiarized with filters and sorting and learn how these concepts apply to databases. 
Filters and sorts are most useful in organizing large groups of data.  A filter simply “filters out” the records that the user does not wish to display and shows only those specified by the user.  A sort works in conjunction with a filter and sorts the records from the filter in a given sequence (I.e. alphabetically, numerically, etc)
Filters and sorts can help college students like Deo and Coco to quickly gather information from a database.  For instance, if there were books on Deo’s book list by a particular author that he wanted to check, he could use the filter command to view only those books written by that author.  Furthermore, he can then sort these books alphabetically to make the information easier to read.
The easiest way to implement a filter is to click in any cell that contains the value of the desired criterion (i.e. any record that contains Johnson).  Then click the shortcut filter by selection button on the Access toolbar.  The result will be a database filtered so that only those records with the author Johnson will show up. 
This is an example of a filtered view of a table I which the user wishes to only see those books that are written by the author, Johnson.  The status bar on the bottom of the screen shows that this is a filtered list and that there are three records that satisfy the criteria.  Note that the book titles are displayed in alphabetical order.
An alternate and more efficient way to apply a filter is to use the filter by form command.  Clicking the filter by form button will a screen on which the user can select criteria from a drop-down menu for every field in the database.  This allows the user to apply multiple criteria simultaneously.  The most useful advantage of filtering by form is that the user can specify relationships within a given criterion (i.e. books with a copyright year greater than 2000).  Filtering by selection, on the other hand, requires that the user specify criteria equal to an existing value.  Another advantage of filtering by form is that the user can specify alternative criterion (i.e. book titles with copyright year of 2000 and book titles by author Johnson).  This is done by clicking the “or” tab located on the bottom of the screen.
The sort command allows the user to list the records within a field in a specific sequence.  To use this command, click in the field on which you want to sequence the records.  Click on either the sort ascending or sort descending button on the Access toolbar.  The sort ascending button is appropriate for names while the sort descending button is more appropriate for numeric fields. 
The sort ascending button is appropriate for fields such as last name, if you want to display the records with the smallest value listed first.
The sort descending button is appropriate for numeric fields such as salary, if you want to display the records with the highest value listed first.
By clicking the Remove Filter button, it will display the complete table at any time.
The power of Access is seen in its ability to demonstrate relationships between multiple tables.  The use of such functions becomes apparent as more and more databases are added. With each database, common areas can be related. These four points are the objectives of this section.  The student will learn what a relational database is and why it should be used.  They will also become more skilled at creating and utilizing relational databases.  Lastly, they will learn how to use the referential integrity tool.
Start from the most basic explanation of databases. The most generic database will contain only one table. This is very simple and information can be retrieved using the scroll bar or sorting functions. A relational database contains more than one table. With more tables, the amount of information is multiplied and it becomes hard to locate certain things or to view certain groups of information. The key to a relational database is to relate certain information such as names, crops, and area so that you can view all related information which pertains to each category. A finished relational database can be used to retrieve all the necessary information regarding one category using a single query.
Try and keep another computer and perform the tasks in the presentation.
Perform example queries to show how it is a hassle to do it every time.
Relational databases can help students like Deo who want to know more information about their college books. 
These three tables represent tables that are related.  The Books table is related to the Publications table by Book ID and the Books table is related to the Topic table by topic ID.
In order to find the necessary information, Deo would have to first search the books table for the title of the book “Fundamentals of Psychology” in order to obtain the Book ID.  He would use this Book ID and search for it in the Publication table to obtain the publication place.  To find the topic, Deo would again have to search the books table for the title of the book “Fundamentals of Psychology” in order to obtain the Topic ID.  He would use this Topic ID and search for it in the Topic table to obtain the topic of the book.  This relationship between the Books table and the other two tables is called one-to-many relationship.
This is an overview of how we are going to set up relations between the databases. There are three databases we are going to use as examples. The first one is called Books, the second one is called Publications, and the third one is called Topics.
We are going to draw a relation between Book ID’s which is an entry in both the Books database and the Publications database. The usefulness of such a task is that in the future, we are able to retrieve all information which relates to Book ID. For example, if we wanted to know the place of publication name, as well as the type of cover, we can do it with one query instead of two.
Now we will learn how to create relationships between tables using Access.  Start by closing all windows and starting at this basic screen.
To set up the relationship between two tables,  first click on tools and scroll down to relationships.  Right click anywhere on the screen, and select show table. Highlight books to add it and highlight publications to add it.
Now, double-click anywhere on the gray area.  The Edit Relationships dialog box will pop up.  This is the main menu for beginning to draw relationships between the selected databases.  Click Select New.
Now, we must select the two tables we are going to establish relationships for. On the left table, select Books, on the right table, select Publications. Now, this is the category which we will relate the two databases with. Lets select Book ID for both the left column and the right column.
Now, double click on the Publications table.
You can see that there is a “+” sign on the left of the first entry. Click on it.
This brings you directly to all the other entries which is related to Book ID.
From here, it is easy for you to view related data between both tables which correlate to Book ID, and also change it if you wish.
With data entry, it is often important to make sure that data is consistent. We are going to set up Referential Integrity. Referential Integrity is a function which is used to make sure that data is consistent between related databases. Go back to the relationships screen. Double click on the line between the two tables. This is the Edit Relationships menu. Click ok on Referential Integrity menu and click enforce referential integrity. An error message will be displayed if the data is inconsistent.