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