Share via

Which way is better to track something's current status

Anonymous
2025-06-19T09:01:05+00:00

This goes for multiple things, but I'll use my current example.

I have a table for employees. I have a table for jobs in my company. I have a table that tracks when an employee is put into that job, and when they end that job (either having been terminated, or transferred to another job) with foreign keys linking to tblEmployees and tblJobs.

Is there a right/wrong, or better/worse, way to track their current status? I have two options I'm seeing right now. A fk field JobID in tblEmployees that is their current job. Or looking for whatever job they have on tblJobTransfers without an end date.

I can see a drawback to the latter option in that if I'm not careful and leave it possible to intentionally or accidentally change a person's job without putting in an end date they could have multiple "current jobs" by that metric.

I also think that if I'm not careful the fk in tblEmployees could miss being updated, and there being a conflict there between their status in the tblJobTransfers and tblEmployees.

So, again, right/wrong or better/worse? Is there one, or is it just a matter of preference? I am leaning towards the end date one because I feel like it would be easier to catch a person having two current jobs than it would be catching the foreign key not having been updated to their new job. A query showing people with two empty end dates would be simple. I can't think of a basic query that would just somehow know that their JobID should be 15 and not 26 now.

Microsoft 365 and Office | Access | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Accepted answer
  1. George Hepworth 21,805 Reputation points Volunteer Moderator
    2025-06-19T11:11:37+00:00

    You've already pointed out the difficulty in maintaining two different methods of tracking job status. That should tell you what you probably need to do.

    You have also pointed out which method is more reliable in controlling how people are assigned to jobs. That should also tell you what you probably need to do.

    Go with your own instinct here. Pick the method that has the lower risk of creating inconsistent data and a higher chance of being maintainable.

    Perhaps, to help ensure a person doesn't end up with two different jobs with no end date, you can focus on data validation that won't allow more than one job FK for an employee with no EndDate specified. A basic query, as you mention, could be run before you add a new job record to see if that employee is currently in another job without the EndDate for it, and disallow the new record to be saved until you have dealt with the old one.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-06-19T11:33:01+00:00

    You've already pointed out the difficulty in maintaining two different methods of tracking job status. That should tell you what you probably need to do.

    You have also pointed out which method is more reliable in controlling how people are assigned to jobs. That should also tell you what you probably need to do.

    Go with your own instinct here. Pick the method that has the lower risk of creating inconsistent data and a higher chance of being maintainable.

    Perhaps, to help ensure a person doesn't end up with two different jobs with no end date, you can focus on data validation that won't allow more than one job FK for an employee with no EndDate specified. A basic query, as you mention, could be run before you add a new job record to see if that employee is currently in another job without the EndDate for it, and disallow the new record to be saved until you have dealt with the old one.

    Initially, I didn't have anything tracking when someone was in a job. Just what their current job was. So I had the foreign key in my Employee table. But then it came up tracking their history, which means dates. So I created the JobChange table.

    But before getting rid of the current job foreign key I wanted to make sure there wasn't some good reason to keep it.

    Using the JobChange table with no EndDate feels better to me. As far as trying to keep from having two "current" jobs, I have some VBA code for when a person is assigned a new job to go back and put the StartDate from the new job into the EndDate of the old job.

    0 comments
  2. Anonymous
    2025-06-19T12:06:38+00:00

    The best way to prevent an employee being recorded as currently holding two jobs would be to apply a CHECK CONSTRAINT to the table which models the many-to-many relationship type between employees and jobs.  You'll find examples of the use of CHECK CONSTRAINTs in RangeValidation.zip in my Dropbox public databases folder at:

    https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

    This little demo file illustrates two CHECK CONSTRAINTs, one for preventing two ranges from overlapping, the other to prevent gaps between ranges.  The first would apply to your situation, the latter might or might not.  However, to prevent more than one incomplete range, if we make an amended copy of the Ranges table so that it allows NULLs in the UpperVlaue column, we can prevent more that one row being inserted with a NULL UpperValue with the following CHECK CONSTRAINT:

    ALTER TABLE RangesUnconstrained ADD CONSTRAINT SingleIncomplete CHECK((SELECT COUNT(*) FROM RangesUnconstrained WHERE UpperValue IS NULL) <=1)

    Unlike other DDL statements adding a CHECK CONSTRAINT cannot be executed in the SQL window.  It has to be done in code like this:

    CurrentProject.Connection.Execute "ALTER TABLE RangesUnconstrained ADD CONSTRAINT SingleIncomplete CHECK((SELECT COUNT(*) FROM RangesUnconstrained WHERE UpperValue IS NULL) <=1)"

    This can be done in the immediate window.  To drop the constraint the code would be:

    CurrentProject.Connection.Execute "ALTER TABLE RangesUnconstrained DROP CONSTRAINT SingleIncomplete"

    In my case the constraint allows only one incomplete row in the Ranges table, whereas in yours you want to allow one per employee.  To do this the DDL statement would be slightly different.  In my case:

    CurrentProject.Connection.Execute "ALTER TABLE RangesUnconstrained ADD CONSTRAINT SingleIncomplete CHECK((SELECT LowerValue FROM RangesUnconstrained WHERE UpperValue IS NULL GROUP BY LowerValue HAVING COUNT(*) >1) IS NULL)"

    In your case the statement would be GROUPed BY EmployeeID.

    0 comments