[AccessD] Joining Two Tables

MarkH lists at theopg.com
Mon Nov 15 03:37:54 CST 2004


Thanks John

Mark

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: 14 November 2004 13:05
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Joining Two Tables


It is my understanding that a join breaks down to (is interpreted as)
exactly that code anyway.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MarkH
Sent: Thursday, November 11, 2004 3:51 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Joining Two Tables


I never performance tested it and only used this method in migrations
etc. I'd be real interested to know what the limitations are...?

Mark (the other one)

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mitsules,
Mark
Sent: 11 November 2004 20:45
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Joining Two Tables


Thanks for the suggestion.  In this instance, since I'm dealing with <
10K records I wonder if efficiency would be noteworthy?


Mark




-----Original Message-----
From: MarkH [mailto:lists at theopg.com] 
Sent: Thursday, November 11, 2004 3:05 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Joining Two Tables


Didn't read the whole thread so sorry if this has been done...

You don't need a join, you can just use criteria if you like (not
necessarilly very eficient though?)

E.g. "SELECT a.*, b.* FROM a, b WHERE a.col1=mid(b.col2,3)"

Hth

Mark

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mitsules,
Mark
Sent: 11 November 2004 13:35
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Joining Two Tables


Thank you all for the comments.

That is exactly what I ended up doing.  But since I lack the SQL "guru"
certification ;) I thought I'd put the question out there.  I was
thinking back to a thread a long while back regarding a method that
creates a sort of temporary table within one query...or something along
those lines.  I didn't know if/how that method might apply to my
situation.

Mark


-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Thursday, November 11, 2004 6:07 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Joining Two Tables


Hi Mark and Arthur

That may work but you'll be in trouble when trying to switch to design
view. I would create a query for Tbl2 with a field with the expression
needed to match the Tbl1.Fld1. Then use this query for Tbl1 whenever you
need to join Tbl1 and Tbl2.

/gustav


>>I have two tables I would like to join.  Tbl1.Fld1 contains a
>>variable-length string, Tbl2.Fld2 contains the same variable-length 
>>string concatenated with a variable-length suffix.
>>
>>Any suggestions on how to proceed?  Is there an elegant solution?
>>
>>Mark

> At first blush I would try this:
>
> SELECT * FROM T1 INNER JOIN T2 ON T1.Field1 = Left(T.2.Field1,
> LEN(T1.Field1))

> Arthur

-- 
_______________________________________________
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

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.769 / Virus Database: 516 - Release Date: 24/09/2004
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.769 / Virus Database: 516 - Release Date: 24/09/2004
 

-- 
_______________________________________________
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

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.769 / Virus Database: 516 - Release Date: 24/09/2004
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.769 / Virus Database: 516 - Release Date: 24/09/2004
 

-- 
_______________________________________________
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

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.769 / Virus Database: 516 - Release Date: 24/09/2004
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.769 / Virus Database: 516 - Release Date: 24/09/2004
 




More information about the AccessD mailing list