Remove from Availability "List" - ideas needed

[Kicker]

I would appreciate a couple of different ideas if I could get them. Let me give a basic description...

I have a table called People. 5 of the fields are yes/no logical fields that indicate whether the person is qualified in different areas (ie., Qual1, Qual2, Qual3, etc.) Another field is WorkGroup which indicates which days the person has off. I have another table that contains all of the "scheduled" days off such as holidays, vacations, training, etc. Therefore, I can create a query and get a list of everyone available to work on a specific date.

I have another table called WorkAssignments. This tabe contains a list of job assignments needing to be accomplished during each day. In addition, each assignment has a column indicating the specific qualification for the assignment (ie., Qual1, Qual2....). Most of the assignments are generic which everyone is qualified for.

My problem is "removing" a person from list of available people when they are assigned to a job. lets say I have 100 people. for assignments 1 - 5 I have 15 people qualified and would use the top 5. I need the 5 removed from the list and the balance available for other assignments.

I thought about creating an array with a qual element and an assigned element. If I do that, can I dynamically remove or filter, or something the array to only make certain people available?

thanks in advance for your ideas

ttfn
Kicker

 

[Kodo]

just base your lists source on a query that ONLY pulls out people with no records in the jobs table.

 

[Kicker]

Q

You mean...

Query1 will give me the list of qualified people available for the day

Query2 will use Query1, jobs.Qual, minus Assigned.Personnel to give me the balance of people available and qualified.

That has great possibilities!

Is there any "limit" some arbitrary number of times a query can be used with another query? For example: Q1 and Table1 for Q2
Q2 and Q3 for Q4
etc.

ttfn
Kicker

 

[Kodo]

You only need one query.

Roughly:

Select the names from the table where is qualified=true and jobsdates=null

 

[Kicker]

OK, I'l take it a little further.

there is a 3rd table involved. It holds ALL the assignments for a period of one year. which means, as an assignment is being made, a record is added to this table.

I would need:

All personnel in people (meeting criteria in Jobs) and not in (TimeOff or Assigned on a specific date)

I will have need for separate queries for
Assignments on a specific date
Employees off on a specific date
Employees qualified for specific jobs

Any reason I shouldn't use these queries to create the next one?

ttfn
Kicker

 

[Kodo]

Just inner join the tables on (I hope you have one) a unique ID for each person. You only need one query. It would look something like this

SELECT Emp.Employee, Table2.Qual, TableA.DateOff, Table1.assign
FROM ((Emp INNER JOIN Table1 ON Emp.ID = Table1.EmpID) INNER JOIN Table2 ON Table1.EmpID = Table2.EmpID) INNER JOIN TableA ON Table2.EmpID = TableA.EmpID
WHERE (((Table2.Qual)=[@qual]) AND ((TableA.DateOff)=[@odate]) AND ((Table1.assign)<>[@tdate]));

 

[Kicker]

KODO
that is more than I could have expected. later tonight, I will replace the table names with actuals. thank you

ttfn
Kicker

 

[Kodo]

mind you that you may have to play with the criteria a bit to make sure you get what you need with date ranges etc.
Good luck!

 

[Navyguy]

Hi Kicker

This is more of an observation then any real help to your current question...I am not sure how far along you are with your DB, but I have two items that caught my attention.

I think that perhaps you may want to have a separate table for qualifications and separate it from your tblPeople. This will allow you to add addiditonal qualifications with out changing your TblPeople structure and give you more flexibility in the future and save on DB space. (normalization)

My second point is that you have a field called WorkGroup; I am not totally sure, but that may cause problems in the future because the security features use the term workgroup. It may be a reserved word, but once again I am not totally sure.

Maybe somebody else with more experience can chime in here and provide more accurate information on my comments or let me know if they are out of line

 

[Kicker]

NavyGuy:
Thanks. Actually, I used Workgroup instead of the actual acronym because no one would have understood what I meant. As far as the second table for the quals, I thought about that and still haven't made a decision. The program will be an interum program and it is very unlikely there will be any quals added during the usefulness of the program. However, you are right, might want to plan for the unexpected.

Your comments were directly in line with what I have been looking for. thank you.

ttfn
Kicker
USNavy (1967-1974)

 

[The_Doc_Man]

Kicker,

If you have a single table that lists qualifications, you are running with a non-normalized table. It might not bother you, but here is a thought of how to do this with normalized tables...

tblPerson
fldNameInfo
fldPersID (PK)
fldOtherInfoAdInfinitum

tblQual
fldQualName
fldQualID (PK)
fldOtherInfoAdNauseam

tblPersQual
fldPersID (FK)
fldQualID (FK)

tblJobList
fldJobName
fldJobID (PK)
fldJobStrtDate
fldJobEndDate
fldOtherInfoAdAstra

tblJobPers
fldPersID (FK)
fldJobID (FK)

And make all holidays look like special jobs, then assign everyone to "work" on the holidays.

Now it is a matter of JOIN queries to find persons with the required qualifications and who do NOT have conflicting assignments. The whole thing is going to drive off of PersID in parts...

First, a query of qualified personnel for a particular Qualification.

Second, a query of folks assigned to work at the time of the job to be assigned. (This is trickier than it looks, but can be made to work with the Between ... And ... operator.)

The latter query works like this: A person is assigned (conflicted) during a particular job if any of the following is true:

1. Person has a job assignment with a start date/time BETWEEN new job's start date/time AND new job's end date/time.
2. Person has a job assignment with an end date/time BETWEEN new job's start date/time AND new job's end date/time.
3. Person has a job assignment with a start date/time BEFORE new job's start date/time and an end date/time AFTER new job's end date/time. (I.e. old job completely encloses the existing job.)
4. If the new job encloses the old job, #1 and #2 will BOTH be true, so no extra test is required.

Now, the list of available persons for the new job can be determined by finding all persons (PersID) in the first query and not in the second query. The "Not In" operator will do nicely for this. Could be done in a third query with the Not In operator as part of the WHERE clause. You could code the second query first, then make the list of qualified people (your first query) just have the "Not In" clause as part of its WHERE clause.