Wouldn't you use JET4.0 instead of ACE? ACE is the filetype for Access 2007 and beyond.

Cool! Thanks, Stefan!

Now to walk through it slowly (with the explanations) and see if I can learn to spot the pattern.

I think the fun part is the NOT EXISTS...

Here's a small subset of the data... that's actually intelligible. Apologies for any SQL errors.

CREATE TABLE #Course (

CourseID CHAR(7),

CONSTRAINT pkCourse PRIMARY KEY (CourseID));

CREATE TABLE #Prereq (

NextCourseID CHAR(7),

RequiresCourseID CHAR(7),

CONSTRAINT pkPrereq...

I must have munged the SQL somewhere... it's not omitting the "next" courses for which a given student has some but not all of the prerequisites.

Here's the SQL I have,...

okay, now to do something sane and create a subset of the two tables and make sure it works -- looks like it does, but I'm not sure yet. ...

So negate everything in parentheses, and then wrap a NOT around it (double negatives, my favorite!)

Makes sense... now, let me see if I can actually make it work!

Thanks!

Since the course level should be fairly static (well, unless the prerequisites for a course are changed), I figured I would change things a little and store the computed level....

LOL, thanks.

The funny thing was that I figured it out after posting a couple of times. I guess posting forces me to re-read a bunch of my code to...

Figured it out... I was joining on the wrong column in the CTE...

-- courses without prereqs

WITH PrereqsCTE (CourseID, Depth) AS

(

--- Anchor: courses without prerequisites

SELECT c.CourseID, 0 AS Lvl

FROM Course c

WHERE...

Oh, super cool! Guess I gotta read (and re-read) Paul's article until I understand APPLY, then...

what if you use a different browser?

what if you print a different report?

This might help

http://www.sqlservercentral.com/blogs/pearlknows/2009/08/18/i-o-i-o-it-s-why-my-server-s-slow-examing-i-o-statistics/

Never use someone else's code that you don't understand, because if it breaks, you're on the hook.

Try Ola Hallengren's stuff.

Why not use EXISTS and something like t1.FK<>t2.FK to check for the existence of two records?

By definition, EXISTS stops as soon as it evaluates to True.

Sean's right - honey beats...

Thanks HappyCat,

worked a charm!

Pieter