Please Review Tables and Help With Query

[Frank]

I'm working on a database to track students in a Title1 program that
provides special tutoring in Reading and Math. There is a set of criteria
that determines which students get placement in the Title1 program. On a
month to month basis, I need to know who is in the program. A student can be
added or dropped at any time. A student can be added, make good progress to
where he is dropped and then if his classroom performance drops again be
added back to the program. This could actually happen a couple of times
during the school year. Here are my tables:
TblStudent
StudentID
etc

TblTitle1Student
Title1StudentID
StudentID
AddDropDate
AddDropID

TblAddDrop
AddDropID
AddDrop

1. Comments in the tables?

2. How do I get a list of the students in the Title1 program on a selected
date?

Thanks,

Frank

[Pieter Linden]

> StudentID

etc

TblTitle1Student
Title1StudentID
StudentID
AddDropDate
AddDropID

TblAddDrop
AddDropID
AddDrop

1. Comments in the tables?

2. How do I get a list of the students in the Title1 program on a selected
date?

Thanks,

Frank

This is a minor twist on your standard students-classes database.
Student---(1,M)--AddDrop--(M,1)---Section---(M,1)---Course

AddDrop(
AddDropID (if you need the record to be a parent of something else)
StudentID
AddDropDate
AddDrop (A/D) or whatever.

Find out who is in the class on a given date. Use Date math fun. Try
testing your given date for being BETWEEN a student's Add date and his
drop date. (Of course, if the Drop Date is Null, then he is still in
the class.

Also, if you can have a Roster table...
StudentID, SectionID, AddDate, DropDate,...

And then NULL drop date = still enrolled.
then just do the same date math for the rest. Kinda makes sense,
because you can't drop a class you're not attending. and you can only
(theoretically) drop that class once anyway.

[MacDermott]

To find out if a specific student is enrolled on a particular date, you
could write a function like this:
Public Function IsEnrolled(StudentID as long, WhichDate as date) As
Boolean
Dim IE as Boolean
dim LastEntry as Variant
' Look for the last entry for this student before the date in
question
LastEntry=DMax("AddDropDate","tblTitle1Student","S tudentID=" &
StudentID & _
" AND AddDropDate<#" & WhichDate & "#")
'If there is no entry for the student, he's not enrolled
if not isnull(LastEntry) then
' Look at the AddDropID for that entry, and determine whether
it's an add or a drop
LastEntry=DLookup("AddDropID","tblTitle1Student"," StudentID=" &
StudentID & _
" AND AddDropDate=#" & LastEntry & "#")
If LastEntry=adAdd then _
IE=True
end if
IsEnrolled=IE
End Function

(Note that you'll need to either hard-code whatever value you're using for
adAdd, or provide some sort of global variable and read its value out of
your tblAddDrop table before invoking this function. Reading it from the
table each time you invoke this function would be redundant.)

This would be slow code to use repeatedly in a query, because of all the
domain aggregate functions, but it's a way to approach the problem.

HTH
- Turtle

"Frank" <fr******@earthlink.net> wrote in message
news:Kk*****************@newsread2.news.atl.earthl ink.net...

I'm working on a database to track students in a Title1 program that
provides special tutoring in Reading and Math. There is a set of criteria
that determines which students get placement in the Title1 program. On a
month to month basis, I need to know who is in the program. A student can be added or dropped at any time. A student can be added, make good progress to where he is dropped and then if his classroom performance drops again be
added back to the program. This could actually happen a couple of times
during the school year. Here are my tables:
TblStudent
StudentID
etc

TblTitle1Student
Title1StudentID
StudentID
AddDropDate
AddDropID

TblAddDrop
AddDropID
AddDrop

1. Comments in the tables?

2. How do I get a list of the students in the Title1 program on a selected date?

Thanks,

Frank