Show all of one tbl and partial match from another tbl

[Steve_A]

I am trying to show all lines of Sheet1.ER_NUMBER and all partial matches of
AUTHDOR.Field24

I am trying to use this

SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 inner JOIN AUTHDOR ON Sheet1.[ER_NUMBER] like "*" &
AUTHDOR.[Field24] & "*"
WITH OWNERACCESS OPTION;

but it is just giving me both fields are equal?

where did I go wrong?

 

[John Spencer]

Try using a LEFT JOIN instead of an INNER JOIN. That should return all
records in Sheet1 plus any matches in AuthDor.

SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 LEFT JOIN AUTHDOR
ON Sheet1.[ER_NUMBER] like "*" & AUTHDOR.[Field24] & "*"
WITH OWNERACCESS OPTION;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve_A]

Thanks John, that is now showing me everything from Sheet1.ER_NUMBER but it
is not matching everything yet

example

Sheet1.ER_NUMBER has FP100.54 and AUTHDOR.[Field24] has FP100.54/20 but it
wont show.

is the "/" going to be a problem or am I doing this backwards?

Try using a LEFT JOIN instead of an INNER JOIN. That should return all
records in Sheet1 plus any matches in AuthDor.

SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 LEFT JOIN AUTHDOR
ON Sheet1.[ER_NUMBER] like "*" & AUTHDOR.[Field24] & "*"
WITH OWNERACCESS OPTION;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I am trying to show all lines of Sheet1.ER_NUMBER and all partial matches of
AUTHDOR.Field24

I am trying to use this

SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 inner JOIN AUTHDOR ON Sheet1.[ER_NUMBER] like "*" &
AUTHDOR.[Field24] & "*"
WITH OWNERACCESS OPTION;

but it is just giving me both fields are equal?

where did I go wrong?

.

 

[John Spencer]

Looks like your comparison is reversed to me,

FP100.54/20 is like FP100.54* but
FP100.54 is NOT like FP100.54/20*


SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 LEFT JOIN AUTHDOR
ON AUTHDOR.[Field24] like "*" & Sheet1.[ER_NUMBER] & "*"
WITH OWNERACCESS OPTION;

Of course, you MIGHT (unlikely) need to rewrite that to the following.
SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM AUTHDOR RIGHT JOIN Sheet1
ON AUTHDOR.[Field24] like "*" & Sheet1.[ER_NUMBER] & "*"
WITH OWNERACCESS OPTION;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thanks John, that is now showing me everything from Sheet1.ER_NUMBER but it
is not matching everything yet

example

Sheet1.ER_NUMBER has FP100.54 and AUTHDOR.[Field24] has FP100.54/20 but it
wont show.

is the "/" going to be a problem or am I doing this backwards?

Try using a LEFT JOIN instead of an INNER JOIN. That should return all
records in Sheet1 plus any matches in AuthDor.

SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 LEFT JOIN AUTHDOR
ON Sheet1.[ER_NUMBER] like "*" & AUTHDOR.[Field24] & "*"
WITH OWNERACCESS OPTION;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I am trying to show all lines of Sheet1.ER_NUMBER and all partial matches of
AUTHDOR.Field24

I am trying to use this

SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 inner JOIN AUTHDOR ON Sheet1.[ER_NUMBER] like "*" &
AUTHDOR.[Field24] & "*"
WITH OWNERACCESS OPTION;

but it is just giving me both fields are equal?

where did I go wrong?

.

 

[Steve_A]

Thanks John, that got me going, have a great weekend

Looks like your comparison is reversed to me,

FP100.54/20 is like FP100.54* but
FP100.54 is NOT like FP100.54/20*


SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 LEFT JOIN AUTHDOR
ON AUTHDOR.[Field24] like "*" & Sheet1.[ER_NUMBER] & "*"
WITH OWNERACCESS OPTION;

Of course, you MIGHT (unlikely) need to rewrite that to the following.
SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM AUTHDOR RIGHT JOIN Sheet1
ON AUTHDOR.[Field24] like "*" & Sheet1.[ER_NUMBER] & "*"
WITH OWNERACCESS OPTION;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thanks John, that is now showing me everything from Sheet1.ER_NUMBER but it
is not matching everything yet

example

Sheet1.ER_NUMBER has FP100.54 and AUTHDOR.[Field24] has FP100.54/20 but it
wont show.

is the "/" going to be a problem or am I doing this backwards?

Try using a LEFT JOIN instead of an INNER JOIN. That should return all
records in Sheet1 plus any matches in AuthDor.

SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 LEFT JOIN AUTHDOR
ON Sheet1.[ER_NUMBER] like "*" & AUTHDOR.[Field24] & "*"
WITH OWNERACCESS OPTION;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Steve_A wrote:
I am trying to show all lines of Sheet1.ER_NUMBER and all partial matches of
AUTHDOR.Field24

I am trying to use this

SELECT Sheet1.ER_NUMBER, AUTHDOR.[Field24]
FROM Sheet1 inner JOIN AUTHDOR ON Sheet1.[ER_NUMBER] like "*" &
AUTHDOR.[Field24] & "*"
WITH OWNERACCESS OPTION;

but it is just giving me both fields are equal?

where did I go wrong?

.

.