Which jobs are vacant?

[Ace-training]

I am trying to list from a personnel database, the jobs which are vacant.

I have a personal, employment and termination tables linked (one-to-many) so I can keep historical data of employees job details and if they change jobs, keep track of where they are. Therefore, when they change jobs, their job becomes vacant until someone else fills that post. The job is held in the Establishment No. field within the employment table which gives info on the job title and location.

When a person leaves the job - they select a reason for termination and enter a termination date.

If there is no termination - they actually click on No Termination and no date is entered.

Therefore, to get a list of vacancies, I can pull out all those with no termination date against them.

But..... if someone else jumps into their job, it doesn't cross reference to the Establishment No. So I need a way of saying that if the [Start Date within current post] is later than the latest [Termination Date] and match it with [Establishment No] - I think I should get the list of vacancies. There are about 250 jobs available. Its the historical data which confuses me.

I hope I have explained that OK - please help.

 

[ecniv]

Hi,

their job becomes vacant until someone else fills that post

Ok, so this says you want to list all the jobs (open this table first in query builder). That is assuming you have a list of jobs table (organisational chart?)

Now the other part that looks to be tricky, but I'm sure there is a good reason its set up like this...

You want to join the employees to the terminations as a sub query. I don't know how the tables are laid out but something like:

select tblEmp.[EmpID],tblEmp.[Name], tblEmp.[Start Date], tblTermination.[End Date]
From tblEmp left join tblTermination on tblEmp.EmpID[b]>[/b]tblTermination.EmpID and tblEmp.JobID=tblTermination.JobID

It won't display in the query builder due to the bit highlighted in bold.

It now depends whether you want to store that as a query and reference it normally or if you want to you can copy n paste the sql statement into the overall main one.
Then you need to link the table with the jobs to the result you just got to find the data you want.



Personally I think if its allowable, you should redesign the tables around...
Table for list of jobs, table for employees (including current status, job etc), table for history of empolyee (holds the previous jobs start and end dates for each employee).

Just a thought


Vince

 

[Ace-training]

Job Vacancies

Thanks Vince - have done it using a two queries to list all jobs vacant and all jobs taken and then run an Unmatched QUery which worked.

Thanks for your help.