Join using LIKE
in [Getting Started]
|
Prev: How do I retrieve my ID code.I have already registered.
Next: Having problems understanding sub-form selections and uses ofcomb
From: JE on 22 Feb 2010 06:48 I have two tables with data that is nearly exact - one table has truncated data: Table 1 BARRICK GOLD CORP DIREXION SHS ETF TR Table 2 BARRICK GOLD CORP COM ISIN# DIREXION SHS ETF TR LARGE CAP BULL 3X SHS I am wanting to join these two tables on the these fields to extract another field. Is this possible? And is it possible without using VBA using a Design View query? Many thanks.
From: Rick Brandt on 22 Feb 2010 07:21 JE wrote: > I have two tables with data that is nearly exact - one table has truncated > data: > > Table 1 > BARRICK GOLD CORP > DIREXION SHS ETF TR > > Table 2 > BARRICK GOLD CORP COM ISIN# > DIREXION SHS ETF TR LARGE CAP BULL 3X SHS > > I am wanting to join these two tables on the these fields to extract > another > field. Is this possible? And is it possible without using VBA using a > Design View query? It is possible and you do not need to use VBA, but you do have to use the SQL view of the query designer. The graphical designer can only deal with joins using =. SELECT * FROM [Table 2] INNER JOIN [Table 1] ON [Table 2].FieldName LIKE [Table 1].FieldName & "*"
From: Daryl S on 22 Feb 2010 10:11 JE - You can do this in query design. First, remove any links between the tables, so they look like they are not joined at all. Then, add the fields in the grid, and under the two fields from Table 2 put criteria like this: Like [Table 1]![field1] & "*" Do this for both fields. -- Daryl S "JE" wrote: > I have two tables with data that is nearly exact - one table has truncated > data: > > Table 1 > BARRICK GOLD CORP > DIREXION SHS ETF TR > > Table 2 > BARRICK GOLD CORP COM ISIN# > DIREXION SHS ETF TR LARGE CAP BULL 3X SHS > > I am wanting to join these two tables on the these fields to extract another > field. Is this possible? And is it possible without using VBA using a > Design View query? > > Many thanks.
From: KARL DEWEY on 22 Feb 2010 11:05 If the short field is consistent in the number of characters you can use two queries by creating a calculated field in the first and left function. In the second query join on the calculated field. -- Build a little, test a little. "JE" wrote: > I have two tables with data that is nearly exact - one table has truncated > data: > > Table 1 > BARRICK GOLD CORP > DIREXION SHS ETF TR > > Table 2 > BARRICK GOLD CORP COM ISIN# > DIREXION SHS ETF TR LARGE CAP BULL 3X SHS > > I am wanting to join these two tables on the these fields to extract another > field. Is this possible? And is it possible without using VBA using a > Design View query? > > Many thanks.
From: David W. Fenton on 22 Feb 2010 18:43
=?Utf-8?B?RGFyeWwgUw==?= <DarylS(a)discussions.microsoft.com> wrote in news:FE1ABC91-992C-46EB-A0DB-49AF6D16185F(a)microsoft.com: > You can do this in query design. First, remove any links between > the tables, so they look like they are not joined at all. > > Then, add the fields in the grid, and under the two fields from > Table 2 put criteria like this: > Like [Table 1]![field1] & "*" > > Do this for both fields. Why do you need it under both fields? Certainly if you put that criteria under Field1, all records will match, so it seems redundant to me. It won't change the result set, but it might cause the query optimizer to evaluate it incorrectly. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |