Complex Loop

[mxp346]

I am a beginner and I am trying to write a rather complicated loop. The background is... I am trying to write VB code that loops through unassigned jobs and based on the Job Type, it assigns the job to an intern that can do the Job Type.

I have a table called "JobTickets" that has one of its fields called "JobType". Some examples of the job types are Server Issues and Software Installation. Another field of the "JobTickets" table is "AssignedTo" which will have the name of the intern assigned to the job. I have another table that is called "InternSkills" that has the interns listed with the columns being each of the possible job types(Server Issues, Software Installation, etc.). In each job type column there is a yes if the intern can complete the task.

So what I want the loop to do is take the first job and loop through each intern until it finds an intern that can do that job type. Once it finds an intern that can do that job type, I would like the intern's name to be placed into a column called "AssignedTo" that is in the "JobTickets" table.

The looping in English that I came up with is...

Loop Through JobTickets Table
If AssignedTo field in JobTicket tabble is Empty Then
Take Take the JobType and loop through InternSkills till that JobType has a yes.
Then take that name of the intern and copy it into the AssignedTo field of the JobTickets table


Hopefully this makes sense and is feasible. I want to thank you in advance for any help that you provide. It is greatly appreciated.

 

[gusbrunston]

Hi, m:

Thinking out loud,[tt]

Two tables: 1) JobTickets;
2) InternSkills

"JobType" is a field in both tables.

Query1: Select all JobTickets
with AssignedTo = null.

Query2: Select all Interns.

Design main form to display Jobtickets,
single record, based on Query1.

Design sub form to display all interns,
continuous records, based on having
a "Yes" in the JobType displayed in
main form, based on Query2.

Link sub form to main form by JobType.

Main Form:
==============================================
JobType: "SoftwareInstall"
Assign to who: ______________________
----------------------------------------------
Sub form:
Joe Smith "SoftwareInstall = Yes"
Brian Jones "SoftwareInstall = Yes"
Bill Gates "SoftwareInstall = Yes"
(all other interns are not selected
because they don't link with a "Yes"
in the required JobType)
==============================================[/tt]
Come to think of it, you wouldn't need a sub form at all. You could have a list box, or combo box showing the interns with with all their skills, and simply select one from the list. You could sort the list be skills, etc.

You could input the JobType as a parameter for Query2, and base your list box on Query2 showing only the interns with that skill in their tool box. That's probably better than form/subform.

Go with one form based on Query1, and put a list box on it data source Query2. At least, that's what I'd start tinkering with. The VB coding for this should be zero or minimal.

I hope this is helpful.

Gus Brunston An old PICKer, using Access2000
[tt]Want solutions you can understand?
Post understandable questions.[/tt]

 

[MichaelRed]

before proceeding, review the rules of normalization. You SHOULD NOT have a field for each job type in the intern skills. It is complicating the issue.

Back up ten (or so) and start with a properly normalized data structure.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mxp346]

I'm going to spend some time looking over what I have and follow your suggestions. Thank you both for the replies.