date tracking dilemma

I am designing a database in Access 2000 to track students' disabilities, accommodations, personal aids and status (active, inactive, etc.) I have all my tables and relationships working beautifully, but for one detail. I need to track semesters and dates corresponding to their status. Statuses will change and I need to have the dates for all applicable statuses (ie when a student was active as well as the dates they were inactive or archived). So far I have entered no dates either in tables or fields so what would be the most efficient way to accomplish this? TIA

Hi!

What about a table `status_log` with columns `student_id`, `gone` and `returned`, where the first column is of whichever type your ids are and the latter two of the date type you prefer.

An example:


There are two entries for student No. 154. She/he has been gone all year 1998 and is currently away since 2002.
You can easily find out whether some one is checked in by

SELECT count (*) FROM `status_log` WHERE returned != null AND student_id = 154;

returning no row. Just as well you can easily view the entire history of a student.

Of course the date types need to be more precise than in the example if you want to map certain periods even.

I hope this helps, just a thought...


Regards,

Atrus.

I took a bit of a twist on your suggestion and created a semester table with the semester as the PK and a start and end date. I also created a history table that tracks status by emplID, start date of status and end date of status. This is tracking the information I need, but I have one more question about querying these tables. Would I also be able to write a query that would give me disabilities or accomodations sorted by the active status during dates that do not match the semester or status end and start dates? Such as, what disabilities were registered with our office with an active status during the fiscal year?

Remember you cannot write it like this:
returned != null

But

returned IS NOT NULL

Hi!

MattR, thank you! I should not be doing this without the manual in mind.

Avenestra, sorry I was gone for a few days. Now I don't understand your question, regrettably.

I conclude this is currently the table structure:

`semester`:
semester | start | end

`history`:
emplID | startofstatus | endofstatus

Where you asking if it were possible to find out any startofstatus-endofstatus pairs which don't match an arbitrary start-end pair from the first table?

Regards,

Atrus.