On Wed, 19 Jul 2006 17:08:45 GMT, "premieritguy" <u24292[ at ]uwe> wrote:
[Quoted Text] >I have created a database for our Recruiters to track potential employee
>candidates. Also, this database keeps track of current job openings for our
>company. Two of the tables in this database are called offers and job
>openings. I have a field in the JobOpenings table called "JobFulfilled"
>which is a checkbox which says if the job has been fulfilled or not. The
>offers table keeps track of offers sent out to candidates for particular job
>openings. The recruiters then change the 'Status' field, a combobox, in our
>offers table to 'Accepted' when an employee accepts. I want the
>"JobFulfilled" field to be marked 'Yes' once the Recruiter changes the
>'Status' field in Offers to 'Accepted' for that particular job. The thing is
>that there might be several offers sent out for the job and a few of them
>could be declined. No matter how many offers have been declined for a
>jobOpenings, once any offer for that job has been accepted, I want it to show
>in the JobOpenings table to show that it is no longer open.
I'd suggest that this field should NOT be stored in any table.
Instead, just use a calculated field in a Query:
JobFulfilled: EXISTS(SELECT OpeningID FROM Offers WHERE JobID =
JobOpenings.JobID AND Status = "Accepted");
This expression will be TRUE if there is any acceptance for this job,
and it will update automatically when an acceptance is entered - which
a stored yes/no field in the table will not.
John W. Vinson[MVP]
|