IS 201 – Access Project Part Three

Question # 00555856 Posted By: Prof.Longines Updated on: 07/03/2017 02:12 AM Due on: 07/03/2017
Subject Computer Science Topic Algorithms Tutorials:
Question
Dot Image
2

The goal of this part of the project is to create a final database that includes: (1) a database structure with
related tables that are populated with data that you can use for testing; (2) forms suitable for casual
computer users to add, change, and delete data; (3) queries that can be used by forms and reports to
provide information; and (4) forms and reports to display information from tables and queries in a format
suitable for casual computer users.
Some of the requirements for this part of the project require that you have a working database and queries
developed in parts one and two of the project. If you were not able to get everything to work correctly in
Part 2 of the project, I recommend that you read the requirements for this part and fix anything in your
database that you will need. I will not provide a database with the required queries from Part 2, so
check out the requirements in this document and see what queries you need to have working correctly.
This part of the project will be evaluated both on the content and the format of the forms and reports you
create. The forms you create can be ugly, pretty, simple, complex, easy-to-use, hard-to-use, etc. but your
grade will reflect the amount of time and effort you put into creating visually appealing easy-to-use
forms and reports. Take this opportunity to learn how to incorporate the concepts of effective
information visualization that we have discussed in class into your business application.
I recommend that you choose a style (color, formatting, etc.) and stick with that style for all your forms
and reports. Feel free to use a pre-defined theme in Access. The screens and reports should include a
picture logo (find something appropriate from a free image that you find on the web), a meaningful title,
and use a consistent look-and-feel. Consistency is important - you are creating these forms and reports
for one company so all the forms and reports should have the same look-and-feel.
Upon completion upload your database named in the format LastNameFirstInitialAccessProj3.accdb to
WebCampus.
Forms
1) frm1: Create a form that allows a user to add, delete, change and view data in the database about
a customer. Make use of record navigation and operation buttons as reviewed in the lab.
Include a combo box to navigate to a specific customer. Name this form “frm1”.
2) frm2: Create a form that allows a user to add, delete, change and view data in the database about a training event. This form should allow a user to enter training events by customer, rather than simply adding individual training events. The customer data should be in the main form, while the data
for training events for that customer should be in a subform. Make use of record navigation and operation buttons you believe are appropriate and helpful for the form. Include totals in the main form
to provide some information about the training events for that customer – choose which totals you believe are relevant for your business application. Name this form “frm2”.
An idea of the overall look of this form is shown on the next, but do NOT duplicate this form because it is
not especially great - just use it as a guideline to understand the content of the form. The sample below
does not show any buttons – you should include navigation and/or operation buttons on your form.
3
Advice: Do not use the Form Wizard to build this form – it may result in a form that doesn’t work
correctly. Use the Form Design option in the Create tab, and then add the subform by adding the subform
control to the form. I recommend that you follow chapter 7, starting on the bottom of page 453, as your
reference for building this form.
3) frm3: Create a form that displays summarized information about training events within a given
training category. Base this form on the query created for qry9 on part 2 of the Access Project. You need
to make a slight modification to that query – you need to add a column for the overhead percentage for
this form. If you had problems creating that query, seek help before starting the form. The form should
display information from the query based on entry of the category description. The form should include a
combo box to select the category. Name the form “frm3”. A sample of the form is provided on the next
page of this document.
4
The challenging part of this form is that a few of the fields displayed on the form are calculated,
rather than available in the query:
Category Percent of Total Training Income: this is the percentage of the total training income for the
category out of the total training income for all categories. For example, on the form below, the category
“Microsoft Office Technical Training” has a total training income of $12,330.00, which is 17.72% of the
total training income for all categories (in my ProLearning database the total income for all training
events is $69,585.00 – but that amount is not displayed on the form).
Category Percent of Total Training Events: This is the percentage of the training category count out of
the total count of all training events. For example, the form above shows the category “Microsoft Office
Technical Training” has 10 training events, which is 33.33% of the total count of 30 events in my
ProLearning database.
Hints for these calculations: You must sum the total income and total count of events on the form in the
“form footer” section. Name these summed fields within the property sheet for the fields. Use the name of
the summed field in the “detail” section of the form to calculate the percentage. Make the format of the
calculated field a percent within the property sheet for the field. To finish off the form and make it prettier,
make the form footer not visible.
5
Reports
1) rpt1: Create a report that lists all training events in the database. The report should include the
StartDate, Customer Name, Trainer Name, TrainingHours, TrainingPrice, TrainingCost,
OverheadPercentage, OverheadExpense, ClassExpense, and EventProfit. Include an appropriate report
title, report date, and page number. Include your name in the page footer on each page. Name the report
“rpt1”. Sort the report by StartDate.
I recommend that you base the report on qry6 from part 2 of the Access Project. To refresh your memory
qry6 calculates the overhead expense by multiplying the trainer cost (TrainerHourlyRate multiplied by the
TrainingHours) by the overhead percentage. If the overhead percentage is 0, then a flat $25 is charged for
the overhead expense. For Example, if the Trainer Cost is $2,730 and the Overhead Percentage is 15.25%
then the Overhead Expense is $416.33. The overhead expense is deducted from the price paid (along with
the other class expenses) to calculate the event profit. Modify qry6 so that it shows all the events in the
database, rather than just the events in one month. Since a report is frequently paper-based, it is easier to
display more information than you can show on a screen. Sort the output in ascending order by the event
start date.
The report that you create from the query should include a final total training price, total trainer
cost, total event profit, count of training events, and average event profit.
A sample report is provided in WebCampus. The name of the link is rpt1.pdf. The report sample is not
especially pretty - it is provided only as a sample so that you can see the required contents.
2) rpt2: Create a report that lists all training events by training category. Events should then be
grouped by training type. The report should show the overhead percentage, trainer name (as one field),
customer/organization name (as one field), the event start date and the event profit – as calculated in part
2. The event profit should also be totaled for each training category with an appropriate label. The event
profit is totaled again at the end of the report for the grand total. Name this report “rpt2”.
A sample report is available for download from the course website to give you an idea of the content –
this report is not particularly pretty so do not copy it – just use it as a guide. The link is called “rpt2.pdf”.
In addition to the information shown on the sample report, you must have a page header that includes the
title of the report, current date and page number. Include your name in the page footer.
I recommend that you base the report on a query. The result table from the query that I used to create the
sample report is available on the class website. The link is called qryRpt2.pdf.
Dot Image
Tutorials for this Question
  1. Tutorial # 00553298 Posted By: Prof.Longines Posted on: 07/03/2017 02:12 AM
    Puchased By: 2
    Tutorial Preview
    The solution of IS 201 – Access Project Part Three...
    Attachments
    soln.zip (652.26 KB)

Great! We have found the solution of this question!

Whatsapp Lisa