Selecting records with matching criteria

[Daryl2106]

Hi,

I have a table of CourseNumbers that contains a four digit course number assigned to a particluar class each year. (The course number does not carry over from year to year). Each course number and its PK is associated with three FKs one each for CourseName,CourseSection and InstructorName. Some of the CourseNumbers are numerically the same from year to year but the PK is always unique because the course may have a new section, different instructor, etc. There are about 200 CourseNumbers in a single table; about 30 per year associated with a year field (2012, 2013, 2014).

When students return, these CourseNumbers need to be updated (in the StudentEnrollment table) to reflect the new CourseNumber assigned for the current year (students can continue in a course for two years). I need to update these numbers based on the CourseName and InstructorName fileds where there is a match. That is, find last year's CourseNumber and this year's CourseNumber where the CourseName and Instructor fields match.

I need to first select the StudentEnrollment records which need to be updated (which I can do). I then to need to update FK in each record to match the PK in the CourseNumbersT that corresponds to the new CourseNumber for the current year.

My difficulty is in creating the query to match last year's CourseNumbers with this year's CourseNumber based on the InstructorName and CourseName fields being equal.

I know this shouldn't be too difficult, I just can't seem to figure it out. I'm not great with SQL and I use the query builder a lot.



Thanks in advance and take care,

Daryl

[June7]

Sounds like you need to do a self-join on the Courses table, with a little twist.

Do a query that constructs a field to calculate the previous year for each record.

SELECT CourseNumber, [yearfield], InstuctorName, CourseName, [yearfield]-1 As PrevYr FROM Courses;

Now do another query that joins that query to the Courses table linking on the Year/PrevYr, InstuctorName, CourseName fields.

[Daryl2106]

Thanks June. It worked!