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

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" wrote:

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" wrote:

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 :)
.

Relevant Pages

  • RE: Join two tables with 3 fields; 1 field doesnt match exactly
    ... Another string evaluation would be the Instrfunction, ... trying to join these fields in order to run a query that brings together data ... In Tbl2, ... There could be several engines listed in the options field in Tbl1 but only ...
    (microsoft.public.access.modulesdaovba)
  • Re: Deleting Unmatched records
    ... I have created an unmatching query, using the wizard, on tbl2 with ... comparison to tbl1 to filter out which records in tbl1 no longer ... SQL View of query: ... PRIMARY KEY (Key1) ...
    (microsoft.public.access.queries)
  • Re: Combine Tables
    ... Tbl1 is single field: ... Tbl2 has 2 fields: ... that determines where, the dash is. ... THEN use the query and tbl2 to create another query that joins ...
    (comp.databases.ms-access)
  • Re: sequence for tickets
    ... Create a table of number (tblNums) with a single, ... FROM tbl1, tblNums ... You can convert this to a make table or append query to get your records ... my output table is tbl2 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Truncated Memo field displayed on form
    ... 255 characters in any column that is in the combo box's RowSource query. ... My fields down to "Knowledge and Understandings" are fine. ... occurs with the "descriptor" field being truncated on my form. ...
    (microsoft.public.access.forms)
Loading