C
Cydney
I'm doing my best to articulate this question without the ability to point to
my report and say "This can't be here because THIS is here.." =\ But here
goes..
We have a database containing a table of Employees with courses they've
taken by dates and then another table with their required courses based on
the type of work they do. One of the factors of whether the course needs to
be updated is based on the number of months since the last time it was taken.
Many courses have an alternative "retrain" course that's required to be taken
after the first course has expired. Or the course may not have an expiration
date at all. I've been able to flag the course that is out of date and list
it's retrain course. But there's a bigger delimma. About 5 of these courses
will not require retraining if the employee has taken a different course.
My problem is, I'm not sure how to search within the DETAIL data of my
report (which is grouped by employee name header) to find out if the other
course exists as taken (Date_Taken) and is not expired.
Here's an example:
Name (group header)
----------
CourseID Expiration Date_Taken RetrainCourseID FLAG
1A 12 months 1/1/2008 1B OK
(because 1B is good)
1B 12 " 1/1/2009 1B OK
1C 12 " 1/1/2008 1C
Expired (take course 1C over)
1D 12 " 1/1/2009 1D OK
In this case, 1A and 1B I've figured out how to flag. But now 1C and 1D are
equivalent and 1C should be flagged "OK" because he took 1D. So, without
messing up the other criteria for 1A and 1B, I need to somehow bypass that
and allow 1C to be ok because 1D exists --AND is not expired.
I'm guessing it's sort of a query within a query?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson
my report and say "This can't be here because THIS is here.." =\ But here
goes..
We have a database containing a table of Employees with courses they've
taken by dates and then another table with their required courses based on
the type of work they do. One of the factors of whether the course needs to
be updated is based on the number of months since the last time it was taken.
Many courses have an alternative "retrain" course that's required to be taken
after the first course has expired. Or the course may not have an expiration
date at all. I've been able to flag the course that is out of date and list
it's retrain course. But there's a bigger delimma. About 5 of these courses
will not require retraining if the employee has taken a different course.
My problem is, I'm not sure how to search within the DETAIL data of my
report (which is grouped by employee name header) to find out if the other
course exists as taken (Date_Taken) and is not expired.
Here's an example:
Name (group header)
----------
CourseID Expiration Date_Taken RetrainCourseID FLAG
1A 12 months 1/1/2008 1B OK
(because 1B is good)
1B 12 " 1/1/2009 1B OK
1C 12 " 1/1/2008 1C
Expired (take course 1C over)
1D 12 " 1/1/2009 1D OK
In this case, 1A and 1B I've figured out how to flag. But now 1C and 1D are
equivalent and 1C should be flagged "OK" because he took 1D. So, without
messing up the other criteria for 1A and 1B, I need to somehow bypass that
and allow 1C to be ok because 1D exists --AND is not expired.
I'm guessing it's sort of a query within a query?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson