need help..(WHERE.....OR (Right(original_missing.field2,6) = Righ.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)));
 
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)));
Click to expand...
 
it's saying that i have a syntax error when i go to save either of those
queries. the first is in the from statement the second in the where statement.

John Spencer (MVP) said:
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)));
Click to expand...
Click to expand...
 
Try surrounding the tablename (27049) with square brackets. Other than that I
don't see where I missed something.
it's saying that i have a syntax error when i go to save either of those
queries. the first is in the from statement the second in the where statement.

John Spencer (MVP) said:
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)));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top