Training schedule -need qry to return all employees for that month

[fossy]

Situation: I have 2 tables: "employee info" & "employee training" linked by
"ID" filed. "employee info" has the employee personal information -all I
needed for is the name; the "employee training" contains the "training type"
(one text field) and 20 "renew training dates" (Date/Time field) including
the 1st time training.

Target: Need the a query that will return the "name" of the employee, the
“type of training" that will be next (not a problem with this 2), but only
for those employees that the training will be between now and the next month
and for all the 20 "renew training dates" (this is why: for some employees
may be the 2nd or 3rd renew-training, but for other may be the 15th or 16th).

First I created a qry for each "renew training dates" with the criteria
">=Date() And <=Date()+30", and then I did one last qry where I include all
of these single qrys, but when I try to run it the message error: "query is
too complex" popped up... any suggestions????? Ty

 

[KARL DEWEY]

You should not be using a multitude of date fields for new training
requirement but a new record and have a date field indicating when the
training was accomplished.
With a date it is easy to go to training attendance roster to find where the
employee signed in. If OSHA is looking at your training records you do not
want the employee to be able to say they did not receive the training.

A couple of more things to consider. Some training must be repeated on a
calendar basis and others taken once. Some training is mandatory and other
electable. So I suggest these fields to the tblTrainings

TrainingID PrimaryKey (=PK), Autonumber
TrainingName -
Description -
Recur - number - integer - number of months, weeks, or years to repeat
training Interval - text - W- Week, M- Month, Q- Quarter, YYYY- Year used in
DateAdd function with Recur to establish next training requirement.

tblTrainingsTaken
TrainingID FK
EmployeeID FK
DateTaken
DateSched - used to calculate next training if based on schedule plus Recur
times interval instead of when last taken.

If Recur has a 0 (zero) then the training is not required and query not
generate a new tblTrainingsTaken record.