Comparing data between a field and the values in a query

[Ryis]

I have a form that has a subform which displays all the training records for
an employee. I also have on the same form a subform that list required
training for that employee. I would like to be able to have the required
training query not show an item once it has been started in the training
subform.

The required training list is static information that never changes. I use
this table to open the training form and populate fields based on the static
info. The field include the training description.

The training subform query is filtered by employee name

The required training is filtered by check boxes on the same form which
allow only to show training required for the level of the employee.

Once the training record is started, i would like the required training
query to check the training query and if the training description exist in
the training query i would like it to show in a field just on the
subform..not recorded to say "In Progress" or "Not started"

If anybody has an idea...I guess i am looking for an If statement that
checks a value to a whole query


Thanks

Ryan

 

[Ron2006]

Two solutions - I believe I would prefer the second.

Solution 1
To the query that lists the required training, add a query of (the
same one that you use for the showing the training that the employee
HAS or IS taking) and link by the traing id or description or whatever
will allow the match but make it so that the required will show even
if the employee has not taken it.

then add criteria to that query that says only show records that are
null in the matching field of the employee's records. This wil then
only show those that the employee has NOT yet Started/taken.

Solution 2 if these are truely subforms and not dropdowns.
To the query that lists the required training, add a query of (the
same one that you use for the showing the training that the employee
HAS or IS taking) and link by the traing id or description or whatever
will allow the match but make it so that the required will show even
if the employee has not taken it.

Show all of the records on the required side but then use the form's
conditional formating to do one or more of the following:
disable the description field IF the employee matching ID is NOT
null. or

color the description field as green IF the employee matching ID is
NOT null and
color the description field as red if the employee matching ID IS NULL

The advantage I see of this second solution is that the user always
sees the same number of entries for required for the particular level
that the employee is on. This tends to give the user confidence that
the application really does know all of the requirements. It also
makes it easier for them to visually see that the system has not lost
one of the requirements.

Ron