Relationship Problems... of the database kind

[eepok]

(see sig for details on what I'm capable of before you toss code at me ;P )


Here's my current setup:

Table1(Assignments)
Instructor <related to Instructor table, pulls data from there)
Academic Year
Fallcourse# (where # = 1, 2, 3, 4), (course pulled from Courses table)
Fallcourse#comp
Fallcourse#notes
Wintercourse#
Wintercourse#comp
Wintercourse#notes
Springcourse#
Springcourse#comp
Springcourse#notes

Table2(Courses)
Coursename (unique)
Coursetype (required, elective)
Courselevel (ugrad, grad, MAS)

Table3(Instructors)
lastname
firstname
empID
rank

The purpose of the database is to assign courses to various professors and append supplementary compensation and, if necessary, notes to each course assignment. Each professor can be assigned up to 4 courses (or something to take the place of a course) per quarter.

My problem. I have created a quite a few reports with this setup including an academic year schedule, compensation reports for the year, sabatical / course release listings, etc. I am having trouble with what I am hoping to be my final report.

Requirements for the final report:
List of all courses for an academic year (filter by year)
group by level (easy)
group by type (easy)
append instructor's name to the end

Report/Relationship Problem:

I can't seem to set the relationship to allow more than one field's data into the report. I can only show courses from the [Fallcourse1] because I can't relate multiple fields from the "Assisgnments" table to one field in the "Courses" table. Effectively, that's what I want to do, but my mind's burnt and can't seem to figure it out.

Maybe you guys could help me out? Suggestions, questions - all welcomed.

 

[DevastatioN]

I believe the problem is your database doesn't seem to be normalized properly. You have FallCourse#, WinterCourse#, SpringCourse#, but I'm assuming if it's a FallCourse, it can't be a WinterCourse or a SpringCourse.

This should be only one field with a dropdown for something like "Semester" which includes "Fall", "Winter," and "Spring".

You would also only need one Notes field and Comp field instead of three.

Your assignments table could include:

Instructor
Academic Year
Course Name (Picked from list linked to Course Table)
Semester (Drop down of Fall, Spring, Winter)

(All four I believe would technically be your primary key).

 

[eepok]

I believe the problem is your database doesn't seem to be normalized properly. You have FallCourse#, WinterCourse#, SpringCourse#, but I'm assuming if it's a FallCourse, it can't be a WinterCourse or a SpringCourse.

This should be only one field with a dropdown for something like "Semester" which includes "Fall", "Winter," and "Spring".

You would also only need one Notes field and Comp field instead of three.

Your assignments table could include:

Instructor
Academic Year
Course Name (Picked from list linked to Course Table)
Semester (Drop down of Fall, Spring, Winter)

(All four I believe would technically be your primary key).

I knew that was the problem. When I first created the tables it was perfectly normalized. Then there was a problem with the most important report and the form and I couldn't find a way to make it work without rearranging the data as I did. (They want to be able to input all of a year's course assignments for a professor in one form as they input it -- so it looks like excel) Now that I have all these reports based on the current data tables, I can't find a way to make this last report work >.<.

But hey, you stray from normalization and it will come to back to bite ya, won't it?

But, just for the sake of time, could you imagine a way to make it work as is?

 

[DevastatioN]

To make it work just for the sake of time until you can normalize it properly the only way I can see doing it is this way.

Create a new table with the following fields:

Instructor
Academic Year
Course
Comp
Notes

Create a query that takes the current information:

Instructor
Academic Year
FallCourse1
Fall1Comp
Fall1notes

Then make the query write these fields to the table (Using an update query, or simply copy paste into the table). Do this Fallcourse1, Fallcourse2, Fallcourse3, Fallcourse4, Wintercourse1, Wintercourse2... and so on.

This will combine of all the fields into a table that you can create the report from, it may be time consuming but it's the only way I can think of to get you the correct report. It's technically renormalizing your data.

In the future if you don't wish to renormalize, maybe you can automate making all these queries and deleting / recreating the combined table over and over.

Hope this is of some help to you.

 

[CraigDolphin]

you stray from normalization and it will come to back to bite ya

Yes. And it has quite sharp teeth too, as you're discovering.

Here's a work around:

Build a query (Query1) that lists instructor year, FallCourse1 as MyCourse, instructor and uses an instructor year criterion.
Likewise, build another query (Query2) that lists instructor year, FallCourse2 as MyCourse, instructor

Keep going for all course types.


Create SQL union query along the lines of
Select InstructorYear, MyCourse, Instructor
From Query1
Union
Select InstructorYear, MyCourse, Instructor
From Query2
Union
Select ...ditto
From Query3
etc
etc
for all the queries you built.

Base your report on the union query.

Now, wouldn't it have been so much easier just to leave things normalized and educate your users or re-do your user interface?

Final step: Vow never to build another db for plonkers who really want to use Excel, or else educate them/yourself to create a user interface they can live with.

[Edit] Sorry Dev...missed your followup post while I was typing

 

[DevastatioN]

No problem Craig, good to know I was on the right track

It's extremely hard dealing with a non-normalized database.

 

[CraigDolphin]

It's extremely hard dealing with a non-normalized database.

Very true

(They want to be able to input all of a year's course assignments for a professor in one form as they input it -- so it looks like excel)

BTW eepok, this task would have been perfectly attainable using a normalized design.

Consider

Instructors
InstructorID (pk)
InstructorName etc

AcademicYears
AcademicYearID (pk)
AcademicYear (long int or text)

InstructorAcademicYears
InstructorAcademicYearID (pk)
InstructorID (FK)
AcademicYearID (FK)

CourseAssignments
CourseAssignmentID (pk)
InstructorAcademicYearID (FK)
Coursename (FK)
Course_comp
Course_notes

Courses
Coursename (unique PK)
Coursetype
Courselevel


You just needed to use a main form bound to the InstructorAcademicYears table (single form view) and a subform bound to the CourseAssignments table (continuous form or datasheet view) using the InstructorAcademicYearID field as the child/master field for the subform.