Complex query to return training completion dates

[BLM HR guy]

I'm an Access beginner, relatively speaking. I've created a query (and
report) which returns a list of employees, grouped by each
training/certification, with the date the training was completed and a
calculated field that returns when the training expires. (Trainings are taken
individually or by small groups) I then realized that higher-level trainings
meet requirements for completion of lower-level, as do refresher trainings.
The problem: supervisor's don't want their folks to show up deficient in any
area unless they're actually defficient. How can I structure a query--or
queries--to evaluate if a higher-level course or refresher applies?

 

[KARL DEWEY]

How can I structure a query--or queries--to evaluate if a higher-level
course or refresher applies?
This is rather difficult to do seeing as how you did not say anything about
how you are recording your data.
How do you indicate higher-level course from a refresher or for that matter
one course from another?
Post your table structure with field names and datatype. Also some sample
data.

 

[BLM HR guy]

Karl,

Thanks for the quick response. Sorry for the lack of data. I have not
decided how to assign/indicate training hierarchy. I only just learned there
WAS a heirarchy. How do I "post [my] table structure with field names and
datatype?" Access doesn't allow copy/paste from design view and I don't know
how to post a screen-capture to this forum...if it allows that. Simply:

Format = **Table Name
Field Name--Type--Primary key or joining field

**tbl_Trainings
Training--Text--Primary key and 1-to-many with tbl_TrainingCrossTable
Renewal_YRS--Number

**tbl_TrainingCrossTable
Employee_ID--Number--outer join w/ tbl_Employees
Training--Text--1-to-many join w/ tbl_Trainings
TrainingDate--Date/Time

**tbl_Employees
Employee_ID--AutoNumber--Primary key and outer join w/ tbl_TrainingCrossTable
EmployeeLastName--Text
---A whole lot of unrelated fields used in other applications

Sample data would be

**Employee_ID--Training--Date--Expires
101--First Aid Basics--10/01/2001--[TrainingDate]+365*[Renewal_YRS]
101--First Aid Basics Yearly Refresher--10/01/2006--" ' "
101--First Aid Advanced--10/02/2008--" ' "