Record History

[stevembe]

Finding it difficult.

I have a tables where there is a Job Position Number. Can anyone suggest a good way to tackle this problem:
I have a form where you can see this:

Post Number: MU071
Title: Mrs
Initials: S
Name: Smith
Start Date: 1 Jan 05
End Date: 1 Jun 05
Replacement Title: Miss
Replacement Initials: P
Replacements Name: Jones
Start Date: 1 Jun 05
End Date: 1 Dec 05

What I want to do is move the replacement into the post number once they assume the position and the replacement fields become empty until somebody else is identified.. However I want to maintain a history that Mrs Smith held the job.

 

[Remou]

Hi
Is it possible to redesign the table?

 

[stevembe]

Yes, I am only in the planning stages but cannot work out the best way to do this. The database is in very early stages, so a new design is possible.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stevembe]

Sorry, unsure as to what part of that link I am meant to be looking?

 

[Remou]

All of it, I think.
At a rough guess (and hoping someone will step in to say where I am wrong), I think you need something like

Key: Unique
PostNo: Not unique
Replacement: Key of replacement person
Former: Key of former person
Status: Archive, Current, Future or whatever.

 

[stevembe]

First off thanks for your replies. Sorry, not too hot on Access and not sure I understand what you are saying here:

Key: Unique
PostNo: Not unique
Replacement: Key of replacement person
Former: Key of former person

 

[Remou]

Ok. It wasn't great, maybe this is better. Say you have three tables

[tt]tblPeople
PersonID - Unique

tblPosts
PostID - Unique

tblPeoplePost
PersonID ) Unique, assuming that a person
PostID ) cannot have the same job twice
Status - A code to say whether this Post / Person
is the current holder of the post, will
be taking the post in the future, or is a
record of a former holder of this post.[/tt]