Join two tables with 3 fields; 1 field doesn't match exactly

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I am using Access 2002. I created two tables and want to join them on three
fields. All text fields. Two fields from each table match (ie. Tbl1 = ABC
and Tbl2 = ABC). The third field from Tbl1 contains part of the data that I
need but doesn't exactly match the corresponding field in Tbl2 (ie. Tbl1 =
AB&xxx.LXz and Tbl2 = AB). How can I join all three fields to get an
accurate output? I'm new at this! Thanks :)
 
Is it always the first two letters in Tbl1 that match tbl2? If so, try
something like this:


WHERE mid([Tbl1.Field], 1, 2) = [Tbl2.Field])

The Mid function above would select only the first two characters of Tbl1
and try to match them with Tbl2. The Like comparison and wildcard may also
work. Don't take the syntax above for granted... I'm not sure where you are
trying to apply this, so the statement is 'reference only'

HTH
-jack
 
Hey Jack...thanks for the respone.

No...it could be anywhere within the string of letters / characters. I'm
trying to join these fields in order to run a query that brings together data
from both tables. I've tried running the query with Like and wildcard but
the results aren't correct because these two fields aren't equal. So,
inTbl1, I want the query to provide product, mktg division, part number and
options (which is the AB&xxx.LXz field). In Tbl2, I want the query to
provide vehicle code, vehicle name and engine (which is the AB field). The
options field in Tbl1 (AB&xxx.LXz) contains the engine data (AB) from Tbl2.
There could be several engines listed in the options field in Tbl1 but only
one engine listed in Tbl2. Therefore, I need all of the records returned
from Tbl1 but based on the engine data from Tbl2.

I thought maybe a Union Query but don't know the sql language to use. I'm
not sure if I can join these fields at this point.
Thanks.
Lisa

Dymondjack said:
Is it always the first two letters in Tbl1 that match tbl2? If so, try
something like this:


WHERE mid([Tbl1.Field], 1, 2) = [Tbl2.Field])

The Mid function above would select only the first two characters of Tbl1
and try to match them with Tbl2. The Like comparison and wildcard may also
work. Don't take the syntax above for granted... I'm not sure where you are
trying to apply this, so the statement is 'reference only'

HTH
-jack




Lisa said:
I am using Access 2002. I created two tables and want to join them on three
fields. All text fields. Two fields from each table match (ie. Tbl1 = ABC
and Tbl2 = ABC). The third field from Tbl1 contains part of the data that I
need but doesn't exactly match the corresponding field in Tbl2 (ie. Tbl1 =
AB&xxx.LXz and Tbl2 = AB). How can I join all three fields to get an
accurate output? I'm new at this! Thanks :)
Click to expand...
Click to expand...
 
Sorry, I'm no expert with queries by any means.

Another string evaluation would be the Instr() function, but you've probably
already thought of this.

I would think that wildcards would have done it. You tried using two
wildcards in the statement ( Like *AB* )? That should (<- disclaimer,
should) match up any field where AB is found, no matter where it is.

Unfortunately, thats the best I can do. Might have to wait for someone who
actually knows what they're talking about....
Lisa said:
Hey Jack...thanks for the respone.

No...it could be anywhere within the string of letters / characters. I'm
trying to join these fields in order to run a query that brings together data
from both tables. I've tried running the query with Like and wildcard but
the results aren't correct because these two fields aren't equal. So,
inTbl1, I want the query to provide product, mktg division, part number and
options (which is the AB&xxx.LXz field). In Tbl2, I want the query to
provide vehicle code, vehicle name and engine (which is the AB field). The
options field in Tbl1 (AB&xxx.LXz) contains the engine data (AB) from Tbl2.
There could be several engines listed in the options field in Tbl1 but only
one engine listed in Tbl2. Therefore, I need all of the records returned
from Tbl1 but based on the engine data from Tbl2.

I thought maybe a Union Query but don't know the sql language to use. I'm
not sure if I can join these fields at this point.
Thanks.
Lisa

Dymondjack said:
Is it always the first two letters in Tbl1 that match tbl2? If so, try
something like this:


WHERE mid([Tbl1.Field], 1, 2) = [Tbl2.Field])

The Mid function above would select only the first two characters of Tbl1
and try to match them with Tbl2. The Like comparison and wildcard may also
work. Don't take the syntax above for granted... I'm not sure where you are
trying to apply this, so the statement is 'reference only'

HTH
-jack




Lisa said:
I am using Access 2002. I created two tables and want to join them on three
fields. All text fields. Two fields from each table match (ie. Tbl1 = ABC
and Tbl2 = ABC). The third field from Tbl1 contains part of the data that I
need but doesn't exactly match the corresponding field in Tbl2 (ie. Tbl1 =
AB&xxx.LXz and Tbl2 = AB). How can I join all three fields to get an
accurate output? I'm new at this! Thanks :)
Click to expand...
Click to expand...
Click to expand...
 
Hey Jack...thanks for the respone.

No...it could be anywhere within the string of letters / characters. I'm
trying to join these fields in order to run a query that brings together data
from both tables. I've tried running the query with Like and wildcard but
the results aren't correct because these two fields aren't equal. So,
inTbl1, I want the query to provide product, mktg division, part number and
options (which is the AB&xxx.LXz field). In Tbl2, I want the query to
provide vehicle code, vehicle name and engine (which is the AB field). The
options field in Tbl1 (AB&xxx.LXz) contains the engine data (AB) from Tbl2.
There could be several engines listed in the options field in Tbl1 but only
one engine listed in Tbl2. Therefore, I need all of the records returned
from Tbl1 but based on the engine data from Tbl2.
Click to expand...

Well, you're paying the penalty for violating the basic principle that fields
should be atomic - containing only one piece of data. Your Options field
evidently contains multiple pieces, and not even in any defined order! OUCH!

Could you post some actual (rather than ABC) examples of the fields you're
trying to match, and indicate what substring or the composite field should be
matched? What if the short string occurs "accidentally" inside the options
string (i.e. you think you're looking for Engine but the vehicle name happens
to also contain AB)?
 
Hey John,

The data I work with is confidential but I will try to create an example for
you.

Tbl 1 = Part Number Table: Fields are Part # (ie. 25087607) Product (ie.
02), Mktg Div (ie. 8E), Options (ie. LMO/LRP&MZ6-T43)

Tbl2 = Vehicle Program Codes Table: Fields are Vehicle Code Name (ie.
HT3-000), Mktg Div (ie. 8E), Product (ie. 02), Vehicle Model Name (Honda
Civic) and Engine Code (ie. LMO).

I'm trying to determine what vehicle programs correspond to each part
number. The engine code along with the Product and Mktg codes connects the
two together. By joining the tables on Product and Mktg Div, you can see
that these codes are the same. But I need the engine codes to match to pull
accurate vehicle data. The problem is the Engine Code in Tbl2 exists in the
Options field but doesn't match exactly because of the string of other codes.
Plus, there could be other engine codes in that field as well. (The data in
table 1 is generated by a corporate database that I download and then import
into access.)

I want to be able to output: Part #25087607 goes on Product 02, Mkt Div. 8E,
Vehicle Code HT3-000, Vehicle Name Honda Civic and has the following Options
LMO/LRP&MZ6-T43. This information gives the end user the required
information.

Maybe I'm going about this the wrong way. I'm not sure. Thanks for any
input.
Lisa
 
Hey John,

The data I work with is confidential but I will try to create an example for
you.

Tbl 1 = Part Number Table: Fields are Part # (ie. 25087607) Product (ie.
02), Mktg Div (ie. 8E), Options (ie. LMO/LRP&MZ6-T43)

Tbl2 = Vehicle Program Codes Table: Fields are Vehicle Code Name (ie.
HT3-000), Mktg Div (ie. 8E), Product (ie. 02), Vehicle Model Name (Honda
Civic) and Engine Code (ie. LMO).

I'm trying to determine what vehicle programs correspond to each part
number. The engine code along with the Product and Mktg codes connects the
two together. By joining the tables on Product and Mktg Div, you can see
that these codes are the same. But I need the engine codes to match to pull
accurate vehicle data. The problem is the Engine Code in Tbl2 exists in the
Options field but doesn't match exactly because of the string of other codes.
Plus, there could be other engine codes in that field as well. (The data in
table 1 is generated by a corporate database that I download and then import
into access.)

I want to be able to output: Part #25087607 goes on Product 02, Mkt Div. 8E,
Vehicle Code HT3-000, Vehicle Name Honda Civic and has the following Options
LMO/LRP&MZ6-T43. This information gives the end user the required
information.

Maybe I'm going about this the wrong way. I'm not sure. Thanks for any
input.
Click to expand...

I'm guessing that the corporate database is going about it in the wrong way
(at least in how they export the data), and you're having to make the best of
a bad situation.

I'd create a query joining the two tables on Product and Mktg Div, and adding
a criterion on Options of

LIKE "*" & [Tbl2].[Engine Code] & "*"

This may get some "false drops" if the text string LMO occurs adventitiously
elsewhere in the options string, but it should at least winnow down the field.

If, however, you can come up with some way to predict where in the Options
string to look, you can do better. I see LMO/ in your example - will the
engine codes always come before a / in the field?
 
Hi John,

I think you've got it! :) I tried your suggestion. In the criteria field of
the Engine Code field, I added the actual Engine Codes I was trying to match
with the Option Codes field using LIKE, OR to narrow down my results. After
reviewing the output, it looks like I'm going to get the results I wanted!
Thanks so much for your help!
Lisa

John W. Vinson said:
Hey John,

The data I work with is confidential but I will try to create an example for
you.

Tbl 1 = Part Number Table: Fields are Part # (ie. 25087607) Product (ie.
02), Mktg Div (ie. 8E), Options (ie. LMO/LRP&MZ6-T43)

Tbl2 = Vehicle Program Codes Table: Fields are Vehicle Code Name (ie.
HT3-000), Mktg Div (ie. 8E), Product (ie. 02), Vehicle Model Name (Honda
Civic) and Engine Code (ie. LMO).

I'm trying to determine what vehicle programs correspond to each part
number. The engine code along with the Product and Mktg codes connects the
two together. By joining the tables on Product and Mktg Div, you can see
that these codes are the same. But I need the engine codes to match to pull
accurate vehicle data. The problem is the Engine Code in Tbl2 exists in the
Options field but doesn't match exactly because of the string of other codes.
Plus, there could be other engine codes in that field as well. (The data in
table 1 is generated by a corporate database that I download and then import
into access.)

I want to be able to output: Part #25087607 goes on Product 02, Mkt Div. 8E,
Vehicle Code HT3-000, Vehicle Name Honda Civic and has the following Options
LMO/LRP&MZ6-T43. This information gives the end user the required
information.

Maybe I'm going about this the wrong way. I'm not sure. Thanks for any
input.
Click to expand...

I'm guessing that the corporate database is going about it in the wrong way
(at least in how they export the data), and you're having to make the best of
a bad situation.

I'd create a query joining the two tables on Product and Mktg Div, and adding
a criterion on Options of

LIKE "*" & [Tbl2].[Engine Code] & "*"

This may get some "false drops" if the text string LMO occurs adventitiously
elsewhere in the options string, but it should at least winnow down the field.

If, however, you can come up with some way to predict where in the Options
string to look, you can do better. I see LMO/ in your example - will the
engine codes always come before a / in the field?
Click to expand...
 
Back
Top