Considering the following tables is it possible to
write an SQL query that would result in rows that look like
(EmpID, CourseID, DateTaken) for each employee and each
required course in one result set?

1 4 8/12/2005
1 5 8/12/2005
1 6 NULL
2 6 7/7/2006
2 4 NULL

Employees(EmpID*, PosID)
Positions(PosID*)
Courses(CourseID*)
RequiredCourses(CourseID*, PosID*)
CompletedCourses(EmpID*, CourseID*, DateTaken*)

Best I can do is a resultset for a single employee
doing a Union query filtering the not completed
courses something like this:

SELECT ...
FROM CompletedCourses
....
UNION
SELECT ...
FROM RequiredCourses
....
WHERE CourseID NOT IN
(SELECT CourseID
FROM CompletedCourses
INNER JOIN Employees
ON Employee.EmpID =
CompletedCourse.EmpID)

rkc wrote:
Just thinking out loud...

what does this give you?

SELECT cc.EmpID,cc.CourseID,cc.DateTaken
FROM CompletedCourses cc, Employees e, RequiredCourses rc
WHERE cc.EmpID = e.EmpID
AND cc.CourseID = rc.CourseID
AND rc.PosID = e.PosID
ORDER BY cc.EmpID,cc.DateTaken;

zac.carey (AT) gmail (DOT) com wrote:
Oh hang on - you're subject line said Completed AND Required.
That would be more along these lines - although this isn't quite right
either:

SELECT e.EmpID, cc.CourseID, cc.DateTaken
FROM Employees e
LEFT JOIN RequiredCourses rc ON rc.PosID = e.PosID
LEFT JOIN CompletedCourses cc ON cc.EmpID = e.EmpID
AND cc.CourseID = rc.CourseID
LIMIT 0 , 30

zac.carey (AT) gmail (DOT) com wrote:
this should be right now...

SELECT e.EmpID, rc.CourseID, cc.DateTaken
FROM Employees e
LEFT JOIN RequiredCourses rc ON rc.PosID = e.PosID
LEFT JOIN CompletedCourses cc ON cc.EmpID = e.EmpID
AND cc.CourseID = rc.CourseID
LIMIT 0 , 30

zac.carey (AT) gmail (DOT) com wrote:
I appreciate your efforts on my behalf.
I have not tried the first query shown yet.

The second one shown, with the Left Joins has
Access (I'm working with an Jet/.mdb database)
complaining about ambiguous joins and suggesting
I create a seperate query that does the first join
and include it in the other.

So I'm am going see what I can do with that
advice.

rkc wrote:
Sorry, I know nothing about Access - but, on the face of it, I can't
see why it wouldn't work. Maybe Access doesn't like aliases? Try this:

SELECT Employess.EmpID, RequiredCourses.CourseID,
CompletedCourses.DateTaken
FROM Employees
LEFT JOIN RequiredCourses ON RequiredCourses.PosID = Employees.PosID
LEFT JOIN CompletedCourses ON CompletedCourses.EmpID = Employees.EmpID
AND CompletedCourses.CourseID = RequiredCourses.CourseID;

zac.carey (AT) gmail (DOT) com wrote:
Jet handles the aliases O.K.
It's the last join where the ON criteria comes from
two different tables. Remove either one and no error.

rkc wrote:
I see, so it needs a derived table. Does this work?

SELECT t1.EmpID, t1.CourseID, t1.DateTaken
FROM
(SELECT e.EmpID, e.PosID, rc.CourseID, cc.DateTaken
FROM
Employees e, RequiredCourses rc, CompletedCourses cc) t1
LEFT JOIN RequiredCourses ON RequiredCourses.PosID = t1.PosID
LEFT JOIN CompletedCourses ON CompletedCourses.EmpID = t1.EmpID
AND CompletedCourses.CourseID = t1.CourseID;

zac.carey (AT) gmail (DOT) com wrote:
This one runs. The problem is that it runs forever. Access
will show the rows as it comes up with them and give a total
count when the query stops running. The query never stops
running and the rows it does return are not what I'm looking
for.

I'm beginning to think I have to run my original solution once
for each employee and procedurally build a temp table. That is
within my skill set. It just didn't seem right to have to do that.

rkc wrote:
I don't think so. We must be close - I probably made a mistake in my
logic - it's just that I'm stabbing in the dark a bit now.

SELECT E_ID, EP_ID, RC_ID, DateTaken
FROM (

SELECT e.EmpID E_ID, rc.CourseID RC_ID, e.PosID EP_ID, rc.PosID RCP_ID
FROM Employees e, RequiredCourses rc
WHERE e.PosID = rc.PosID
)t1
LEFT JOIN CompletedCourses cc ON cc.EmpID = t1.E_ID
AND cc.CourseID = t1.RC_ID;