 | |
Recruitment database - candidate availabilty
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Recruitment database - candidate availabilty -
03-25-2009
, 07:52 AM
#2
| |||
| |||
|
|
I am working on the design of a recruitment database. I am stuck at the tricky subject of candidate availability. All contracts in this db are temporary. Two key user requirements are: 1. Show on each candidate's screen the range of dates that candidate is available to work, taking into account holidays, sickness, contracts, etc. 2. When the user is searching for a candidate to fill a vacancy that runs between DateA and DateB, the search results must show all candidates even if they are only 'partially' available. Where I am stumped is: How do I store the candidate availability/non-availability data? How do I derive the availability date ranges? (loop through a query of all the unavailable dates...and do what...?) As a starting point, I considered an 'unavailability' table that might look something like this: CandidateID FromDate ToDate Type 1 01/03/2009 014/03/2009 Holiday 1 08/04/2009 08/07/2009 On a contract etc... Using a table like this, that shows all the dates that the candidate is not available, should allow me to derive the candidate's availability, but when I started looking at all the variables that affect it (i.e the contract date changes, holiday dates change, candidate leaves the contract early, etc..) it started to look like a very complex approach. Can someone point me in the right direction as to how to manage, display and interrogate candidate availability. I am happy to code it and build the queries, I just need to methodology. Thanks, Paul |
record
CandidateID AssignDate TypeID
1 01/03/2009 2
1 01/04/2009 2
1 ...
1 01/14/2009 2
1 08/04/2009 1
1 08/05/2009 1
1 08/06/2009 1
1 08/07/2009 1
Assignment Type Table
ID Description
1 On a Contract
2 Holiday
New Assignment Table
AssignDate
01/14/2009
01/15/2009
A simple query can show you which candidates are available on one or more
days for the new assignment. With proper indexing nobody can be assigned to
more then 1 place on the same day.
#3
| |||
| |||
|
|
"Paul H" <comcraft1... (AT) googlemail (DOT) com> wrote in message news:a1544d88-0756-443e-b02e-eb902648289b (AT) w9g2000yqa (DOT) googlegroups.com... I am working on the design of a recruitment database. I am stuck at the tricky subject of candidate availability. All contracts in this db are temporary. Two key user requirements are: 1. Show on each candidate's screen the range of dates that candidate is available to work, taking into account holidays, sickness, contracts, etc. 2. When the user is searching for a candidate to fill a vacancy that runs between DateA and DateB, the search results must show all candidates even if they are only 'partially' available. Where I am stumped is: How do I store the candidate availability/non-availability data? How do I derive the availability date ranges? (loop through a query of all the unavailable dates...and do what...?) As a starting point, I considered an 'unavailability' table that might look something like this: CandidateID * FromDate * * ToDate * * * * * * Type 1 * * * * * * * * * 01/03/2009 * 014/03/2009 * * Holiday 1 * * * * * * * * * 08/04/2009 * 08/07/2009 * ** On a contract etc... Using a table like this, that shows all the dates that the candidate is not available, should allow me to derive the candidate's availability, but when I started looking at all the variables that affect it (i.e the contract date changes, holiday dates change, candidate leaves the contract early, etc..) it started to look like a very complex approach. Can someone point me in the right direction as to how to manage, display and interrogate candidate availability. I am happy to code it and build the queries, I just need to methodology. Thanks, Paul I like the 'unavailability' table but consider a structure with 1 date per record CandidateID * AssignDate * * * * * *TypeID 1 * * * * * * * * * *01/03/2009 * * * * * *2 1 * * * * * * * * * *01/04/2009 * * * * * *2 1 * * * * * * * * * * * * * * * *... 1 * * * * * * * * * *01/14/2009 * * * * * *2 1 * * * * * * * * * *08/04/2009 * * * * * *1 1 * * * * * * * * * *08/05/2009 * * * * * *1 1 * * * * * * * * * *08/06/2009 * * * * * *1 1 * * * * * * * * * *08/07/2009 * * * * * *1 Assignment Type Table ID * * * * * Description 1 * * * * * * On a Contract 2 * * * * * * Holiday New Assignment Table AssignDate 01/14/2009 01/15/2009 A simple query can show you which candidates are available on one or more days for the new assignment. With proper indexing nobody can be assigned to more then 1 place on the same day. |
Paul
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.