Selecting data from lookup field for query

[Kathy]

Hello! I am building a database that will track students' course careers in
this department. I need to gather the following information:

Up to 10 course numbers, the semester taken and the grade (I have a form -
studentinfo/subform - courseinfo for this)
Find any 4 of 10 possible courses that could be taken to qualify for a
certificate
Calculate the GPA of those 4 courses

It is possible that I have not designed the database correctly to accomplish
this. Briefly, I have separate tables for:

*course numbers with a yes/no box to tell if the course 'counts' for the
certificate
*grades with a letter grade and the corresponding point value for GPA
calculation
*student_info that holds the name, address, id#, etc of the student
*course_info that holds the 10 courses info for each student (using student
id#)
*semester codes used at the university

I have combo boxes in the form to drop down the course numbers (but not
showing the yes/no), the semester code and the letter grade (but not showing
the point value). I can query my bogus data and get all the information on
any student but I can't figure out how to sort the courses that 'count' and
then average the GPA from the grades for those courses only.

Thanks for any help!

 

[Dale Fye]

Kathy,

We really need a little more about your table structure. Give us the Table
Names, followed by a list of the pertinent field names. For example:

tbl_Courses:
Course_No, Text (PK)
Certificate, Y/N

tbl_Student_Courses_Info: PK is Student_ID, Course_No, Semester
Student_ID, Text
Course_No, Text
Semester, Text (I think you'll need this for retakes)
Grade, Text

tbl_Grades
Grade, Text (PK)
Grade_Points, Numeric (single)

What happens if the student has not taken 4 certificate courses? How about
if they have taken 6?

Dale

 

[Kathy]

Thanks, Dale! I was kind of hoping not to bog down everyone with all those
gory details.

But here we go:

tbl_Courses
CourseID (autonumber)
CourseNumber
Counts (Yes/No)

tbl_Semesters
Semester (internal code for each semester)


tbl_CourseInfo
CourseInfoID (autonumber)
EmplID (student ID number)
Course01 (lookup to tbl_Courses)
Semester01 (lookup to tbl_Semester)
Grade01 (lookup to tbl_Grades)
Course02
Semester02
Grade02
(etc. through Course10...)

tbl_Grades
GradeID (autonumber)
GradeValue (this is the letter grade)
GradeScale (this is the point value)

tbl_StudentInfo
StuInfoID (autonumber)
FirstName
MiddleName
LastName
EmplID (student ID number)
Street
City
ST
ZIP
Country
Phone1
Phone2
Email

I have a form/subform that is based on StudentInfo (master) and CourseInfo
(child) with EmplID as the linked field. The design is such that only the
letter grade and course number is visible in the dropdowns. I think I am
trying to store a point value in tbl_CourseInfo, however, and I need to know
if the course number is one that counts towards the certificate.

If the student takes more than 4 of the courses that 'count' (which has
never happened - hee!), the ideal situation would be to take the 4 best
grades. Students need to have a B (or 3.0) average to get the certificate.
Any courses that don't count need to be ignored in the calculation.

I really appreciate any help or suggestions, even if there is great change
to the basic structure. My coding chops are limited and I do tend to lean on
the wizards a little more than I should. Thanks for your time!

Kathy

 

[John Vinson]

tbl_CourseInfo
CourseInfoID (autonumber)
EmplID (student ID number)
Course01 (lookup to tbl_Courses)
Semester01 (lookup to tbl_Semester)
Grade01 (lookup to tbl_Grades)
Course02
Semester02
Grade02
(etc. through Course10...)

You need to normalize this table. It's not a table - it's a
spreadsheet!

If you have a Many (students) to Many (courses) relationship, the
proper table structure would have ONE RECORD for each course in which
the employee is enrolled. "Fields are expensive, records are cheap"!

John W. Vinson[MVP]

 

[Kathy]

THAT'S what I was stumbling over! (well, ok = probably more than that, too).
To tell the truth, this whole thing started because a predecessor had
created a massive and cumbersome and impossible to manage spreadsheet with a
bunch of this data in it. I just fell into the trap of perpetuating the
misery.

I have never used the Many to Many relationship before. Please feel free
to correct me as I fumble through what I think you are saying -

tbl_CourseInfo
CourseInfoID
EmplID
Course
Semester
Grade

Then the EmplID remains the link to tbl_StudentInfo but the CourseInfoID
becomes the player for each course the student takes? And once that occurs,
what , if any, changes need to be made to the data entry form?

Then I get into isolating the courses that 'count' and calculating the GPA
but first things first, right?

Many thanks!

 

[John Vinson]

THAT'S what I was stumbling over! (well, ok = probably more than that, too).
To tell the truth, this whole thing started because a predecessor had
created a massive and cumbersome and impossible to manage spreadsheet with a
bunch of this data in it. I just fell into the trap of perpetuating the
misery.

I have never used the Many to Many relationship before. Please feel free
to correct me as I fumble through what I think you are saying -

tbl_CourseInfo
CourseInfoID
EmplID
Course
Semester
Grade

You don't need, and shouldn't have, the Course (if that's the course
name) in this table. Again... you need *THREE* tables: employees (with
no course information); courses (with no employee information, but
with all of the relevant information about the course); and something
like this table. I wouldn't call it tbl_CourseInfo though, since it
does not contain "information about courses"; instead it contains
information about which student is enrolled in which course.

Then the EmplID remains the link to tbl_StudentInfo but the CourseInfoID
becomes the player for each course the student takes? And once that occurs,
what , if any, changes need to be made to the data entry form?

Generally, you can't easily enter data into the employee table, the
courses table, and the enrollment table all on one form. To reiterate:

tbl_Employees
EmplID <Primary Key>
LastName
FirstName
<other bio data>

tbl_CourseInfo
CourseInfoID <Primary Key>
CourseName

tbl_Enrollment
EmplID <link to tblEmployees>
CourseInfoID <link to tblCourseInfo>
Semester <when they took the course>
Grade <how well they did>

Typically you would have a Form based on one of the tables tblEmployee
(if you want to see all of the courses in which an employee is
enrolled) or on tbl_CourseInfo (if you want to see a course and all of
its students); you might in fact want both. In either case you would
have a Subform on the form, based on tblEnrollment, with textboxes or
combo boxes for Semester and Grade, and a combo box based on the
"other" side table (that is, for the Employees form you'ld have a
combo box storing the CourseInfoID but displaying the CourseName.

Then I get into isolating the courses that 'count' and calculating the GPA
but first things first, right?

For GPA, you might need a Credits field in the course table (do some
courses count more than others?); you would also need a numeric grade,
or a translation table with A+ = 4.3, A = 4, A- = 3.7 and so on. A
Totals query can readily calculate the GPA.

Many thanks!

You're welcome!

John W. Vinson[MVP]

 

[Kathy]

So far, so good! I have recreated the monster and am slowly dawn is
awakening. But I promise to be back if I stumble on the rest of the queries!

Thank you x infinity (+1)

Kathy