TLuebke said:
Thanks for your time Amy this is a tough one to crack. I'll study and
mockup
the suggestions below an look forward to the form piece
OK, obviously I've given you a fairly complicated structure. So it will be
very important to your instructors to be able to correctly put the right
grade for the right assignment for the right student--in the right semester.
So I suggest this form structure:
Semester
Query based on CourseOfferings joined to Course
Module
Query based on a left join of Assignment to tblSubmit to a
query that returns all students enrolled in this CourseOffering
I think that's fairly self-explanatory until you get to the final subform,
so let me go into a bit of detail.
You probably noticed that the Assignment table does not directly reference
CourseOffering. This is kind of a problem, because if you're anything like
me, trying to put in a fully-qualified reference to a control on a nested
form is just a PIA. So what I normally do in such cases is create a global
variable that contains the value of whatever that is. To do this, create a
separate module just for global variables and code relating to them. I
usually call mine "Global"
At the top of this module, under the Option Explicit, etc., put
Public CourseOffering_ID As Integer
Then you need a function to return that variable's current value, so on the
next line:
Public Function getCOID() As Integer
getCOID = CourseOffering_ID
End Function
Now you need to be able to set that variable's value, so open the properties
sheet for the subform nested inside Semester based off of the
CourseOfferings/Course query, and go to the "Events" tab. Click on
"Current" and select "Event Procedure" and the ... on the right of that.
This should take you to the VBA code window, and you should be inside a sub
that was already created.
Inside it, type:
If Not Me.NewRecord then
CourseOffering_ID = Me.CourseOfferingID
Else
CourseOffering_ID = 0
End If
The form structure I gave you really isn't appropriate for adding new
CourseOfferings, so you should never need to go down the else path, but it
never hurts to be safe ;-). The else helps prevent Invalid Use of Null
errors.
So now let's look a bit more closely at the innermost subform. I should
explain that I've presumed that the instructor will have each assignment due
at the same time for all students, so that subform is set up to show all
students by assignment, rather than the reverse. If you noticed I said to
use a query joining two tables to a query. The reason for this is what is
called a "frustrated join." If you use a Where clause on a left or right
join, Access treats it as an inner join, and you don't get all the records
you need. You can use a subselect, but I prefer to use a separate stored
query.
To make this query, which I will call frmStudentsInOffering*, you'll need to
open the query grid, and show these tables:
tblStudentCourseOffering
tblStudent
If there is not a join line joining StudentID in tblStudentCourseOffering to
StudentID in tblStudent, then drag one of the StudentID's on top of the
other.
Select the fields in tblStudent that will be most useful in identifying that
student for grading. At a minimum, probably first and last name. You may
want to do an ascending sort on those fields as well. Test the query.
You'll see that it contains information on all students assigned to _all_
Course Offerings, which is not what we need. So, we'll narrow it down with
a Where clause. Drag the field CourseOfferingID from the list of fields
into the grid, uncheck the "show" button, and in the WHERE row, put =
getCOID(). If you test the query again, you will notice that it returns no
records, because the global CourseOffering_ID variable is not set yet.
Next, we want to make the left join query to show a record for each student
enrolled in the course offering to each assignment. You can either do this
as a separate stored query or just do it in the form's data property panel.
In the query grid, show:
tblAssign
tblSubmit
frmStudentsInOffering
Make sure there's a join line between the two AssignID fields and between
the two StudentID fields, and that the joins are set up to "show all records
in tblAssign and only the records in tblSubmit where the joined fields are
equal", "show all records in tblSubmit and only the records in
frmStudentsInOffering where the joined fields are equal".
Now, drag the fields in from the field list onto the new subform. You'll
want to right-click the StatusCode field and change it to a ComboBox. In
the format panel, set the column count to 2, the widths to 0, 1. In the
Data panel, set the data source to tblStatusCodes and the bound column to 1.
Note that this will put the ID of the status code in rather than the status
code itself, but this means that you can globally change a status code if
you need to. It's also slightly more compact than storing several letters.
If that's not what you want, use one column and use a query to set the data
source to the status code (and you probably don't need the ID in the status
codes table at all).
This should work and be updateable, but since this has been 100% from my
head, I can't guarantee it. If you have any problems, post back and we'll
try to work through them.
*A word on naming. You'll notice that I named my query with the prefix
"frm" rather than the more traditional "qry". This is because I often have
hundreds of queries in one of my databases, and if they all start with
"qry," it makes it impossible to quickly find the one I want to use/work on
in the query window. Plus, if I'm in the query window then OBVIOUSLY I am
looking at queries. Therefore, if I prefix my objects at all, I prefix
them according to how they are used, rather than what they are. Since this
is a query that is being used to drive a form, I prefix it with "frm".
Typically, in my own work, I don't prefix tables or forms at all, since
their names tend to be self-explanatory and a prefix just stands in the way
of quick navigation and results in extra typing. I'm not suggesting you
adopt this scheme, just telling you how I do it.
HTH;
Amy
