If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Training schedule -need qry to return all employees for that month
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 |
#2
|
|||
|
|||
Training schedule -need qry to return all employees for that month
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. -- KARL DEWEY Build a little - Test a little "fossy" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|