[AccessD] Complicated join

ksklos at comcast.net ksklos at comcast.net
Sat Dec 16 08:22:25 CST 2006


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 last 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.


More information about the AccessD mailing list