[AccessD] Complicated join

Hi Susan

That could be done this way:

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

/gustav


>>> ksklos at comcast.net 18-12-2006 00:50 >>>
OK, Gustav.  This is good once I isolate the records that have 3 characters at the beginning of the field from the others.  Any idea how to do that?

-------------- Original message -------------- 
From: "Gustav Brock" <gustav at cactus.dk> 

> 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. 
> 
> -- 
> AccessD mailing list 
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd 
> Website: http://www.databaseadvisors.com 
-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com