If you are joining on the fields, then you won't get any records that are a
partial match. You can use a non-equi join.
SELECT original_missing.field1, original_missing.field2,
original_missing.field3, original_missing.field4, [27049].reference_num,
original_missing.field6, [27049].participation_date, original_missing.field8,
original_missing.field9, original_missing.field10, original_missing.field11,
original_missing.field12, original_missing.field13, [27049].program_status,
original_missing.field15, original_missing.field16, original_missing.field17,
original_missing.field18, original_missing.field19, original_missing.field20,
original_missing.field21, original_missing.field22, original_missing.field23
FROM original_missing INNER JOIN 27049
ON original_missing.field2 = 27049.loan_number OR
Right(original_missing.field2,6) = Right(27049.loan_number,6)
OR DROP the JOIN
SELECT original_missing.field1, original_missing.field2,
original_missing.field3, original_missing.field4, [27049].reference_num,
original_missing.field6, [27049].participation_date, original_missing.field8,
original_missing.field9, original_missing.field10, original_missing.field11,
original_missing.field12, original_missing.field13, [27049].program_status,
original_missing.field15, original_missing.field16, original_missing.field17,
original_missing.field18, original_missing.field19, original_missing.field20,
original_missing.field21, original_missing.field22, original_missing.field23
FROM original_missing , 27049
WHERE ((original_missing.field2 = 27049.loan_number) OR
(Right(original_missing.field2,6) = Right(27049.loan_number,6)));
i have a list of numbers in one table (along with other columns), and another
list of numbers (and other columns) in another table. i want to design a
query that pulls these fields together where the one id number and the other
are equal or one is equal to the right 6 numbers of the other (numbers in the
one table MAY have a "prefix" of 0100 or 0300 before the id number) my
example query is below. Thanks in advance for any help you can give me.
SELECT original_missing.field1, original_missing.field2,
original_missing.field3, original_missing.field4, [27049].reference_num,
original_missing.field6, [27049].participation_date, original_missing.field8,
original_missing.field9, original_missing.field10, original_missing.field11,
original_missing.field12, original_missing.field13, [27049].program_status,
original_missing.field15, original_missing.field16, original_missing.field17,
original_missing.field18, original_missing.field19, original_missing.field20,
original_missing.field21, original_missing.field22, original_missing.field23
FROM original_missing INNER JOIN 27049 ON original_missing.field2 =
[27049].loan_number
WHERE ((original_missing.field2 = 27049.loan_number) OR
(Right(original_missing.field2,6) = Right(27049.loan_number,6)));
