criteria for date range

[terranauro via AccessMonster.com]

Dear all access expert,

Hi,
I have problem with my project, which is as follow :

Suppose I am a project manager and I have 10 technician.
Each tech can be assigned to every project.
Suppose there are already 4 project, therefore 4 tech was assigned. The
assignment data is
follow :
Training Start Date Training End Date Project
Start Date Project End Date

Tech 1 2/9/08 4/9/08
5/9/08 8/9/08
Tech 2 3/9/08 3/9/08
4/9/08 6/9/08
Tech 3 5/9/08 8/9/08
10/9/08 12/9/08
Tech 4 4/9/08 7/9/08
10/9/08 11/9/08

Note : Training are required before commencing project, but during training,
tech can not received
any project or training assignment

And then I got new project with following scenario :
1. Project 4, need no training, and will be held at 8/9/08
From this scenario, we know that Tech 2 and tech 4 plus tech 5-10 who are
unassigned yet
can be assigned to this new project
2. Project 5, need training, which will be held at 7/9/08 until 8/9/08 and
project will be held
from 9/9/08 until 12/9/08
From this scenario, we know that tech 2 and tech 5 until tech 10 can be
assigned to this project

Regarding above scenario, please help me how to define the range date
criteria for above problem.
This look easy, but i haven't figure it out yet.

Many thanks in advance for any help that will come.
Note :
I already learn the Allen Browne Search demo, unfortunately that demo not
cover range date criteria

 

[Lord Kelvan]

well i supose you are doing this through a form you could do a sql
statement something like

select tech
from techtable
where tech not in(
select tech
from projecttable
where newprojectstartdateonform > trainingenddate)

should give you a list of avaliable technicians

hope this helps

Regards
Kelvan