how to join similar records

C

confused!!

example: two lists contain user ids. NT user id list is unique with only one
per user. JDE user id list is not unique with multiple ids that contain the
one in NT user id list.

How do I match JDE user id list with multiple user id to NT user id list
with only 1 id.

NT has a 1 to 1 relationship
JDE has a 1 to many relationship (if i said that correctly).
 
C

confused!!

an actual example is
NT user id MOTON
JDE user id MOTON, MOTON1, MOTON2, MOTON3
how do i get access to join MOTON - MOTON, MOTON - MOTON1, MOTON - MOTON2
AND MOTON - MOTON3
 
C

confused!!

i don't know if part two of my example was posted, becaus i don't see it,
hope this posts.

a specific example to help clarify,
NT user id MOTON
JDE user id MOTON, MOTON1 MOTON2

how do i query results that matches MOTON to MOTON, MOTON to MOTON1 and
MOTON to MOTON2
 
M

Michel Walsh

SELECT whatever
FROM nt INNER JOIN jde
ON jde.UserID LIKE nt.UserID & "*"



Vanderghast, Access MVP
 
K

KARL DEWEY

NT has a 1 to 1 relationship
JDE has a 1 to many relationship (if i said that correctly).
Not said correctly -- NT id's are unique while JDE has multiple.
SQL
SELECT NT.UserID, NT.xxxx, JDE.xxx, JDE.yyy, JDE.zzz
FROM NT LEFT JOIN JDE ON NT.UserID = JDE.UserID;
In design query place both table in the space above the grid. Click on
NT UserID and drag to JDE UserID field. Double click the line that connects
them. Select "Include all records from 'NT' and only those records from 'JDE'
where the joined fields are equal."
 
C

confused!!

a specific example to help clarify,
NT user id MOTON
JDE user id MOTON, MOTON1 MOTON2

In the case of JDE they user ids are not equal, but contain the NT id. Will
this still work?
 
C

confused!!

OK. based on your instruction this is what i gather it would like using the
actual table and field names.

entered into the criteria field is the statement below.
SELECT[UserName] FROM [tblDUCARGAL] INNER JOIN [tblJDE] ON [tblJDE].[User
ID] LIKE [tblDUCARGAL].[UserName] & "*"

I get a syntex error.

How do i fix it?
 
M

Michel Walsh

You enter the statement in the SQL view.


Vanderghast, Access MVP


confused!! said:
OK. based on your instruction this is what i gather it would like using
the
actual table and field names.

entered into the criteria field is the statement below.
SELECT[UserName] FROM [tblDUCARGAL] INNER JOIN [tblJDE] ON
[tblJDE].[User
ID] LIKE [tblDUCARGAL].[UserName] & "*"

I get a syntex error.

How do i fix it?

Michel Walsh said:
SELECT whatever
FROM nt INNER JOIN jde
ON jde.UserID LIKE nt.UserID & "*"



Vanderghast, Access MVP
Click to expand...
Click to expand...
 
J

John Spencer

That would be the SQL statement. This type of query cannot be built in the
Design view, but must be built in the SQL view.

You can start off in design view and then once you have the query built with
the fields you want and the two tables joined select VIEW: SQL from the menu

Find the clause that looks like
FROM NT INNER JOIN JDE ON JDE.UserID = NT.UserID
and edit it to read
FROM JDE INNER JOIN NT ON JDE.UserID LIKE NT.UserID & "*"

That will match up records where JDE.UserID start with the value of NT.UserID

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

KARL DEWEY

You would paste Michel Walsh's post in query SQL view. One disadvantage I
see with his is that it can not be viewed in design view once it is created.

An alternative is to create a query to join them prior to your query.
Place both tables in the design view window. Drag down only the ID fields
from NT and JDE. In criteria for JDE.ID field put Like NT.ID & "*" and
save as 'NTjoinJDE.'

In your query join NT.ID to NTjoinJDE.NT.ID and NTjoinJDE.JDE.ID to JDE.ID
by clicking and draging.
 
C

confused!!

Thank you for your continued help.

I received this error message. Willyou help me identify where the error is
occuring.

The select statement includes a reseved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

SELECT
FROM tblDUCARGAL INNER JOIN tblJDE ON tblJDE.User ID LIKE
tblDUCARGAL.UserName & "*"

Michel Walsh said:
You enter the statement in the SQL view.


Vanderghast, Access MVP


confused!! said:
OK. based on your instruction this is what i gather it would like using
the
actual table and field names.

entered into the criteria field is the statement below.
SELECT[UserName] FROM [tblDUCARGAL] INNER JOIN [tblJDE] ON
[tblJDE].[User
ID] LIKE [tblDUCARGAL].[UserName] & "*"

I get a syntex error.

How do i fix it?

Michel Walsh said:
SELECT whatever
FROM nt INNER JOIN jde
ON jde.UserID LIKE nt.UserID & "*"



Vanderghast, Access MVP


example: two lists contain user ids. NT user id list is unique with
only
one
per user. JDE user id list is not unique with multiple ids that contain
the
one in NT user id list.

How do I match JDE user id list with multiple user id to NT user id
list
with only 1 id.

NT has a 1 to 1 relationship
JDE has a 1 to many relationship (if i said that correctly).
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

First you didn't list which fields you wanted to see.
Second field and table names need to be surrounded with square brackers if
they contain any characters other the letters, numbers, or the underscore. A
space in a name means the name must be surrounded with square brackets.

SELECT *
FROM tblDUCARGAL INNER JOIN tblJDE ON tblJDE.[User ID] LIKE
tblDUCARGAL.UserName & "*"

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

confused!! said:
Thank you for your continued help.

I received this error message. Willyou help me identify where the error is
occuring.

The select statement includes a reseved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

SELECT
FROM tblDUCARGAL INNER JOIN tblJDE ON tblJDE.User ID LIKE
tblDUCARGAL.UserName & "*"

Michel Walsh said:
You enter the statement in the SQL view.


Vanderghast, Access MVP


confused!! said:
OK. based on your instruction this is what i gather it would like using
the
actual table and field names.

entered into the criteria field is the statement below.
SELECT[UserName] FROM [tblDUCARGAL] INNER JOIN [tblJDE] ON
[tblJDE].[User
ID] LIKE [tblDUCARGAL].[UserName] & "*"

I get a syntex error.

How do i fix it?

:


SELECT whatever
FROM nt INNER JOIN jde
ON jde.UserID LIKE nt.UserID & "*"



Vanderghast, Access MVP


example: two lists contain user ids. NT user id list is unique with
only
one
per user. JDE user id list is not unique with multiple ids that contain
the
one in NT user id list.

How do I match JDE user id list with multiple user id to NT user id
list
with only 1 id.

NT has a 1 to 1 relationship
JDE has a 1 to many relationship (if i said that correctly).
Click to expand...
Click to expand...
Click to expand...
 
C

confused!!

Thank you Karl... I got the query to match all duplicate JDE ids with NT ids
based on your direction below.

Your last statement "In your query join NT.ID to NTjoinJDE.NT.ID and
NTjoinJDE.JDE.ID to JDE.ID" didn't quite produce the results I was hoping.

I began with 44 records. Based on the join I should have a large number
based on the multiple JDE ids. That didn't happen

Here is the SQL view:
SELECT tblDUCARGALJDE.*, NTjoinJDE.*
FROM tblDUCARGALJDE INNER JOIN NTjoinJDE ON (NTjoinJDE.[User ID] =
tblDUCARGALJDE.[User ID]) AND (tblDUCARGALJDE.UserName = NTjoinJDE.UserName);
 
C

confused!!

In playing with the joins I returned the multiple records for the multiple
ids.

This is the SQL view statement. I will look at it in more detail make sure
everything that's there should be.

SELECT tblDUCARGALJDE.*, NTjoinJDE.*
FROM tblDUCARGALJDE INNER JOIN NTjoinJDE ON tblDUCARGALJDE.UserName =
NTjoinJDE.UserName;
 
K

KARL DEWEY

You are missing a table - You have tblDUCARGALJDE but not your "NT" table.
Try this --
SELECT tblDUCARGALJDE.*,
FROM (NT INNER JOIN NTjoinJDE ON NT.ID = NTjoinJDE.NT.ID) LEFT JOIN
NTjoinJDE ON tblDUCARGALJDE.UserName = NTjoinJDE.UserName;

Put your "NT" table in the FROM line above - two places.
 
Top