Description
2 attachmentsSlide 1 of 2attachment_1attachment_1attachment_2attachment_2
Unformatted Attachment Preview
Grader – Instructions
Access 2016 Project
MIS 301 – Access Test
Project Description:
Flyer University (FlyerU) uses a MS Access database (that you will download) to track first-year students and to manage their
course offerings. You are asked to complete the below-mentioned tasks to help FlyerU with their operating processes. To
begin, download, i.e., Save (do NOT Open), the test database file named MIS 301_Access_Test.accdb from the course
MyITLab site, if you have not done so already. Logout of MyITLab and close the browser window. Other than at the start and
end of the test as specified, do not open any windows except ACCESS.
Steps to Perform:
Step
Points
Possible
Instructions
1
Start Access. Open the downloaded Access file named MIS 301_Access_Test.accdb.
0
2
FlyerU wants to keep data about course sections to be offered in the upcoming term.
Classrooms cannot seat more than 35 students. Create a table named SECTION to store the
fields with the properties as specified below:
11
Field Name
Data Type
Properties
RecID
Number
Description: Primary key
CourseID
Short Text
Field size of 6; Description:
Foreign key
Section
Short Text
Field size of 2
Honors
Yes/No
Default value: No
Instructor
Short Text
Field size of 20
Capacity
Number
Must only allow a value less
than 36
Set RecID as the Primary key field, save the table, and close the table.
3
Create a one-to-many relationship between the CourseID fields in the COURSE (primary) and
SECTION (related) tables. (Requirement: This relationship must be set up such that it is not
possible to enter a CourseID in the SECTION table that does not already exist in the COURSE
table.) Save the changes and close the Relationships window.
Created On: 12/10/2019
1
4
MIS 301 – Access Test
Grader – Instructions
Step
4
Access 2016 Project
Points
Possible
Instructions
Create one record in the SECTION table as follows with the Instructor field value as your
actual name:
Field Name
Value
RecID
1
CourseID
BUS201
Section
01
Honors
Yes
Instructor
Student Name
Capacity
30
5
Close the table.
5
Advisors need to review information about students and courses they have taken. Create a
form named StudentRecord to display the StudentID, Name, Major, and Balance (in that
order, from STUDENT) for each student and the CourseID, Title, Credits (in that order, from
COURSE), and Grade (from TRANSCRIPT) for all courses he or she has taken (in a subform).
The format should facilitate looking at all the information (as specified) for a student on one
screen, i.e., without tabs or multiple pages. The boxes/columns should be wide enough to
display the data. Save and close the form.
12
6
FlyerU staff often need to review class rosters. Create a form named CourseRoster to display
for each course its CourseID, Title, Credits, and Category (in that order, from COURSE) as
well as the StudentID, Name, and Major (in that order, from STUDENT) of all students in that
course (in a subform). The format should facilitate looking at all the information (as specified)
for a course on one screen, i.e., without tabs or multiple pages, although there may be scroll
bars. The boxes/columns should be wide enough to display the data. Save and close the form.
12
7
FlyerU needs a catalog of courses (suitable for printing) that shows the CourseID, Title, and
Credits (from COURSE) for courses it offers. Use the Report Wizard to create the report with
the name Catalog. The list should be grouped by the Category of the school (Arts,
Professional, and Sciences) that offers the courses and sorted by CourseID. It should also
show the total number (i.e., count) of courses within each category as well as the total count
across all categories (Hint: modify the report as necessary to count the values in the Credits
column). In the Category Footer, to the left of the count, enter the label Sum for Category. In
the Report Footer, to the left of the total count, enter the label Grand Total. Also, the report
must contain the date and page number (in N of M format) at the bottom of each page and the
preparer’s name (type Prepared By: Student) to the right of the report title on the first page
only. Save the report as Catalog and close the report.
18
Created On: 12/10/2019
2
MIS 301 – Access Test
Grader – Instructions
Access 2016 Project
Points
Possible
Step
Instructions
8
Student Services monitors the amount left on students’ declining balance cards and alerts
academic department chairs if the average balance for their majors seems low. It needs a
statement (suitable for printing) that shows the StudentID, Name, and Balance (from
STUDENT) for each student. Use the Report Wizard to create the report with the name Card
Balance. The list should be grouped by Major and sorted by StudentID. Modify the report as
necessary to show the average balance for each major as well as the overall average across
all majors. In the Major Footer, to the left of the average, enter the label Avg. In the Report
Footer, to the left of the average, enter the label Overall Average. Also, the report must
contain the date and page number (in N of M format) on the bottom of each page and the
preparer’s name (type Prepared By: Student) on the top (toward the right) of the first page in
the report. Save the report as Card Balance and close the report.
18
9
FlyerU is interested in reviewing courses offered that are worth only a small number of credits,
i.e., fewer than the typical number of 3. Create a query named Query1
• to display the Credits, CourseID, and Title fields (in that order, from COURSE)
• sorted in descending order by Credits and within that in ascending order by Title
• for all courses where Credits is less than 3.
7
Run and then save the query. Close the query.
10
The Accounting department needs to review the account balance for its majors. Do not display
the major itself in the results, since they are all ACC. Create a query named Query2a
• to display only the Name and Balance (in that order, from STUDENT)
• sorted in ascending order by Name
• for all students whose major is ACC.
7
Run and then save the query. Close the query.
11
0
12
0
13
The Provost is concerned that students are slacking off in Arts courses or those that are only 1
credit. She needs a list of such courses that students have failed. Create a query named
Query3b
• to display the Category, Title, Credits, and Grade (in that order)
• sorted in ascending order by Title
• for all cases with a Grade of F and either Category is Arts or Credits is 1.
Hint: Note that you may have the same course appear multiple times in the output.
7
Run and then save the query. Close the query.
14
Periodically the Dean of Students meets with groups of students by major. She wants to see a
list of students by any major she may specify. Create a query named Query4a
• to display the StudentID, Name, Major, and Balance fields (in that order, from STUDENT)
• sorted in ascending order by StudentID
• for all students based on a Major supplied by the Dean when the query is run; Create the
prompt as [Enter Desired Major].
7
Run (enter BIO) and then save the query. Close the query.
15
0
16
0
Created On: 12/10/2019
3
MIS 301 – Access Test
Grader – Instructions
Access 2016 Project
Points
Possible
Step
Instructions
17
The Marketing department has received a donation, which will result in $50 being added to the
card balance of each student whose major is Marketing. An estimate of the new balance for
each Marketing student is required. Create a query named Query5b
• to display the StudentID, Name, Major, and Balance (in that order)
• a calculated field named NewBalance that displays the result of adding 50 to Balance
• sorted in ascending order by StudentID
• where the Major is MKT.
(Do not be concerned about the formatting of NewBalance.)
7
Run and then save the query. Close the query.
18
For allocating resources to academic departments, FlyerU needs to calculate the sum of credit
hours taken by students in each major. Create a query named Query6a
• to display the Major and a field named TotalCredits that shows the sum of Credits
• for all students within each Major.
6
Run and then save the query. Close the query.
19
0
20
0
21
The chair of the MIS department is concerned that MIS students may be only taking
introductory courses and wants a list of other courses taken. Create a query named Query7b
• to display the StudentID (from STUDENT), Name, Major, and Title (in that order)
• sorted in ascending order by Title
• for all records where the Major is MIS and course Title field value does not begin with Intro
or some variation of Intro (e.g., Introduction, Introducing, etc. but assume that it is too tedious
to predict all the variations.)
5
Run and then save the query. Close the query.
22
The Dean needs to check if there are any students who have not attempted any courses.
Create a query named Query8a
• to display the StudentID, Name, Major, and Balance (in that order, from STUDENT)
• for all students (StudentID, from TRANSCRIPT) who have not taken any courses, i.e., have
no transcript entries.
4
Run and then save the query. Close the query.
23
24
0
At the end, make sure you do the following in sequence:
0
1. Before you close the ACCESS window, make sure that the database is still on your desktop
(saved).
2. Close the database and then close Access.
3. Launch the MyITLab application and Log-in.
4. Return to the MyITLab Assignment and complete Step 3 by Submitting/Uploading your
completed database. NOTE: This is the .accdb file and NOT the .laccdb file) Don’t forget to
submit/upload!
5. Log out of MyITLab after successfully submitting the assignment.
6. Do NOT delete the database from your desktop until the test is graded or you receive
confirmation from the instructor.
Total Points
Created On: 12/10/2019
4
130
MIS 301 – Access Test
Grader – Instructions
Created On: 12/10/2019
Access 2016 Project
5
MIS 301 – Access Test
Purchase answer to see full
attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.
