Originally Posted by
nipponLIFE
ok here goes
Create an Access Database for the purpose of reporting student GPA & Average Course
Grade. This Project is actually your Access Test. Therefore, I will not answer any of
your questions as this is a test. If you did the Access homework, you have been exposed
to everything you need to know (Access related) to do this project. You also need a
student specific data sheet to accompany this project sheet.
You will get this in class when the Project is assigned. If you miss that class, it is your
responsibility to get it from me. Each student will have different data and structural
values. You must turn in your student specific data sheet with your project and of
course, your database must meet the requirements of your student specific data sheet.
Requirements and deliverables:
A) Four (4) tables and data items:
1.{Students}- StudentID, FirstName, LastName, Address1, Address2, City, State,
Zip_Code, Phone, Email, Major
(must use your StudentIDs, FirstName, LastName from your handout – you can
make up the other information. IMPORTANT- StudentID field length must be
what is specified on your sheet)
2. {Class_Master}- ClassID, ClassName, Credits
(must use your Class_Master data from your handout. IMPORTANT – ClassID field
length must be what is specified on your sheet)
3. {Class_Attend}- ClassID, StudentID, Grade, Semester, Year
(must have at least 40 records total – must have records for each
StudentID, must use at least two years (2001, 2002, 2003), must
use at least two different semesters, each StudentID must have at
least one non three credit hour course, all grades must be used)
4. {Quality_Points}- Grade, Points Build this table and enter the
following data in it: A,4; B,3; C,2; D,1; F,0.
B) The following fields are required to be lookup fields with the following data:
Class_Attend:Grade – A,B,C,D,F
Class_Attend:Semester – Fall, Spring, Summer
Class_Master:Credits – 1,2,3,4
C) Table relationships: (remember to make all key fields the same length)
Students to Class_Attend - (one to many – this requires referential integrity)
Class_Master to Class_Attend - (one to many – this requires referential integrity)
Quality_Points to Class_Attend - (one to many – this requires referential integrity)
D) Forms (Three of them)
Create Forms for entering data for the tables: Students; Class_Master; Class_Attend.
E) Queries (Two of them)
1) create a GPA query with all the data required to use in creating the
Student GPA Report.
2) create a query with all the data required to use in creating the
Average Grade Report.
F) Reports (two of them)
1) Create a report based on the GPA query above that will calculate
student’s GPA for all their coursework (a sample is provided on the
class website). This must be grouped by StudentID (with StudentID,
FirstName, and LastName appearing only once per student) and all the
courses must be listed and sorted first by year and second by semester.
Be sure your GPAs are accurate; calculate GPA by dividing total
quality points by total credits.
2) Create an Average Grade by Course Report (based on the query
you created above) . Group it by course ID, sorted by year, sorted
by semester that calculates what the average grade is by course. You
will have to design this report yourself (no sample will be provided)
and figure out how to get (calculate) the average grade per each course
in your DB. This will be hard.