[AccessD] Complicated join

You could do this with a subquery.  I just posted an example of how to do
this a few days ago.  Should be in the archives.  Use the subqueries in the
WHERE clause to create your 'join'.

Drew

-----Original Message-----
From: ksklos at comcast.net [mailto:ksklos at comcast.net] 
Sent: Saturday, December 16, 2006 8:22 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Complicated join

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