Click to edit Master text styles
Second level
Third level
Fourth level
Fifth level
Welcome to Microsoft Access Chapter Three.  Microsoft Access is part of the computer software package called Microsoft Office. Microsoft Office is a group of programs that are very helpful in keeping organized and gives professional results.  The five programs that make up Microsoft Office are Microsoft Word, Microsoft Power Point, Microsoft Access, Microsoft Excel, and Microsoft FrontPage. 
In the following presentation, we will discuss highlights of access and help you become more familiar with the software. We hope you enjoy this presentation and finish will a thorough understanding of Microsoft Access Chapter Three. Good Luck!
The are four lessons that correlate with four objectives.  The first two objectives are shown here.  The first objective is to learn about reports, types of reports, and the anatomy of a report.  The second objective will show you how to Organize data by grouping records together so that information can be sorted to create a presentable report.  You will learn more about these objectives in Lesson One and  Lesson Two.
The third objective is described in much more depth in Lesson Three. The third objective is to learn how to Use queries to select, arrange, and calculate specific data.  The fourth and final objective it to Learn about crosstab queries and the 4 types of action queries.  Once completing these four lessons following, you will have a solid understanding of what Microsoft Access consists of.
This is the first lesson, Lesson One.  In this lesson you will learn the definition of a report.  Then you will learn about all the parts that make up a report.  Then finally you will learn how to create a report using Report Wizard. Don’t forget to pay attention because at the end of this lesson is your first quiz.  Good Luck!
An important fact to learn first is the actual definition of what a report is. A Report is a printed document that displays information from a database.  There are two basic types of reports.  These reports differ in appearance.  They do not have to contain the exact information but similar records make up the two.
This is a general example of what a report looks like.
As said before there are two types of formats for a report.  These types are Columnar (vertical) report and Tabular report.  We will first discuss what a Columnar or Vertical report is.  This type of a report is known as the simplest type of report.  The Columnar report lists every field for every record in a single column.  This makes this type of report easy to prepare and simple to read.  The records in a Columnar report are in the same order as they appear in the table.
This is an example of a Columnar or Vertical Report.  This is an example of a report designed by a high school.  The high school wanted to show the colleges were the graduates attended in an organized report.  In a column lists the Name of the College, the City of the College, the State of the College, and the Number of Students that attended the college who went to this particular high school.
The second type of report is called a Tabular report.  This is a very useful report because it shows exactly what the producer wishes to show.  It shows only selected information from the records, unlike a Columnar report which shows all information.  A Tabular report also displays information in rows other than in a column.  The entire report is very concise which is useful when the producer only want to show limited information.
This is an example of a Tabular report.  All the information is listed in rows.  The producer could of elected to show only three or two fields other than showing all four.  This would of made the report more concise.
Now, We will learn and define all the parts that make up a report.  There are seven parts.  They are Report header, Report footer, Page header, Page footer, Group header, Group footer, and the Detail section.  Each of these parts add different characteristics to a report.  In the following slides, you will learn what each part entails. 
The first part of a report we are going to discuss is called the Report header.  The Report header is displayed only one time at the very beginning of the report.  It is the first section you will see in design view, which is shown in the picture.  The Report header shows information about the report like its title or date.  The red arrow is pointing to the Report header.
Next we will learn about the Report footer.  The report footer is shown here in the design view at the very bottom of the page.  The Report footer is gives a summary of the information in the entire report.  And remember, a Report footer only appears once at the end of a report.  The red arrow is pointing to the Report Footer.
Under the Report header, we will find the Page header.  The Page header gives a quick summary of the reports that follows including title and dates of publication.  The Page Header only is shown once at the beginning of the report.  The red arrow is pointing to the Page header.
The Page footer displays information like the page number of another description.  It is found right above the Report footer.  The Page footer is shown at the bottom of every page of the report.  The red arrow is pointing to the Page Footer.
Under the Report header and the Page Header, you will find the Group header.  The Group header identifies a particular group.  It appears at the beginning of the group of records.  The red arrow is pointing to the Group header.
The Group footer is found above the Report footer and the Page footer.  The Group footer is shown at the end of a group.  The Group footer includes a summary of the information provided in that particular group.  The red arrow is pointing to the Group footer.
Last but no least is the Detail Section.  The Detail Section is shown in the main body of the report and appears once for every record.
We will now discuss what Report Wizard is all about.  Firstly, Report Wizard is found in Microsoft Access.  Report Wizard helps the producer of a report step-by-step. First, the program asks the creator questions.  Then the program uses the answers from the questions to create a report. Once a report has been created, the program allows changes to be made in style.  This is a great way for a first time user to create a report.  The following slides so a brief picture of what this process looks like.  In Lesson Two, you will learn more about the actual process.
The fist step is the first screen you see after you select to create a report using Report Wizard.  The first step asks the producer of the report to select which fields they want shown on the report. (*INSTRUCTOR- Click again so that an arrow flashes across the screen so that the Second step will appear.  This step is needed for each of the following slides.) The second screen you will see is the next step. This screen asks the producer if they want to add any grouping level.
The third step of Report Wizard asks the producer what sort order do they want for the records.  Then the fourth step asks what style would you like for the format of the report.
The fifth and final step asks the producer what title they would like to name the report.  After clicking on the finished button, this is what the report looks like.
Answer: d. Report
Answer: d. Report
Answer: a. Tabular Report
Answer: a. Tabular Report
Answer: c. Report Footer, Group Header, Detail Section
Answer: c. Report Footer, Group Header, Detail Section
Answer: b. Title
Answer: b. Title
Students should have learned to enter information into a table (as learned in previous sections).  Next, they should be familiar with converting data from a query to a report.  That is, they should be able to transfer the information entered in the table into a presentable report.
 Grouping information into specific categories is important when students need to present reports.  The organization of data is usually based on the values assigned in specific fields.  When handing in a report or giving a presentation, it is best for students to not only sort, but group into categories, their information so the audience can get a clear picture of the message the student is trying to communicate.
Sorting data in a specific order is pertinent to the overall presentation of a report.  Students need to realize a proper and professional manner in which they can sort and group the information they have entered into the database.  That way, their message can be easily, and clearly, presented to the audience.
A record can be defined as an entry within a database.  As illustrated, records are simply the horizontal rows in database.  They are often grouped depending on the order of the values in a specific field.
Grouping records organizes data into a presentable format so the audience can readily sort through the various pieces of information being presented in an organized manner.  After the data is sorted, information can be used to create a report.  Grouped records within a report can also perform controlled calculations.  That is, if information is grouped accordingly to specific values in a group, that certain group of data can then be commanded to perform various calculations such as the sum, average, minimum value, maximum value, and counts the number of records for the information provided in the table.
Notice how the ungrouped report is not sorted in any specific categories.  It lacks order and is unprofessional.  The audience of this report would likely find it chaotic and not be convinced by the data being presented to them.  Also, instead of sorting through the data themselves in their heads, they may just not trouble themselves and allow the data being presented to them to remain unorganized. - In the grouped report, the data is categorized according to “Major.”  Further, within those categories, the information is listed according to salary.  Even though the report on the right, the Grouped Report, was created from the same data as the Ungrouped Report on the left, it is much more professional and presentable.  Therefore, this Grouped Report is not only simple, it is most effective at communicating the information being presented.
This very first step is simply data entry into the database.  The “Show Table” dialog box should appear when Microsoft Access is opened.  The “Tables tab” should already be selected.  Proceed to select this option. -Before keying in all the information, be sure to create a title for the columns of the database so you can sort and categorize the data.  -- After making headers for the columns, begin data entry into the database.  Information does not need to be pre-sorted in any order.  All the sorting can be done within Microsoft Access after all of the data entry is completed.
After the initial step of entering data into a table, you want to transform the table into a presentable report. - In order to do this, select “Reports” from the Objects table on the left-hand side of the window.  Next, select “Create report by using wizard.”  This is the simplest way of creating a report (See previous lesson for an overview of “Report Wizard”).
When converting data from a table into a report, you need to select which fields you would like to include on the report.  If all the data that was entered was intended to appear on the report, simply select the double arrows button below the “>” button.  That will automatically select all the fields on the left to appear in the final report. - Therefore, you can select specific fields that are relevant to the report, rather than include irrelevant data/fields to your report.  Students may leave out fields that do not concern the point that is being presented in the report.  Additional fields may confuse and distract the audience.  This step is key when determining the importance of certain fields relative in comparison to the other remaining fields.
Step 4 is the most crucial step when grouping reports.  This is when you decide how you want your report grouped into categories.  Do not randomly select any field for the report to be grouped by.  There should be a correlation between the group heading and the sorted data. - In the example, the report was selected to be grouped according to major.  That was so because the presenter of the report had the theory that there was a strong correlation between what Major a student chose in college, and their future salary.
The last and final step is deciding whether there are any other ways you would like your report to be grouped.  If a student wishes to modify the way they have selected to have their report grouped, they  may select the “<back” button and made changes accordingly. - Finally, selecting the “Finish” button completes the process of grouping records and displays the report you have created, grouped and sorted.
After selecting “Finish” in the Report Wizard creates an example/preview of your final grouped report.  Above is a screenshot of the finished product, following the data that was used throughout the presentation. In conclusion, after the information of a student’s name, gender, major, and salary.  Information was grouped according to Major to show the audience the correlations between the Major that a student selects, and the salary that they are likely to receive.
Answer: d. All of the above
Answer: d. All of the above
Answer: a. The Report Wizard
Answer: a. The Report Wizard
Answer: b. False
Answer: b. False
Answer: a. True
Answer: a. True
Answer: a. True
Answer: a. True
One of the main purposes of using Access is constructing reports by sorting the information from the database. In order to make the most out of reports, one must learn to use queries.
Once completing this lesson, one will be able to do 3 things. 1) State the purpose of a query 2) Know the components of a query. 3) Use criteria to filter out the data that one is seeking 3) Differentiate the 3 view settings for queries
A report bases its info from either tables or queries. The reports based on the tables have all the records in the table, whereas those reports based on the queries only have the records that one specifically asks for.
A query allows specific data from tables to be selected, arranged, and calculated. For example, a teacher may want to find out more information about his/her new students in a class. Therefore, he/she can use the query to select out all the male/female students out of a class and have their last names arranged in the alphabetical order while calculating their average GPA.
A query is composed of a question and an answer. Specific data can be selected out by asking a specific question that can only be answered by the specific data. The question is formed by choosing the criteria in the design grid. And the answer is shown in the dynaset, which looks like a table, but any changes to the dynaset will automatically change the data in the table.
The first type of query that will be focused on are select queries.  Explain to the class that these queries are the most common.  There main purpose is to search underlying tables to retrieve data and to place the data it a table that can be modified to better suit the users needs.
This is a screenshot of a select query.  Point out to the class how all the data has been entered into this table where it can be adjusted with just a click of the mouse.
The design grid is the box on the bottom, and it contains many components. Field List displays all the fields in existing tables. Field row allows one to select the field from the field list. Show role determines if the field will be displayed in the dynaset or not. Sort row allows data to be put into ascending and descending order. Criteria rows is where one puts in the “question” in the design grid. The answer from the design grid is obtained the in the dynaset shown on top.
This is an example of selecting the criteria in the design grid. Suppose one wants the last names of all the male students in a class in ascending order. The fields selected are “Last Name” and “Gender”. The male population is selected by putting “M” in the criteria row. The show row boxes are checked for both fields to show the results in the dynaset. The sort row for the field “Last Name” is then ordered to arrange the results in ascending order. The answer from the question (all the males) will be displayed in the dynaset with the ascending order of the last name. 
In the criteria row, relational operators >,<, >=, <=, =, and <> are all used to indicate the values in which one is seeking. For example, to find out the students with a GPA 3.5 or above, one would select the field “GPA”, the table of students, then check the box of show row, then put >=3.5. In addition, dates are indicated simply by putting in the month, date, then the year. For example, 1/1/95/ would indicate January 5th, 1995.
The criteria rows are case insensitive, which means that STUDENTS = student. In addition, values in one single row indicates that all the conditions must be satisfied in order to be displayed in the dynaset. However, values in different criteria row simply means that either condition has to be true to be displayed in the dynaset.
A query window has three views each having its own individual functions. The three views are design view, datasheet view, and SQL view.
The design view is used to modify query and is displayed by default.
The datasheet view used to display the resulting dynaset.
The SQL view, stands for Structured Query Language View, is an advanced feature which will not be explained in detail in this lesson.
Answer: d. All of the Above
Answer: d. All of the Above
Answer: b. A report based on a table has all the records.
Answer: b. A report based on a table has all the records.
Answer: a. Dynaset
Answer: a. Dynaset
Answer: b. False
Answer: b. False
Answer: d. All of the Above
Answer: d. All of the Above
Once Completing Lesson 4, Students will be able to do two things. One, create crosstab queries and know how to use them effectively. And two, they will know how the uses for the four types of action queries.
Crosstab queries are more powerful than the other types of queries that have been listed so far. They work by consolidating other Access tables and databases into a row and column format, which is much easier to read and to use to find information. There are two methods to create a Crosstab query, The query design view and the “create query wizard”. The query design view can be used to create multiple types of queries, including but not limited to crosstab queries. However, the easiest way to create a crosstab query is by using the create query wizard. This wizard asks you to choose the query you want, and then asks you what sets of data that you want to select, making it very easy to create a query specific to your needs.
The next type of query is the crosstab query.  Point out to the students how this one differs from the select query.  This is a more powerful tool because it consolidates material data from a table and presents it in row, column form.  The crosstab query uses calculations to group the data which is then places in the table where it coincides with the values on the left and top of the table.
The Query Design View can be used to create crosstab queries, as well as other queries. It is easier to use the Create Query wizard for crosstab queries, which allows you to choose the query, then asks for which sets of data to select.
This picture is of the “Query Select Screen”. This is what the screen will look like when you are selecting which type of query you want to choose. You also enter the sets of data that you want to include into this screen.
The main reason that the crosstab query wizard is so much easier to use than the query design view is that the wizard offers simple, self-explanatory prompts to the user to help easily organize data in whatever fashion they need it to be in. The query design view requires a lot more manual effort to get the data into the desired fashion. The Wizard is more of an automatic process, with a few self-explained prompts.
Another very powerful type of query besides crosstab queries are called “Action queries”. Rather than having to change multiple records one by one, and update the database each and every single time, an action query can be used to allow the user to update multiple records in the database all at once with one single operation.
There are four different types of action queries. The first one is called the “Update Query”. This type is used to change more than one record at a time in a query. Using this type of query saves the user from having to perform multiple operations to update records in a query.
The second type of action query is known as the “Append Query”. This type takes the records from one table and adds them to another table.
The third type is known as the “Delete Query”. The user designates specific data in a record or query that they want deleted, and the Delete Query deletes the information from a table based on this criteria.
The fourth and final type of Action Query is known as the “Make-Table Query”. This type uses records in an existing table to create a new table in a different format, which is specified by the user.
The update query changes the records within the table.  This is beneficial if the user needs to modify the records that have already been plugged into the table.
The append query adds records from one table to the end of another one.  This could prove to be useful if more variables arise which were not included in the first query but now have to be included.  
The delete query deletes records from a table according to specific criteria plugged into the database.  For example: this may be useful if you need to eliminate teachers who have retired from the a schools payroll.
The Make-table query makes a new table with data from another table.  This type of query is useful to ensure that you don’t lose needed information when deleting information from a query.  It’s like a backup file that you can retain information from if it is accidentally erased.
After talking about the different types of queries the presentation will focus on different aspects of a query.  To begin, there are three major ways to view the selected query including the design view, datasheet view, and the SQL view.  The design view is the default view, therefore the one we will be focusing on, and is used to create or change the query.  The slide shows a blown up image of the design button which will present the query in design mode.  Below this is an example of what the query looks like in the design view. 
The lower portion of the design view window, underneath the field list for the table, is called the design grid.  This is where specific information pertaining to the query is listed.  In essence this table is the develops the questions to be answered in the table.  The field is added to the design grid by dragging it from the field list.  The information in the design grid consists of columns and rows as displayed by the graphic on the slide.
The dynaset is the answer to the question implied by the design grid.  This area contains the data which is needed to answer the questions presented in the design grid.  A dynaset is not a table but rather a subset of a table which allows the individual to modify the records as specified in the query.  Changes in the dynaset immediately appear in the underlying table.
Selection criteria are used to narrow the number of records selected in a query.  The AND condition means that all the selected records must meet the specifies criteria.  The OR Condition means that all the selected records may satisfy any of the criteria. 
Answer: a. Create Query Wizard
Answer: a. Create Query Wizard
Answer: b. 4
Answer: b. 4
Answer: a. Deletes data from a table based on designated criteria
Answer: a.  Deletes data from a table based on designated criteria
Answer: b. Make-Table query
Answer: b. Make-Table query