#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    4
    Rep Power
    0

    Question 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
  2. #2
  3. yet another member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    262
    Rep Power
    14
    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:
    Code:
    | student_id | gone | returned |
    |------------+------+----------|
    | 154        | 1998 | 1999     |
    | 154        | 2002 | NULL     |
    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.
    Webmaster - Stefan Meier KG TABAKWAREN - Pfeifen, Premium-Zigarren, ... (_Ger)
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    4
    Rep Power
    0
    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?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    13
    Remember you cannot write it like this:
    returned != null

    But

    returned IS NOT NULL
  8. #5
  9. yet another member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    262
    Rep Power
    14
    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.
    Webmaster - Stefan Meier KG TABAKWAREN - Pfeifen, Premium-Zigarren, ... (_Ger)

IMN logo majestic logo threadwatch logo seochat tools logo