Auto populate field if there is data in another (1 Viewer)

asmaar99

New member
Local time
Today, 11:47
Joined
Nov 11, 2019
Messages
6
Hi All,

I have little to no experience with access. However, I have managed to create a small recruitment database.

I am trying to get the status of the candidate to update automatically depending on what stage they are at of the recruitment process. (this may have been answered before but hours of searching i have come up with nothing):banghead::banghead::banghead::banghead:

Below i have listed the tables within the database and what field would indicate what status they are at. The tables are in bold and the fields are in []. What the status should be is in " ".

I have the following tables:
Candidate info - This is where the [Status] field is located.

Pre-interview
[Screening date] would suggest this candidate is in "Pre-Interview" State and I have another column which says [interview] This is a yes or no field. If yes, Status would change to "interview". If no, status should change to "closed - NFA(Failed Screening)"

Interview - if in the field [interview date] has been entered the status should remain as "interview" but if another field called:

[Result] = Fail. Status should change to "Closed - NFA (failed interview)"
[Result] = Cancelled. Status Should change to "Closed - NFA (Interview Cancelled"
[Result] = Yes & [Interview Priority] = Low - Status should be "Talent Pipeline"
[Result] = Yes & [interview Priority] = Fast Track - Status should be "fast track".

Onboarding
If the field [date financial approval requested] has a date then the status should change to "In Approval"
Another field called [Approved] = Yes then state should be "Offer Extended" otherwise "Closed - NFA (Offer Declined)"
If it is approved and another field [Result of Contract offer] = Accepted then status should be "Start Date Set" if declined then status should be "Closed - NFA (Offer Declined)"

Deployment
If field [Deployment Date] has a date in it then status should be "Deployment Date Set" otherwise it should state "On Bench"
If the date entered in "deployment date set" has passed then the status should change to "Deployed".

I really hope the above has made sense. I have literally no idea how to implement this and any ideas or help will be much, much appreciated.

Please let me know if further information is required.

Many thanks in Advance!!
Asmaar
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,357
Hi Asmaar. Welcome to AWF!


Have you tried checking backwards through your data. For example, if there's a deployment date, then the status is deployed, else if contract offer is accepted, then status is start date set, else if etc.
 

asmaar99

New member
Local time
Today, 11:47
Joined
Nov 11, 2019
Messages
6
Hi Asmaar. Welcome to AWF!


Have you tried checking backwards through your data. For example, if there's a deployment date, then the status is deployed, else if contract offer is accepted, then status is start date set, else if etc.

Many thanks for your swift response. It is much appreciated :)

Your solution does make some sense to me however, I have literally no idea how i would go by implementing this. I am a complete beginner who has been given this very stressful task!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,357
Well, normally, you could do this using a query and there won't be any need for a status field in the table. Basically, the status can be calculated to make sure it is up to date.
 

asmaar99

New member
Local time
Today, 11:47
Joined
Nov 11, 2019
Messages
6
Well, normally, you could do this using a query and there won't be any need for a status field in the table. Basically, the status can be calculated to make sure it is up to date.

Aah, I don't want it as a query since this Status information needs to be on every form I have just to remind the user of this specific candidates status.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,357
Aah, I don't want it as a query since this Status information needs to be on every form I have just to remind the user of this specific candidates status.
Ah, but forms can use queries too. And if not, then calculations can be done in forms too. The point being, if the status is stored in the table, then it could be out of date.
 

asmaar99

New member
Local time
Today, 11:47
Joined
Nov 11, 2019
Messages
6
Ah, but forms can use queries too. And if not, then calculations can be done in forms too. The point being, if the status is stored in the table, then it could be out of date.

Okay, now that makes sense. if it is stored in a table it wont be updated. If it is in a query form linked to [status] it will always be in an updated state.

That sounds good and stuff but how would this query work?

I'm really sorry for being a noob :banghead:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,357
Okay, now that makes sense. if it is stored in a table it wont be updated. If it is in a query form linked to [status] it will always be in an updated state.

That sounds good and stuff but how would this query work?

I'm really sorry for being a noob :banghead:
Something like this:


SELECT ClientID, ClientName, GetStatus([ClientID]) AS Status
FROM TableName


Where GetStatus() could be a function to figure out the current/latest status for the specific client/record. And like I said, the function can walk through the conditions to check in reverse order.
 

asmaar99

New member
Local time
Today, 11:47
Joined
Nov 11, 2019
Messages
6
Something like this:


SELECT ClientID, ClientName, GetStatus([ClientID]) AS Status
FROM TableName


Where GetStatus() could be a function to figure out the current/latest status for the specific client/record. And like I said, the function can walk through the conditions to check in reverse order.

No experience in SQL or Coding. is there no built in function to do this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,357
No experience in SQL or Coding. is there no built in function to do this?
Hi. Sorry, no, there won't be a built-in function to get the latest status since your requirement is unique to your own situation. However, what we'll do is combine several built-in functions to create a custom function that's applicable to you.
 

asmaar99

New member
Local time
Today, 11:47
Joined
Nov 11, 2019
Messages
6
Hi. Sorry, no, there won't be a built-in function to get the latest status since your requirement is unique to your own situation. However, what we'll do is combine several built-in functions to create a custom function that's applicable to you.

Okay, makes sense. where do I start with this, what do I do. I need help!!!
 

Users who are viewing this thread

Top Bottom