My work is to look after a Virtual Learning Environment that allow students to access course material. Each "course" has a specific ID (sample ID below). Every academic year a new copy of the course is copied with a new ID and same content and properties that is taken from a template.

Issue

As you can see from the sample data below, the Guest Access property has not been set for the current course, which is causing problems.

Process So Far

I have retrieve a complete list of all 1201 and 1211 courses in Postgres, exported them to separate Excel files, imported them into Access and created a relationship against the Course ID.

This is the SQL in Access that I have used.

SELECT current.external_course_key, current.course_id, current.allow_guest_access
FROM [current]
    INNER JOIN past ON past.external_course_key like current.external_course_key & "*";

I thought REGEX would help with this, but unfortunately, Access doesn't have that ability.

Requirement

I need to check for the guest access property in the past and current course ID's and retrieve a list of courses where there are differences.

Hopefully, I have explained that with enough detail. If you need any more, then please don't hesitate. This issue has been dropped on me and is a high priority.

Any help is appreciated.

thanks

CodePudding user response：

What you want is not totally clear to me, but my guess is that you want the following:

SELECT Current.Course_ID, Current.allow_guest_access
FROM
   Current
INNER JOIN
   Past
ON     (Mid(Current.Course_ID, 1, 17) =  Mid(Past.Course_ID, 1, 17))
   AND (Mid(Current.Course_ID, 22, 11) =  Mid(Past.Course_ID, 22, 11))
   AND (Current.allow_guest_access <> Past.allow_guest_access)

This will provide all the Current courses that have a different value of their property allow_guest_access in respect to their equivalent Past course. By "equivalent" I understood (from the two sample values that you show) that they should have all the digits of course_id the same, except for the four intermediate ones that are different.

If this is not what you wanted, please clarify it in a comment and I will edit this answer accordingly.