Help finding all of the correct records in a query

[Jason M Canady]

Hi, It must be getting late... I have worked on this query for several days
and have yet to come up with a viable way to make it work, so I thought I
might see if I am trying the impossible...

I have three tables, EMPLOYEES, REQTRAINING, TRAINING

Employees table stores info about the employees (IE Name, Departments,
etc...)
ReqTraining Stores what training is required for each employee and the
interval for which retraining is required
Training stores all of the times an employee has had a particular training.

I would like to retrieve all of the training types that are identified in
the required training table that are either overdue or do not exist in the
training table (IE the employee has not had the required training yet.). I
feel like I am very close to the answer, but cannot see the forest through
the trees.

Any assistance would be appreciated.
Thanks,
Jasonm

 

[Allen Browne]

Use a subquery to get the most recent training date.

1. Create a query into your Employees and ReqTraining tables.

2. Type something like this into the Field row of this query:

LastTrained: ( SELECT Max([TrainingDate]) AS LastTrained
FROM Training
WHERE (Training.EmployeeID = ReqTraining.EmployeeID)
AND (Training.CourseID = ReqTraining.CourseID) )

3. Once you get that working, you can calculate the date the employee's next
training is due. For example if ReqTraining has fields named Freqency
(number) and PeriodType (values like "d", "m", or "yyyy" for days, months,
or years), use:

NextDue: DateAdd([PeriodType], [Frequency], ( SELECT Max([TrainingDate]) AS
LastTrained FROM Training WHERE (Training.EmployeeID =
ReqTraining.EmployeeID) AND (Training.CourseID = ReqTraining.CourseID) ))

4. If desired, use Nz() to specify a default due date if the person had
never had the training. It is also a good idea to wrap the calculation in
CDate() or CVDate() so Access knows it is a Date field and does not treat it
as text. Result:

NextDue: CDate(Nz(DateAdd([Frequency], [PeriodType], ( SELECT
Max([TrainingDate]) AS LastTrained FROM Training WHERE (Training.EmployeeID
= ReqTraining.EmployeeID) AND (Training.CourseID = ReqTraining.CourseID) )),
#1/1/2000#))

 

[Jason M Canady]

I think that I have not made my dilema quite clear enough. I thank you for
your input though it is most helpful. What I really need to do is also
identify training that is in the required list that does not exist in the
training table for a given employee.

I have gotten as far as you have gotten me in several different methods,
though your method is cleaner and a bit simpler that what I have
accomplished. But how do I identify those last few records that do not exist
in the training table?

Again, any assistance would be appreciated.
Jason

Use a subquery to get the most recent training date.

1. Create a query into your Employees and ReqTraining tables.

2. Type something like this into the Field row of this query:

LastTrained: ( SELECT Max([TrainingDate]) AS LastTrained
FROM Training
WHERE (Training.EmployeeID = ReqTraining.EmployeeID)
AND (Training.CourseID = ReqTraining.CourseID) )

3. Once you get that working, you can calculate the date the employee's next
training is due. For example if ReqTraining has fields named Freqency
(number) and PeriodType (values like "d", "m", or "yyyy" for days, months,
or years), use:

NextDue: DateAdd([PeriodType], [Frequency], ( SELECT Max([TrainingDate]) AS
LastTrained FROM Training WHERE (Training.EmployeeID =
ReqTraining.EmployeeID) AND (Training.CourseID = ReqTraining.CourseID) ))

4. If desired, use Nz() to specify a default due date if the person had
never had the training. It is also a good idea to wrap the calculation in
CDate() or CVDate() so Access knows it is a Date field and does not treat it
as text. Result:

NextDue: CDate(Nz(DateAdd([Frequency], [PeriodType], ( SELECT
Max([TrainingDate]) AS LastTrained FROM Training WHERE (Training.EmployeeID
= ReqTraining.EmployeeID) AND (Training.CourseID = ReqTraining.CourseID) )),
#1/1/2000#))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, It must be getting late... I have worked on this query for several days
and have yet to come up with a viable way to make it work, so I thought I
might see if I am trying the impossible...

I have three tables, EMPLOYEES, REQTRAINING, TRAINING

Employees table stores info about the employees (IE Name, Departments,
etc...)
ReqTraining Stores what training is required for each employee and the
interval for which retraining is required
Training stores all of the times an employee has had a particular training.

I would like to retrieve all of the training types that are identified in
the required training table that are either overdue or do not exist in the
training table (IE the employee has not had the required training yet.). I
feel like I am very close to the answer, but cannot see the forest through
the trees.

Any assistance would be appreciated.
Thanks,
Jasonm

 

[Allen Browne]

Hi Jason

Presumably you also have a table of the courses offered. Both ReqTraining
(what an employee needs) and Training (actual training undertaken) have
foreign keys to Courses and Employees, so the structure is:
- Courses table: one record for each kind of training offered.
- Employees table: one record for each employee.
- ReqTraining table: one record for each combination of Employee and Course
that the employee needs.
- Training table: a record for each combination of Employee and Course
each time the employee undertakes that training.

If so, then the query containing Employees and ReqTraining does return a
record for every kind of training every employee needs. (The Training table
is not in this query.) The subquery gives the last date the employee
received that training. If they have not received that training previously,
then the LastTrained field will be Null.

The fact that the record is there and the field is Null should meet your
requirements? Perhaps you have added criteria under this field? If so,
remember to include "Is Null" if you want those that have not received the
training - something like this:
Is Null Or < Date()

Please post back if I have not understood what you are saying.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think that I have not made my dilema quite clear enough. I thank you for
your input though it is most helpful. What I really need to do is also
identify training that is in the required list that does not exist in the
training table for a given employee.

I have gotten as far as you have gotten me in several different methods,
though your method is cleaner and a bit simpler that what I have
accomplished. But how do I identify those last few records that do not exist
in the training table?

Again, any assistance would be appreciated.
Jason

Use a subquery to get the most recent training date.

1. Create a query into your Employees and ReqTraining tables.

2. Type something like this into the Field row of this query:

LastTrained: ( SELECT Max([TrainingDate]) AS LastTrained
FROM Training
WHERE (Training.EmployeeID = ReqTraining.EmployeeID)
AND (Training.CourseID = ReqTraining.CourseID) )

3. Once you get that working, you can calculate the date the employee's next
training is due. For example if ReqTraining has fields named Freqency
(number) and PeriodType (values like "d", "m", or "yyyy" for days, months,
or years), use:

NextDue: DateAdd([PeriodType], [Frequency], ( SELECT Max([TrainingDate]) AS
LastTrained FROM Training WHERE (Training.EmployeeID =
ReqTraining.EmployeeID) AND (Training.CourseID = ReqTraining.CourseID) ))

4. If desired, use Nz() to specify a default due date if the person had
never had the training. It is also a good idea to wrap the calculation in
CDate() or CVDate() so Access knows it is a Date field and does not

treat

it
as text. Result:

NextDue: CDate(Nz(DateAdd([Frequency], [PeriodType], ( SELECT
Max([TrainingDate]) AS LastTrained FROM Training WHERE (Training.EmployeeID
= ReqTraining.EmployeeID) AND (Training.CourseID = ReqTraining.CourseID) )),
#1/1/2000#))

Hi, It must be getting late... I have worked on this query for several days
and have yet to come up with a viable way to make it work, so I

thought

I

yet.).

I

 

[Jason M Canady]

Yes you are right, that should meet my needs, I think that I must not be
setting up the query correctly. I will try and work on it a bit more
tomorrow.

Thank you very much for your assistance again, I am sure that I will get it
working now!

Jason

Hi Jason

Presumably you also have a table of the courses offered. Both ReqTraining
(what an employee needs) and Training (actual training undertaken) have
foreign keys to Courses and Employees, so the structure is:
- Courses table: one record for each kind of training offered.
- Employees table: one record for each employee.
- ReqTraining table: one record for each combination of Employee and Course
that the employee needs.
- Training table: a record for each combination of Employee and Course
each time the employee undertakes that training.

If so, then the query containing Employees and ReqTraining does return a
record for every kind of training every employee needs. (The Training table
is not in this query.) The subquery gives the last date the employee
received that training. If they have not received that training previously,
then the LastTrained field will be Null.

The fact that the record is there and the field is Null should meet your
requirements? Perhaps you have added criteria under this field? If so,
remember to include "Is Null" if you want those that have not received the
training - something like this:
Is Null Or < Date()

Please post back if I have not understood what you are saying.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think that I have not made my dilema quite clear enough. I thank you for
your input though it is most helpful. What I really need to do is also
identify training that is in the required list that does not exist in the
training table for a given employee.

I have gotten as far as you have gotten me in several different methods,
though your method is cleaner and a bit simpler that what I have
accomplished. But how do I identify those last few records that do not exist
in the training table?

Again, any assistance would be appreciated.
Jason

Use a subquery to get the most recent training date.

1. Create a query into your Employees and ReqTraining tables.

2. Type something like this into the Field row of this query:

LastTrained: ( SELECT Max([TrainingDate]) AS LastTrained
FROM Training
WHERE (Training.EmployeeID = ReqTraining.EmployeeID)
AND (Training.CourseID = ReqTraining.CourseID) )

3. Once you get that working, you can calculate the date the

employee's
next

training is due. For example if ReqTraining has fields named Freqency
(number) and PeriodType (values like "d", "m", or "yyyy" for days, months,
or years), use:

NextDue: DateAdd([PeriodType], [Frequency], ( SELECT

Max([TrainingDate])
AS

LastTrained FROM Training WHERE (Training.EmployeeID =
ReqTraining.EmployeeID) AND (Training.CourseID = ReqTraining.CourseID) ))

4. If desired, use Nz() to specify a default due date if the person had
never had the training. It is also a good idea to wrap the calculation in
CDate() or CVDate() so Access knows it is a Date field and does not

treat

it
as text. Result:

NextDue: CDate(Nz(DateAdd([Frequency], [PeriodType], ( SELECT
Max([TrainingDate]) AS LastTrained FROM Training WHERE (Training.EmployeeID
= ReqTraining.EmployeeID) AND (Training.CourseID = ReqTraining.CourseID) )),
#1/1/2000#))

"Jason M Canady" <jasonm11_68ATcharterDOTnet> wrote in message
Hi, It must be getting late... I have worked on this query for several
days
and have yet to come up with a viable way to make it work, so I

thought

I
might see if I am trying the impossible...

I have three tables, EMPLOYEES, REQTRAINING, TRAINING

Employees table stores info about the employees (IE Name, Departments,
etc...)
ReqTraining Stores what training is required for each employee and the
interval for which retraining is required
Training stores all of the times an employee has had a particular
training.

I would like to retrieve all of the training types that are

identified
in

the required training table that are either overdue or do not exist

in
the

training table (IE the employee has not had the required training

yet.).

I
feel like I am very close to the answer, but cannot see the forest through
the trees.

Any assistance would be appreciated.
Thanks,
Jasonm