I've built a database where my employer can keep track of the 131 positions he is responsible for, along with the information for past and current staff.
I created a field in the Positions table called [Currently_Filled], and the options are "Yes", "No", and "TBA" (To Be Assigned). The "No" option will probably not be used; I created it just in case my employer wants to keep historical information even if certain positions are eliminated. Otherwise, their status is either "Yes" or "TBA", which means they will show up in the appropriate query and report (Current Staff or Staffing in Progress).
In the Staff table, I created a field called [Current], where the entry can only be "Y" or "N", also to enable historical records to be kept, as each position will eventually have multiple staff who will have held them.
Both tables are linked by the Position Number. I used a Left Join (to include all Positions and only the Staff where the joined fields are equal) as my database relationship.
The problem I've just encountered, now that some of the staffers have departed and the positions have gone back to "TBA" status, is that those now-empty positions still show the past staffers' information in my query called "All Positions" (which is meant to show all 131 positions in the same place, with staffing info for filled positions and with blank staff info for positions not currently filled). Since those positions are empty, the position information should show up, but the staff information should be blank (instead of showing the previous staffer's information).
How can I make this happen without losing the historical information?
Sorry about the long-winded post; I just wanted to be as clear as possible as to the function of the database.
I created a field in the Positions table called [Currently_Filled], and the options are "Yes", "No", and "TBA" (To Be Assigned). The "No" option will probably not be used; I created it just in case my employer wants to keep historical information even if certain positions are eliminated. Otherwise, their status is either "Yes" or "TBA", which means they will show up in the appropriate query and report (Current Staff or Staffing in Progress).
In the Staff table, I created a field called [Current], where the entry can only be "Y" or "N", also to enable historical records to be kept, as each position will eventually have multiple staff who will have held them.
Both tables are linked by the Position Number. I used a Left Join (to include all Positions and only the Staff where the joined fields are equal) as my database relationship.
The problem I've just encountered, now that some of the staffers have departed and the positions have gone back to "TBA" status, is that those now-empty positions still show the past staffers' information in my query called "All Positions" (which is meant to show all 131 positions in the same place, with staffing info for filled positions and with blank staff info for positions not currently filled). Since those positions are empty, the position information should show up, but the staff information should be blank (instead of showing the previous staffer's information).
How can I make this happen without losing the historical information?
Sorry about the long-winded post; I just wanted to be as clear as possible as to the function of the database.
