Comparing and highlighting records between two subforms (1 Viewer)

[nashaz]

Hi all

Please refer to the db attached. Before I ask the question, here are the details of the db (and apologies for a long thread):

• CourseListT, EmployeeT, PositionT, and ProviderT are self-explanatory
• Course_JT (PK DeliverID) keeps records of courses provided by providers e.g., one course may have many providers
• RoleXCourse_JT keeps records of mandatory courses based on roles i.e., PositionID and CourseID
• RoleXEmployee_JT keeps records of which roles have been assigned to which employee i.e., PositionID and CourseID
• EmployeeXCourse_JT keeps records of which employee has been delivered which training (irrespective of whether that course is mandatory or not) i.e., course and provider both, hence contains EmployeeID and DeliverID

The parent form in the db is EmployeeF which contains three subforms, RoleXEmployee, EmployeeXCourse, and EmployeeRoleTraining which is based off the query of the same name. This Query brings together records from RoleXCourse and RoleXEmployees. The idea of the parent form is that it shows the employee's detail, the roles assigned to them (via RoleXEmployee subF), all the trainings delivered (via EmployeeXCourse subF), and mandatory training as per the role assigned to them (via EmployeeRoleTraining subF).

What I am looking to do is to highlight the records in EmployeeRoleTraining subF based on the fact whether a particular mandatory course was delivered or not (i.e., if a record for the CourseID exists in EmployeeXCourse_JT or not). If delivered, that particular entry should be green. If CourseID from EmployeeRoleTraining is not present in the EmployeeXCourse_JT, then that record should be highlighted red.

In the hope of doing so, I have created another query called EmployeeRoleTraining_SubQ which links EmployeeRoleTraining with EmployeeXCourse but I can clearly see that query does not produce right results.

Your help will be greatly appreciated.

 

[bob fitz]

Hi all

Please refer to the db attached. Before I ask the question, here are the details of the db (and apologies for a long thread):

• CourseListT, EmployeeT, PositionT, and ProviderT are self-explanatory
• Course_JT (PK DeliverID) keeps records of courses provided by providers e.g., one course may have many providers
• RoleXCourse_JT keeps records of mandatory courses based on roles i.e., PositionID and CourseID
• RoleXEmployee_JT keeps records of which roles have been assigned to which employee i.e., PositionID and CourseID
• EmployeeXCourse_JT keeps records of which employee has been delivered which training (irrespective of whether that course is mandatory or not) i.e., course and provider both, hence contains EmployeeID and DeliverID

The parent form in the db is EmployeeF which contains three subforms, RoleXEmployee, EmployeeXCourse, and EmployeeRoleTraining which is based off the query of the same name. This Query brings together records from RoleXCourse and RoleXEmployees. The idea of the parent form is that it shows the employee's detail, the roles assigned to them (via RoleXEmployee subF), all the trainings delivered (via EmployeeXCourse subF), and mandatory training as per the role assigned to them (via EmployeeRoleTraining subF).

What I am looking to do is to highlight the records in EmployeeRoleTraining subF based on the fact whether a particular mandatory course was delivered or not (i.e., if a record for the CourseID exists in EmployeeXCourse_JT or not). If delivered, that particular entry should be green. If CourseID from EmployeeRoleTraining is not present in the EmployeeXCourse_JT, then that record should be highlighted red.

In the hope of doing so, I have created another query called EmployeeRoleTraining_SubQ which links EmployeeRoleTraining with EmployeeXCourse but I can clearly see that query does not produce right results.

Your help will be greatly appreciated.

Not sure that I've understood the requirement but see attached:

 

[mike60smart]

Hi all

Please refer to the db attached. Before I ask the question, here are the details of the db (and apologies for a long thread):

• CourseListT, EmployeeT, PositionT, and ProviderT are self-explanatory
• Course_JT (PK DeliverID) keeps records of courses provided by providers e.g., one course may have many providers
• RoleXCourse_JT keeps records of mandatory courses based on roles i.e., PositionID and CourseID
• RoleXEmployee_JT keeps records of which roles have been assigned to which employee i.e., PositionID and CourseID
• EmployeeXCourse_JT keeps records of which employee has been delivered which training (irrespective of whether that course is mandatory or not) i.e., course and provider both, hence contains EmployeeID and DeliverID

The parent form in the db is EmployeeF which contains three subforms, RoleXEmployee, EmployeeXCourse, and EmployeeRoleTraining which is based off the query of the same name. This Query brings together records from RoleXCourse and RoleXEmployees. The idea of the parent form is that it shows the employee's detail, the roles assigned to them (via RoleXEmployee subF), all the trainings delivered (via EmployeeXCourse subF), and mandatory training as per the role assigned to them (via EmployeeRoleTraining subF).

What I am looking to do is to highlight the records in EmployeeRoleTraining subF based on the fact whether a particular mandatory course was delivered or not (i.e., if a record for the CourseID exists in EmployeeXCourse_JT or not). If delivered, that particular entry should be green. If CourseID from EmployeeRoleTraining is not present in the EmployeeXCourse_JT, then that record should be highlighted red.

In the hope of doing so, I have created another query called EmployeeRoleTraining_SubQ which links EmployeeRoleTraining with EmployeeXCourse but I can clearly see that query does not produce right results.

Your help will be greatly appreciated.

Hi
The way you have the Tables related you will see a
List of all Courses in "RoleXEmployee_JT Form"
and
"EmployeeRoleTrainingQ subform" will show you the same list ????

 

[nashaz]

Not sure that I've understood the requirement but see attached:

Hi Bob

Looks like exactly what I wanted! What a neat solution, thanks!

 

[bob fitz]

Hi Bob

Looks like exactly what I wanted! What a neat solution, thanks!

Always pleased to help if I can

 

[nashaz]

Hi
The way you have the Tables related you will see a
List of all Courses in "RoleXEmployee_JT Form"
and
"EmployeeRoleTrainingQ subform" will show you the same list ????

Hi Mike

RoleXEmployee_JT sub form shows roles assigned to the employee in the parent form, and EmployeeRoleTrainingQ subform shows trainings mandatory for that employee and the roles assigned to them.

 

[bob fitz]

Hi Bob

Looks like exactly what I wanted! What a neat solution, thanks!

I've just had an afterthought. Won't you need to take into consideration the length of time that a certificate is valid for?
I noticed a field called "ValidFor". Would I be correct in assuming that this holds the number of days that the certificate is valid for?
Do you need to indicate in the form "EmployeeRoleTrainingQ subform" any certificates that are "outdated" ?

 

[nashaz]

I've just had an afterthought. Won't you need to take into consideration the length of time that a certificate is valid for?
I noticed a field called "ValidFor". Would I be correct in assuming that this holds the number of days that the certificate is valid for?
Do you need to indicate in the form "EmployeeRoleTrainingQ subform" any certificates that are "outdated" ?

Yes, thats what the field precisely is for. For confidentiality reasons I couldnt upload the original db which is more extensive. It was just this bit I couldnt get to work. I am thinking I can use the CourseID2 technique with the validity as well and do the conditional formatting for date rather than null is not null for CourseID2?

 

[bob fitz]

....I can use the CourseID2 technique with the validity as well and do the conditional formatting for date rather than null is not null for CourseID2?

I think that would work but you might need to check the length of Cert Validity AS WELL as the checking for Null in CourseID2. Perhaps a 3rd conditional rule.

Post back if you need more help

 

[nashaz]

I think that would work but you might need to check the length of Cert Validity AS WELL as the checking for Null in CourseID2. Perhaps a 3rd conditional rule.

Post back if you need more help

So basically what I have is ValidFor field and CertificationDate field. I then have an ExpiresOn field which is just CertificationDate+ValidFor. and in the conditional formatting I set up red highlight if ExpiresOn is less than =Date(), yellow if ExpiresOn is between =Date() and Date()+45, and green if ExpiresOn > Date()+45.

I will be sure to reply back if I needed help with this though. Thank you very much

 

[bob fitz]

So basically what I have is ValidFor field and CertificationDate field. I then have an ExpiresOn field which is just CertificationDate+ValidFor. and in the conditional formatting I set up red highlight if ExpiresOn is less than =Date(), yellow if ExpiresOn is between =Date() and Date()+45, and green if ExpiresOn > Date()+45.

I will be sure to reply back if I needed help with this though. Thank you very much

If CertificationDate is the date that the Cert was issued then it might be better to NOT have an ExpiresOn field in a table but have that date calculated in the query. It is usually considered to be better to do the calculation whenever and wherever it is required than to store it in a table.

 

[nashaz]

If CertificationDate is the date that the Cert was issued then it might be better to NOT have an ExpiresOn field in a table but have that date calculated in the query. It is usually considered to be better to do the calculation whenever and wherever it is required than to store it in a table.

Yes, sorry thats what I meant. CertificationDate is a field in a table and then ExpiresOn is created in the query like you created CourseID2. My bad for not being clear on it.

 

[bob fitz]

Yes, sorry thats what I meant. CertificationDate is a field in a table and then ExpiresOn is created in the query like you created CourseID2. My bad for not being clear on it.

Without wishing to be pedantic and for the sake of that clarity, what you have said is correct but take the following into account:
If the body that issues a cert were to change the length of time a cert is valid for from, then you would need to create a new entry in "CertListT" rather than edit the existing entry.

 

[nashaz]

Without wishing to be pedantic and for the sake of that clarity, what you have said is correct but take the following into account:
If the body that issues a cert were to change the length of time a cert is valid for from, then you would need to create a new entry in "CertListT" rather than edit the existing entry.

I actually hadn't thought of this scenario. Totally makes sense why to not change the existing record. Thank you for bringing this up!

 

[Pat Hartman]

@nashaz you wouldn't be comparing data in the three subforms. You would be using a query that joins the three sets of data.