Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
CIO Insight
Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Speeding Shipping, Improving Compliance – Read the ScanCode Systems Case Study
  #1  
Old June 3rd, 2002, 01:56 PM
Avenestra Avenestra is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2002
Posts: 4 Avenestra User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation 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

Reply With Quote
  #2  
Old June 3rd, 2002, 11:06 PM
Atrus's Avatar
Atrus Atrus is offline
yet another member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Feb 2001
Posts: 262 Atrus User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
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)

Reply With Quote
  #3  
Old June 10th, 2002, 02:26 PM
Avenestra Avenestra is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2002
Posts: 4 Avenestra User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation 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?

Reply With Quote
  #4  
Old June 10th, 2002, 10:05 PM
MattR MattR is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2001
Location: High above the mucky-muck (Columbus, OH)
Posts: 266 MattR User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 7
Send a message via ICQ to MattR
Remember you cannot write it like this:
returned != null

But

returned IS NOT NULL

Reply With Quote
  #5  
Old June 18th, 2002, 04:29 AM
Atrus's Avatar
Atrus Atrus is offline
yet another member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Feb 2001
Posts: 262 Atrus User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
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.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > date tracking dilemma


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

 Free IT White Papers!
 
Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

 
Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

 
Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

 
Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

 
Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway