Date Tracking

[Guest]

Hi all!

I have created an employee relations' database to track and trend ER issues.
Each issue is currently recorded on one record, however, the HR consultants
would like to be able to track each step of the issue, i.e., counseling,
verbal warning, written warning, termination, etc. These steps are currently
in a drop-down combo box as the "resolution", and they have been just
changing the drop-down as the case has gone on. I have created reports that
use date parameters, but an issue that was reported two months ago could
still be an open issue and would not show up on a monthly report. I currently
have a report date but can't figure out how to track each part of the process
from start to finish. Any tips?

 

[Allan Murphy]

Tracking each step
I would add a new record every time there was a change in resolution
i.e. from counselling to verbal warning etc.

Issues still open
In your report query or SQL criteria add OR issues = " NOT FINALISED"
or the relevant wording

 

[John Vinson]

Hi all!

I have created an employee relations' database to track and trend ER issues.
Each issue is currently recorded on one record, however, the HR consultants
would like to be able to track each step of the issue, i.e., counseling,
verbal warning, written warning, termination, etc. These steps are currently
in a drop-down combo box as the "resolution", and they have been just
changing the drop-down as the case has gone on. I have created reports that
use date parameters, but an issue that was reported two months ago could
still be an open issue and would not show up on a monthly report. I currently
have a report date but can't figure out how to track each part of the process
from start to finish. Any tips?

I agree with Allan: you need to add a new record for each step. Just
to clarify, you should probably have THREE tables: the Employee table
related one-to-many to an Issues table, and that related one-to-many
to a Steps table. This final table would have the IssueID (linked to
the primary key of the Issues table), a StepDate field, a field
indicating the type of step (say "verbal warning"), and probably a
comments field.

If you overwrite the step each time, you thereby permanently and
irrevocably lose any record of the previous steps.

John W. Vinson[MVP]