SQL clause

[confused!!]

I would like to eliminate the repetition of results some how. Just don't
know what criteria to use or how to phrase the SQL stmt.

SELECT JDE.USERNAME, Ducar.UserName
FROM JDE, Ducar
WHERE (((Ducar.UserName) Like ([JDE].[USERNAME]) & "*"))

This is the SQL statement. However for every DUCAR username Allen W. and
Allen S. the result returns JDE usernames with Allen... and everything
following it. Each name Allen S and W produce the same number of results.

What criteria can i use in order to further restrict the AllenW to only
Allen W and Allen S to Allen S?

 

[Dirk Goldgar]

I would like to eliminate the repetition of results some how. Just don't
know what criteria to use or how to phrase the SQL stmt.

SELECT JDE.USERNAME, Ducar.UserName
FROM JDE, Ducar
WHERE (((Ducar.UserName) Like ([JDE].[USERNAME]) & "*"))

This is the SQL statement. However for every DUCAR username Allen W. and
Allen S. the result returns JDE usernames with Allen... and everything
following it. Each name Allen S and W produce the same number of results.

What criteria can i use in order to further restrict the AllenW to only
Allen W and Allen S to Allen S?

What are the exact values of the UserName fields in each table, which are
supposedly matching when they shouldn't? If JDE.USERNAME = "Allen", then
your statement above *should* match it with Ducar "Allen W" and "Allen S".

 

[confused!!]

JDE usernames = Allen and AllenW
DUCAR usernames = Allen, William and Allen, Wynter

the results appear as follows:
Allen - Allen, William
Allen - Allen, Wynter
AllenW-Allen, Wynter

only AllenW belongs to Allen, Wynter, but see how the stmt resulted in ALLEN
- Allen, Wynter.

That's what I need further restrictions on. Did I answer your question?

I would like to eliminate the repetition of results some how. Just don't
know what criteria to use or how to phrase the SQL stmt.

SELECT JDE.USERNAME, Ducar.UserName
FROM JDE, Ducar
WHERE (((Ducar.UserName) Like ([JDE].[USERNAME]) & "*"))

This is the SQL statement. However for every DUCAR username Allen W. and
Allen S. the result returns JDE usernames with Allen... and everything
following it. Each name Allen S and W produce the same number of results.

What criteria can i use in order to further restrict the AllenW to only
Allen W and Allen S to Allen S?

What are the exact values of the UserName fields in each table, which are
supposedly matching when they shouldn't? If JDE.USERNAME = "Allen", then
your statement above *should* match it with Ducar "Allen W" and "Allen S".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

JDE usernames = Allen and AllenW
DUCAR usernames = Allen, William and Allen, Wynter

the results appear as follows:
Allen - Allen, William
Allen - Allen, Wynter
AllenW-Allen, Wynter

only AllenW belongs to Allen, Wynter, but see how the stmt resulted in
ALLEN
- Allen, Wynter.

That's what I need further restrictions on. Did I answer your question?

Yes, but there's a problem with your response. Given the data you gave me
and the SQL you posted, this result:

AllenW-Allen, Wynter

.... is not possible. There is no way the SQL statement can match 'AllenW'
to 'Allen, Wynter', if those are the values of fields being matched on.

So either your reporting is wrong, or there's something you're not telling
me about the actual contents of the tables.

 

[confused!!]

You are correct

JDE usernames = ALLEN and ALLENW
DUCAR usernames = ALLEN and ALLENW
which returns
ALLEN - ALLEN
ALLEN - ALLENW
ALLENW-ALLENW

Sorry about that

 

[Dirk Goldgar]

You are correct

JDE usernames = ALLEN and ALLENW
DUCAR usernames = ALLEN and ALLENW
which returns
ALLEN - ALLEN
ALLEN - ALLENW
ALLENW-ALLENW

So then, what is it you want to match? If you want to end up with

JDE Ducar
----------- --------------
ALLEN ALLEN
ALLENW ALLENW

then why are you using the Like operator in your SQL statement? Why aren't
you just joining on equality; e.g.,

SELECT JDE.USERNAME, Ducar.UserName
FROM JDE INNER JOIN Ducar
ON Ducar.UserName = [JDE].[USERNAME]

?

If you *don't* want to match on equality, maybe you've just got the pattern
reversed. Did you want to end up with

JDE Ducar
----------- --------------
ALLEN ALLEN
ALLEN ALLENW
ALLENW ALLENW

?

 

[confused!!]

Because there are instance where
JDE username = Moton, Moton2, Moton3
DUCAR username = Moton

Therefore I need to match DUCAR username = Moton to ALL JDE usernames Moton,
Moton2, Moton3.

There are the exceptions like in the Allen instances where there is no
numeric value but instead an alpha value.

You are correct

JDE usernames = ALLEN and ALLENW
DUCAR usernames = ALLEN and ALLENW
which returns
ALLEN - ALLEN
ALLEN - ALLENW
ALLENW-ALLENW

So then, what is it you want to match? If you want to end up with

JDE Ducar
----------- --------------
ALLEN ALLEN
ALLENW ALLENW

then why are you using the Like operator in your SQL statement? Why aren't
you just joining on equality; e.g.,

SELECT JDE.USERNAME, Ducar.UserName
FROM JDE INNER JOIN Ducar
ON Ducar.UserName = [JDE].[USERNAME]

?

If you *don't* want to match on equality, maybe you've just got the pattern
reversed. Did you want to end up with

JDE Ducar
----------- --------------
ALLEN ALLEN
ALLEN ALLENW
ALLENW ALLENW

?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Because there are instance where
JDE username = Moton, Moton2, Moton3
DUCAR username = Moton

Therefore I need to match DUCAR username = Moton to ALL JDE usernames
Moton,
Moton2, Moton3.

There are the exceptions like in the Allen instances where there is no
numeric value but instead an alpha value.

I wish you had explained all this in your original post. It would have
saved a lot of time. Is there anything else I ought to know?

*IF* I now understand you correctly, this SQL statement may give you what
you want:

SELECT JDE.UserName, Ducar.UserName
FROM JDE, Ducar
WHERE
IIf(JDE.UserName Like "*#",
JDE.UserName Like Ducar.UserName & "*",
JDE.UserName = Ducar.UserName);

Note that I have reversed the direction of the pattern-matching. That
corresponds to what you are saying now, but contradicts what you originally
said.