Using Dates

[villan60]

Hi

I have a fair amount of experience with databases. I am currently redesigning a system in Access for tracking and billing phones. There are some things that I would enjoy some feedback on. The first is the question of dates.

Phones change their status over time, so I have a table with the PhoneID and StatusID plus Indate and OutDate. Is this the only/best way of doing this?

In addition, there are actually a number of things that change with time, eg the owner (if it is a rental unit), the price plan (in the case of a price increase), etc. At the moment, all of these variables are in the same record
so when any of them changes the record has to be recreated with the change and the date of change. This repetition of data is obviously bad design, would it be better to have each variable in it's own table with in and out dates, linked to the phone? One negative is that it will make querying a lot more complicated.

I look forward to your feedback.

Thanks very much
Dax

 

[LittleSmudge]

You don't need IN and OUT dates in the record - you just need one or the other because a Status is Valid from the StartDate to the point of the next Status StartDate.

Other than that - I'd say you are moving in the right direction.


'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[villan60]

That is a good point... I do find it useful having the outdate for 2 reasons:
1) If I need to select the applicable record for a specific date I can just select for indate < date and outdate > date.
2) It's a good way of checking the integrity of the database, because if an indate does not match the previous outdate then at least I have a good chance of working out which it should be.

Does this make sense?

 

[LittleSmudge]

No. - Explicitly for point 2) NO

It is ACTUALLY a good way of generating Data Integrity Errors in your data.

Ie. If you have the following data

DateStart    EmpStatus       DateEnd
Jan 1985     Trainee         July 1986
July 1986    Technitian      Sept 1992
Sept 1992    Manager         June 2000
Oct 2002     Chief of Staff  April 2002
April 2003   Retired

Then where is the error ?
Is Manager DateEnd at fault ?
Is Chief of Staff DateStart at fault ?
Is there a missing record ?

ALL you can tell is that you have a problem. It does NOT aid data integrity at all. ( Only a change log will do that )



'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[villan60]

You are correct, I realise that it does create an opportunity for integrity errors. Something I hope to avoid through the use of forms which will automatically fill in the additional field with the same date. You notice I did say it was a way of checking the integrity, not aiding it. Which is what you said in your last line.

ok now, I have an uneasy feeling that this is going to look like a stupid question, but I am tired so please humour me.

what query would you use to find out what the empstatus was at a specific date?

 

[LittleSmudge]

dim datTheDate As Date

datTheDate = 29/July/1994 ' So Ans = Manager from above.

&quot;SELECT TOP 1 EmpStatus &quot; _
&quot;FROM tblStatus &quot; _
&quot;WHERE DateStart < #&quot; & datTheDate & &quot;# &quot; _
&quot;ORDER BY DateStart DESC&quot;


QED.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[villan60]

Thanks, I had not thought of using TOP

I need to go away and ruminate over this.

Thanks for your help.

Dax