lookup criteria for combobox

T

terranauro

Dear all access expert

I have a problem regarding my access application.
Here are the description :

I have 3 tables, which are :
Technician which have fields : TechnicianID (PK), TechnicianName, Skill ID
(Foreign Key)
Skill table which have fields : SkillID (PK), SkillName
Project table which have fields : ProjectID, StartTime, EndTime,
TechnicianID

After that i make some queries and then form as user input (Form Project).
Form project will be the main input as my database is primarily made for
accomodating
project.
In form project, i make a combo box for selecting technician that will be
assigned
for project.
Suppose in this case for project 1 (welding project), the project manager
already select tech A. And then come another welding project, let say project
2.
There are to scenario for this :
1. project2 will start after project 1 finish. In this case
techinician A will be available, because he had already finished
project 1.
2. Project2 start before project 1 start but will end during the on-going
process of project 1 OR project 2 start after project 1 start.
In both cases, we know that techinian A would be unavailable.

My question is :
WHAT IS THE BEST WAY TO "TELL OR INSTRUCT" ACCESS SO IT WILL DISPLAY
THE RIGHT TECHNICIAN IN COMBO BOX (technician that have required
skill and not yet assigned for any project) ?

So many thanks in advance for all of you here.

Sincerely yours.


Hendra Himawan
 
J

Jeff Boyce

Hendra

Are you saying that one Technician can have only one Skill?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

terranauro

Dear Mr. Jeff Boyce

Thanks for such quick response

For the technician, they could be have at least 1 skill.

Many thanks in advance.

Sincerely yours



Hendra Himawan
 
J

Jeff Boyce

"at least one" implies "but could have more than one". If this is true,
then your table structure may need modification.

If you have Technicians and Skills, you'd also need a trelTechnicianSkill
table to hold the valid combinations.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

terranauro

Dear Mr. Jeff Boyce

Yes it's true.
For combining technician and skill, i may have make one more
table let's say "technicianDetail" which contained list of skill for each
technician.
Is it correct sir ?
If that's the case, how could i solve my original question with this new
added table.
Thank you so much

Hendra Himawan
 
J

Jeff Boyce

The new table might look something like:

trelTechnicialDetail
TechnicialDetailID
TechnicianID
SkillID
DateAcquired (?in case you are interested in when the technicial
aquired the skill?)

To find all the skills a certain Technician had acquired, use a query
joining the above table with the Technician table (via Technician ID) and
with the Skill table (via SkillID). Use criteria if you wish to narrow the
search. Include the fields (?Technician's FirstName and LastName,
SkillTitle) you want to see.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

terranauro

Dear Mr. Jeff Boyce

Thanks for your guidance sir for query looking for the date acquiring of
technician skill.
But to be honest, my real questions still not answered.

My questions is how to instruct access to find technician with specific skill
that
not yet assigned for any particular project ? I have put some scenario that
might be
happen in my first post.

Thank you so much in advance. Sorry for incovenience answering my questions

Best regards

Hendra Himawan
 
J

Jeff Boyce

Hendra

If there's a particular skill that you're looking for, you can use the query
I suggested in my previous post to find all technicians with that skill.
That would be a first step.

The next step would be to find all of THOSE technicians who were not already
engaged in a project. To do that, you'd need to have a table that stores
information about which technician is associated with which project during
which date range (assuming that you'll want to know who WAS associated with
a project in the past).

That table might look like:

trelProjectAssignment
ProjectAssignmentID (primary key)
ProjectID (foreign key, points back to a tblProject table)
TechnicianID (foreign key, points back to a tblTechnician table)
AssignedBegin (date/time field)
AssignedEnd (date/time field)

Next, to find "available" technicians, first create a query of this
trelProjectAssignment table that shows all technicians (TechnicianID) who
ARE assigned at present (AssignedBegin is less than today's date and
AssignedEnd is greater than today's date).

The last step is to find Technicians with the Requisite Skill (see first
step) who are NOT in the query of the Currently Assigned Technicians (see
previous step). You can use the Unmatched Query Wizard to help build this
query.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

terranauro via AccessMonster.com

Dear Mr. Jeff Boyce

Thanks for your helps.
I will try your suggestion and will inform you the result
Sorry for late reply due to different timezone
Thank you

Hendra
 
Top