[AccessD] Complicated join

Hi Susan

For such cases I create a select query for each table which just returns all records but with the modified key.

Here, something like this:

  SELECT
    Left(CourseNo, 3) & Right(CourseNo, 3) AS ID,
    *
  FROM
    tblCourses

and

  SELECT
    Left(CourseNo, 3) & Right(CourseNo, 3) AS ID,
    *
  FROM
    tblMajor

Then you can join and filter etc. as you would for any other table using the cleaned ID fields - except for updates, of course.

/gustav


>>> ksklos at comcast.net 16-12-06 15:22 >>>
We are a SAS shop and it isn't very often because of what we do that I get the chance to show Access's colors.  However, the latest project has been great for this.  It is a perfect project where using a relational database has been faster.  However, I have run into a problem I hop someone can help me with.  First the data is in Excel.  The two tables need to be matched by course number.  In one table (tblCourses) all the course numbers are 7 characters long.  Some are numbers, others are character+6 numbers, 4 characters+3numbers.  The fourth character in the last instance indicates if the course is a lab (L), a combination course (C) or a college level class (1-0).  The other table (tblMajors) came from a different office and has 7 characters also but the 4th character does not always correspond to what is in tblCourses.  This is not critical as long as the first three and the laste 3 match.  So I figured that I would create a new field that had just the first 3 and the la!
st thre
e and join the tables on that field.  Here is the rub.  Most of the entries in the courses fields match because they are 7 numbers or they both have the character+6 numbers combination.  There are approximately 1800 records that need to be matched with the 3/3 combination.  How do I isolate just those records that need to be matched using the 3/3 combination?  Here is an example:  tblCourses: SPN1101, SPN2101, SPNL101, can match with tblMajors:  SPNX101.  I hope this is understandable.  I can send more examples if needed.  Thanks.