Anybody??

[Rogue Programme]

I need to report on historical data that is stored on an access database. The reports are by team and by department. However if a member of staff in the interim has moved team/department how can i ensure that if i query historical data i retrieve the information as it was at that time before they moved.

Hope this makes sence.

Mike

 

[Mile-O]

The best way is to have a look at your structure.


Do you have a table for staff? A separate table for department?

The staff table needs a foreign key, linking it to the department.

Looking at historical data with the staff ID as a foreign key in that shouldn't cause problems.

Or....

...are you wanting to return the department the person was in at the time?

 

[WayneRyan]

Mike,

The only way to tell that is to examine how the database was
built:

As a simple example, consider a small database that tracked
people and chores.

The table People has a name and an hourly rate. Joe's rate
today is $20.00 per hour.

The table Chores has a task, a person and a number of hours.
Joe worked on a task (Design) two years ago for 80 hours.
He made $15 an hour back then.

If the People table rate is used, the task cost $1,600. If
they made provisions to record his rate THEN, the task would
be $1,200.

It all depends on how your database was designed. Only
research will show that.

Wayne

 

[Rogue Programme]

I'm not very good at explaining but here goes.

I have 2 main tables and 2 reference tables.

'tblmain' holds info on staff..EmployeeID is my pk. The info held is name, staff number, team name (drop down box) and department (drop down box), extension number etc..in this table i have 2 foreign keys also ..DeptID and TeamID which link to my 2 reference tables...tbldeptref and tblteamref (which create my dropdown boxes.

'tblcallskills' basically holds scores, i'm not going into detail because there are loads.
CallID is my pk for this table. This table also holds call date, call time, etc.. and various fields that hold scores which are calculated via a form using calculation fields. I also have a foreign key in this table.. 'EmployeeID' which links to EmployeeID pk in 'tblmain'.

At anytime a member of staff can change teams/depts. Therefore if i want to query overall results by dept or team for say 3 months ago but in the meantime a member of staff has moved teams/depts the information retrieved will be wrong.

I am now more confused!

Please tell me if i am talking bollox!

Mike

 

[Pat Hartman]

That is not possible with your current table structure. You would need to store Team/Dept in tblcallskills to satisfy this request. The employee's current Team/Dept would need to be stored in tblcallskills as each record is created. Therefore changes to that info in tblMain would not modify the stored values in tblcallskills.

This is similar in concept to storing the price of an item in the OrderDetails at the time the order is placed. You always want the value from the time the record was added rather than the current value.

There is nothing you can do about your existing data unless someone would be able to update it manually. If you decide to add the fields and use them on a going forward basis, just run an update query to update the tblCallskills value with that from tblMain.

 

[Rogue Programme]

Pat, cheers!!

Thats what i'll do, i'll put team and dept into tblcallskills.

Mike