How to approach complex query

[Julia Boswell]

Hi folks,

I'm having a real problem getting a query (or set of queries to work) and no
matter what I try I fail :-( I wonder if someone could help me with the
approach?

I need data from 4 tables and I'm aware I need to create a series of queries
to end up with the final one, but how?

The tables are:

Employees with employeeID (primary key), employeename, startdate being the
key fields
TrainingDue with recordno (primary key), employeeID, trainingcourseID
TrainingCompleted with recordno (primary key), employeeID, trainingcourseID,
completedate being the key fields.
TrainingCourses with trainingcourseID (primary key), training course,
frequency being the key fields (note frequency is a long numeric field
identifying number of months)

My query should show all training due sorted in training course order and
when it is due for each relevant employee. I therefore want my final query
to pick all employees in the TrainingDue table that have training identified
as due. The problem is the date fields.

I need a "last completed date" which picks a matching max date from the
trainingcompleted table or displays "never completed" if there is no
matching record in the training completed table. If I use a base query to
select maxoftrainingcomplete, the query tends to display the last completed
date for the course period, not the last completed date for the course for
each employee.

I also need a "next due date" which adds the number of months in the
frequency field onto either the startdate (if the course has not been
completed) or onto the last completed date. This fails as the last completed
date I can't get working.

Any ideas?

Thanks

Julia

 

[Allen Browne]

Use a query with 3 of your tables, and a subquery to get the most recent
date completed. Note that the results of the query will be read-only.

Steps:
1. Create a query using the TrainingDue table, the Employees table, and
TrainingCourses table.

2. Although the TrainingCompleted table is not in your query, type this into
a fresh column in the Field row:
LastDone: (SELECT Max(TrainingCompleted.CompleteDate)
FROM TrainingCompleted
WHERE TrainingCompleted.EmployeeID = TrainingDue.EmployeeID
AND TrainingCompleted.trainingcourseID = TrainingDue.trainingcourseID)

3. In the next next column, in the field row, enter:
NextDue: Nz(DateAdd("m", TrainingCourses.frequency, [LastDone]),
Employees.startdate)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Ultimately, it might be better to combine those 2 fields into one (e.g. if
you want to use criteria on the field). That's just a matter of typing
everything after the column from the first one in place of [LastDone] in the
second one.

JET often misunderstands the data type of calculated fields, so it would
also be good to typecast the result. More info on this detail:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The end result will be something like this:
NextDue: CVDate(Nz(DateAdd("m", TrainingCourses.frequency,
(SELECT Max(TrainingCompleted.CompleteDate) FROM TrainingCompleted
WHERE TrainingCompleted.EmployeeID = TrainingDue.EmployeeID
AND TrainingCompleted.trainingcourseID = TrainingDue.trainingcourseID)),
Employees.startdate))

 

[Vincent Johns]

I have a couple of ideas (am working on an example, but won't be able to
post that before tonight).

To avoid confusion on my part, I changed each primary key from
[recordno] to a name suggestive of its Table (such as
[TrainingCompleted_ID]).

If, as you say, you want to add [TrainingCourses].[Frequency] to
[Employees].[StartDate], then why is [StartDate] not a field in one of
your other Tables instead, such as [TrainingDue]? Actually, the name
[Frequency] suggests recurring training, and my guess is that you'd want
to schedule it at the specified number of months after the employee has
completed /that course/, not from the time he was hired or other fixed
date applying to the employee irrespective of any training he might have
completed. My thought is that, for recurring training, you'd want to
create a new [TrainingDue] record, including the calculation involving
[Frequency], any time an employee completes a course.

Indeed, it may be that you can combine the fields from [TrainingDue] and
[TrainingCompleted] into one Table, unless you expect an employee to
complete a course more than once to satisfy a [TrainingDue] requirement,
or something like that. You could leave the completion date and
similar information blank until the course is completed. (But for the
example that I plan to post, I'll maintain your design, as I expect
there are other fields that affect your design decisions here.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Julia Boswell]

Thanks, this works very well!

Use a query with 3 of your tables, and a subquery to get the most recent
date completed. Note that the results of the query will be read-only.

Steps:
1. Create a query using the TrainingDue table, the Employees table, and
TrainingCourses table.

2. Although the TrainingCompleted table is not in your query, type this
into a fresh column in the Field row:
LastDone: (SELECT Max(TrainingCompleted.CompleteDate)
FROM TrainingCompleted
WHERE TrainingCompleted.EmployeeID = TrainingDue.EmployeeID
AND TrainingCompleted.trainingcourseID = TrainingDue.trainingcourseID)

3. In the next next column, in the field row, enter:
NextDue: Nz(DateAdd("m", TrainingCourses.frequency, [LastDone]),
Employees.startdate)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Ultimately, it might be better to combine those 2 fields into one (e.g. if
you want to use criteria on the field). That's just a matter of typing
everything after the column from the first one in place of [LastDone] in
the second one.

JET often misunderstands the data type of calculated fields, so it would
also be good to typecast the result. More info on this detail:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The end result will be something like this:
NextDue: CVDate(Nz(DateAdd("m", TrainingCourses.frequency,
(SELECT Max(TrainingCompleted.CompleteDate) FROM TrainingCompleted
WHERE TrainingCompleted.EmployeeID = TrainingDue.EmployeeID
AND TrainingCompleted.trainingcourseID =
TrainingDue.trainingcourseID)),
Employees.startdate))

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

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

Hi folks,

I'm having a real problem getting a query (or set of queries to work) and
no matter what I try I fail :-( I wonder if someone could help me with
the approach?

I need data from 4 tables and I'm aware I need to create a series of
queries to end up with the final one, but how?

The tables are:

Employees with employeeID (primary key), employeename, startdate being
the key fields
TrainingDue with recordno (primary key), employeeID, trainingcourseID
TrainingCompleted with recordno (primary key), employeeID,
trainingcourseID, completedate being the key fields.
TrainingCourses with trainingcourseID (primary key), training course,
frequency being the key fields (note frequency is a long numeric field
identifying number of months)

My query should show all training due sorted in training course order and
when it is due for each relevant employee. I therefore want my final
query to pick all employees in the TrainingDue table that have training
identified as due. The problem is the date fields.

I need a "last completed date" which picks a matching max date from the
trainingcompleted table or displays "never completed" if there is no
matching record in the training completed table. If I use a base query to
select maxoftrainingcomplete, the query tends to display the last
completed date for the course period, not the last completed date for the
course for each employee.

I also need a "next due date" which adds the number of months in the
frequency field onto either the startdate (if the course has not been
completed) or onto the last completed date. This fails as the last
completed date I can't get working.

Any ideas?

Thanks

Julia

 

[Julia Boswell]

Thanks, for this but I've already got a solution from Allen Browne. Having a
look at your ideas, I think the idea of creating a training due record every
time a training course is completed is a good idea, and I will certainly
consider that for the future. The idea currently is to work out whether
training has ever been completed, if not, then the due date is calculated
from the employee's start date, otherwise it's calculated from the last
training date completed. Employees can complete training courses multiple
times.

Thanks

Julia

I have a couple of ideas (am working on an example, but won't be able to
post that before tonight).

To avoid confusion on my part, I changed each primary key from [recordno]
to a name suggestive of its Table (such as [TrainingCompleted_ID]).

If, as you say, you want to add [TrainingCourses].[Frequency] to
[Employees].[StartDate], then why is [StartDate] not a field in one of
your other Tables instead, such as [TrainingDue]? Actually, the name
[Frequency] suggests recurring training, and my guess is that you'd want
to schedule it at the specified number of months after the employee has
completed /that course/, not from the time he was hired or other fixed
date applying to the employee irrespective of any training he might have
completed. My thought is that, for recurring training, you'd want to
create a new [TrainingDue] record, including the calculation involving
[Frequency], any time an employee completes a course.

Indeed, it may be that you can combine the fields from [TrainingDue] and
[TrainingCompleted] into one Table, unless you expect an employee to
complete a course more than once to satisfy a [TrainingDue] requirement,
or something like that. You could leave the completion date and similar
information blank until the course is completed. (But for the example
that I plan to post, I'll maintain your design, as I expect there are
other fields that affect your design decisions here.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hi folks,

I'm having a real problem getting a query (or set of queries to work) and
no matter what I try I fail :-( I wonder if someone could help me with
the approach?

I need data from 4 tables and I'm aware I need to create a series of
queries to end up with the final one, but how?

The tables are:

Employees with employeeID (primary key), employeename, startdate being
the key fields
TrainingDue with recordno (primary key), employeeID, trainingcourseID
TrainingCompleted with recordno (primary key), employeeID,
trainingcourseID, completedate being the key fields.
TrainingCourses with trainingcourseID (primary key), training course,
frequency being the key fields (note frequency is a long numeric field
identifying number of months)

My query should show all training due sorted in training course order and
when it is due for each relevant employee. I therefore want my final
query to pick all employees in the TrainingDue table that have training
identified as due. The problem is the date fields.

I need a "last completed date" which picks a matching max date from the
trainingcompleted table or displays "never completed" if there is no
matching record in the training completed table. If I use a base query to
select maxoftrainingcomplete, the query tends to display the last
completed date for the course period, not the last completed date for the
course for each employee.

I also need a "next due date" which adds the number of months in the
frequency field onto either the startdate (if the course has not been
completed) or onto the last completed date. This fails as the last
completed date I can't get working.

Any ideas?

Thanks

Julia

 

[Vincent Johns]

Yes, Allen Browne's solution looks good -- I wouldn't have posted
anything if I'd seen his first.

However, I did have a couple of questions, and you may already have
addressed them in your design. But in case not, here they are...

How do you know if the records match? My guess is that, if
[Employees].[StartDate] is later than that employee's last
[TrainingCompleted].[CompleteDate] for some course, or if there is no
[TrainingCompleted] record for that employee/course combination, it is a
"never completed" course for that employee.

How do you handle [TrainingCompleted] records for which no [TrainingDue]
record exists?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks, for this but I've already got a solution from Allen Browne. Having a
look at your ideas, I think the idea of creating a training due record every
time a training course is completed is a good idea, and I will certainly
consider that for the future. The idea currently is to work out whether
training has ever been completed, if not, then the due date is calculated
from the employee's start date, otherwise it's calculated from the last
training date completed. Employees can complete training courses multiple
times.

Thanks

Julia

I have a couple of ideas (am working on an example, but won't be able to
post that before tonight).

To avoid confusion on my part, I changed each primary key from [recordno]
to a name suggestive of its Table (such as [TrainingCompleted_ID]).

If, as you say, you want to add [TrainingCourses].[Frequency] to
[Employees].[StartDate], then why is [StartDate] not a field in one of
your other Tables instead, such as [TrainingDue]? Actually, the name
[Frequency] suggests recurring training, and my guess is that you'd want
to schedule it at the specified number of months after the employee has
completed /that course/, not from the time he was hired or other fixed
date applying to the employee irrespective of any training he might have
completed. My thought is that, for recurring training, you'd want to
create a new [TrainingDue] record, including the calculation involving
[Frequency], any time an employee completes a course.

Indeed, it may be that you can combine the fields from [TrainingDue] and
[TrainingCompleted] into one Table, unless you expect an employee to
complete a course more than once to satisfy a [TrainingDue] requirement,
or something like that. You could leave the completion date and similar
information blank until the course is completed. (But for the example
that I plan to post, I'll maintain your design, as I expect there are
other fields that affect your design decisions here.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Julia Boswell wrote:

Hi folks,

I'm having a real problem getting a query (or set of queries to work) and
no matter what I try I fail :-( I wonder if someone could help me with
the approach?

I need data from 4 tables and I'm aware I need to create a series of
queries to end up with the final one, but how?

The tables are:

Employees with employeeID (primary key), employeename, startdate being
the key fields
TrainingDue with recordno (primary key), employeeID, trainingcourseID
TrainingCompleted with recordno (primary key), employeeID,
trainingcourseID, completedate being the key fields.
TrainingCourses with trainingcourseID (primary key), training course,
frequency being the key fields (note frequency is a long numeric field
identifying number of months)

My query should show all training due sorted in training course order and
when it is due for each relevant employee. I therefore want my final
query to pick all employees in the TrainingDue table that have training
identified as due. The problem is the date fields.

I need a "last completed date" which picks a matching max date from the
trainingcompleted table or displays "never completed" if there is no
matching record in the training completed table. If I use a base query to
select maxoftrainingcomplete, the query tends to display the last
completed date for the course period, not the last completed date for the
course for each employee.

I also need a "next due date" which adds the number of months in the
frequency field onto either the startdate (if the course has not been
completed) or onto the last completed date. This fails as the last
completed date I can't get working.

Any ideas?

Thanks

Julia