History for Database information

[JerryWoodstock]

where i work we have a personel database where we collect information of employees . Currently when a user leaves he is deleted from the database. The problem is that we need this info for roughly a period of two years.What is the best way to handle this situation?

what is the best method? creating a Log file and storing the 10 fields that are needed in a .txt file? or Createing a new table in the database that this info is stored in when a user is deleted? I have thought of putting a resolver table and in between the User and the Position etc. but with all the relationships it would mean that positions and other Fk's could never be deleted to keep the info in tact? what is the best solution .

This Data will need to be Queried

Thanks again
Jerry Woodstock

 

[WayneRyan]

Jerry,

If they will still appear on reports, etc. then it might be easiest
to add fields for Active, DateLeft, ReasonLeft, and so on and
leave them where they are. Periodically you can run a delete
query that will weed out the Inactive ones.

If they won't appear on your reports, then you could stick them
in a "history" table with the additional fields.

There is a lot of room for discussion on this, I'm surprised that
someone hasn't joined in.

Wayne

 

[JerryWoodstock]

I was worried that if i kept this info in side the database that it would increase the size by to much is this the case?

Im in an Organization that re orgs quite a bit changing position names and such involving deleteing positions and entering new ones i was worreid that if I put it in a table related to positions and such that things may get messed up should for example a position was deleted that related to a history table?

Im just throwing Questions around as I havent started this yet but when i do i want to be satisfied with the way im handleing it.

Records will be needed to be refrenced for reports.

Thanks again Guys

Jerry

 

[Jerry Stoner]

WayneRyan gave you a great way to do this I wouldnt worry about size unless your going to be in the millions of records. Ive got db's with 500k + and have no problems. You also get the advantage of having rehires allready in the db in addition to historical reporting Wayne mentioned.
Jerry

 

[JerryWoodstock]

How should i set this up then ?

sorry for all the questions but i really want to get this right. I will be needing to Query the info onto reports.

Should i redo me ERD ?
i currently have a member position and accomidation table(s). where the position and accomidation are related to the member table in a many member to one position/accomidation.

should i change this and insert a history table in where i relate the member/position /accomidation tables to?

How will it effect should i need to delete a position/accomidation/Person that is related in the History Table?

is there another way of doing this?
i need to keep the info of not only people that are leaving the organization but who have moved internally as well eg. when bob gets promoted from position 1 to position 2. i need to keep info that bob was in position 1 from year 1999-2001.


again sorry for all the questions its just that if i set this up propeer it will make things so much easier later on thanks again for all your help

Jerry

 

[WayneRyan]

Jerry,

This gets very involved.
But this is the time to address the
issues.

Your tables and their structure are obviously
driven by the information that you need to get
out of them.

Don't think about tables right now.

Will your database involve maintaining any
planning for the companies work efforts?
If so, are they broken up into divisions
or projects?

Will you be tracking actual work efforts
within the company and reporting on
Planned versus Actual costs?

How much do you need to maintain about
the employees? Do you need salary information
for calculations mentioned above.

To address your initial question, here's a
rough idea of four tables:

tblPositionAccomodation
PositionName
Description

tblMember
PositionName
EmpID

tblEmployee
EmpID
OtherStuff

tblEmployeeHistory
EmpID
ActionDate
ActionDescription

hth,
Wayne

 

[jeremie_ingram]

What I have personall done with a similar situation was create a single table to accomindate the neccessary data on one of the servers (outside of the original database). I inserted a DELETE button which activates an APPEND query and DELETE query, the fist moving the data to the single table, the second removing the data from the original DB.
To gather the appropriate data for reports or such, I use a UNION query and essentialy join the two sources of data for what is needed. This still keeps them seperate, but allows for getting the info when needed.
Also, even though Access has a size limitation, this can be avioded by compartmentalizing the DB into seperate DB, and then drawing the data through the use of queries. This allows for a much larger/stable environment. For one system, I have created 3 DB, one of which contains only forms, reports, and queries. The other two contain only tables related to a specific set of data.

 

[JerryWoodstock]

Thanks for answering back again Wayne This is more appreciated then you could ever imagine.

This is for a Department in Goverment. the Department is seperated into roughly 10 different sub sections eachsub section has its own positions and the position names are based on the subsection name and a numerical system combined . The Data that is being stored is mainly personal member information
ie phone number home address etc. which is already there in a table, position Info what the position does other position info and accomidation info cubicles locations/phone number for that cubicle etc.

There is No Calculations in this database.

This is/was a working databse and up until now it has worked perfectly.Unfortunatly a new boss wishes now to keep track of people currently in a establishment postion(Max of 2)/cubicle as well as the person who was previously in that establishment position/cubicle.

There is no interest in keeping more then the current person(s) in the position /accomidatoion and the person who was previously there.

Im not the best at explaining myself on these boards

Thanks again

Jerry

 

[WayneRyan]

Jerry,

Here's a rough idea.

I had to put the first two tables just to
make it general purpose.

And I tend not to believe when they say:

"Never more than two to a cubicle."
"No desire to track any more than the
last occupant"

If that was the case, you could just add
a previous resident and date moved field.

tblDepartment
DepartmentName
OtherInfo

tblSections
DepartmentName
SectionName

tblPositionAccomodation
DepartmentName
SectionName
SystemName
Description
Cubicle - J123A
Phone
EmpId - CurrentResident

tblPositionAccomodationHistory
DepartmentName
SectionName
SystemName
Description
Cubicle - J123A
Phone
EmpId - OldResident
TransDate - Date Moved Out

hth,
Wayne

 

[JerryWoodstock]

Hehe sorry i must seem as dumb as a post . im hoping i have this down pat.

So what your saying is that when a person leaves a position instead of imediatly deleting that record i would first copy the info to basically a duplicate table and then delete it from the current table.

Again im sorry, fresh out of school and it feels like i havent leared a thing in the last 3 years there

Jerry

 

[WayneRyan]

Jerry,

Basically, on your form you could have a command button
to enter a new "resident". Bring up a little "pop-up"
window and get the new "residents" name.

When they hit OK, insert a new record into the history table
based on the current record (old resident).

Then update the current record with the new resident.

Then close the window and return to your form.

Wayne

 

[jeremie_ingram]

Yes, you would basically "Achieve" the data for later retrieval, if needed. You can always query the data between the two sources to give a comprehensive view of things. When people leave our agency, we want to immediately take their information from the system in relation to PH Ext, Scheduling, and ECT; but on the same note, we need their information accessible for future use such as taxes, pension info, ECT.
By achieving the data into a separate table/DB, it is then excluded from the queries/modules within the original DB. Since it is not completely gone, it can be queried for future use and even combined (Union Query) with the current data.
Keep in mind when doing this that you SHOULD only keep the pertinent data, but you never know. Once you exclude data from the process, someone will ask for it later.

 

[Ramrod]

Unless there is an overiding reason like "because I'm the boss and I don't want to do it that way...", Why not just add a 2 new fields to your original table? A Termed(y/n) swith and TermDate. To show active records, Termed(y/n) = False in all queries,reports, etc.

After 2 years, run a delete query that removes all termed records that meet the 2year old criteria.

This seems like a more simple solution than trying to keep up with who is supposed to be in what table.

Just my 2cents....