Using Multiple Fields to Find Unmatched Records

C

Chris

Hi, I have two tables, where the child table has two primary keys to link it
to the parent table. I'm trying to create an unmatched query where it will
find any records on the parent table that don't have corresponding rows on
the child table, but I'm stuck. Does anyone have some example SQL?

Thanks,
Chris
 
K

KARL DEWEY

Try this --

SELECT [Change Requests].[Date open], [Change Requests].[Date close],
[Change Requests].x, [Change Requests].y
FROM [Change Requests] INNER JOIN [Change Requet-1] ON ([Change
Requests].[Date close] = [Change Requet-1].[Date close]) AND ([Change
Requests].[Date open] = [Change Requet-1].[Date open])
WHERE ((([Change Requests].y)<>[Change Requet-1].[y])) OR ((([Change
Requests].x)<>[Change Requet-1].[x]));
 
C

Chris

Hi Karl,

I tried the following code and my query's still producting no results... Any
thoughts?

SELECT [Fund Table].*
FROM [Fund Table] INNER JOIN [Client Fund Table] ON ([Fund Table].[IM
Number]=[Client Fund Table].[IM Number]) AND ([Fund Table].[IM Fund]=[Client
Fund Table].[IM Fund])
WHERE ((([Client Fund Table].[IM Number]) Is Null) Or (([Client Fund
Table].[IM Fund]) Is Null));


KARL DEWEY said:
Try this --

SELECT [Change Requests].[Date open], [Change Requests].[Date close],
[Change Requests].x, [Change Requests].y
FROM [Change Requests] INNER JOIN [Change Requet-1] ON ([Change
Requests].[Date close] = [Change Requet-1].[Date close]) AND ([Change
Requests].[Date open] = [Change Requet-1].[Date open])
WHERE ((([Change Requests].y)<>[Change Requet-1].[y])) OR ((([Change
Requests].x)<>[Change Requet-1].[x]));

--
KARL DEWEY
Build a little - Test a little


Chris said:
Hi, I have two tables, where the child table has two primary keys to link it
to the parent table. I'm trying to create an unmatched query where it will
find any records on the parent table that don't have corresponding rows on
the child table, but I'm stuck. Does anyone have some example SQL?

Thanks,
Chris
Click to expand...
Click to expand...
 
K

KARL DEWEY

You said you were looking for no match so the fields are not null.
SELECT [Fund Table].*
FROM [Fund Table] INNER JOIN [Client Fund Table] ON ([Fund Table].[IM
Number]=[Client Fund Table].[IM Number]) AND ([Fund Table].[IM Fund]=[Client
Fund Table].[IM Fund])
WHERE ([Client Fund Table].[IM Number] <>[Fund Table].[IM Number]) Or
([Client Fund Table].[IM Fund]) <>[Fund Table].[IM Fund]);

--
KARL DEWEY
Build a little - Test a little


Chris said:
Hi Karl,

I tried the following code and my query's still producting no results... Any
thoughts?

SELECT [Fund Table].*
FROM [Fund Table] INNER JOIN [Client Fund Table] ON ([Fund Table].[IM
Number]=[Client Fund Table].[IM Number]) AND ([Fund Table].[IM Fund]=[Client
Fund Table].[IM Fund])
WHERE ((([Client Fund Table].[IM Number]) Is Null) Or (([Client Fund
Table].[IM Fund]) Is Null));


KARL DEWEY said:
Try this --

SELECT [Change Requests].[Date open], [Change Requests].[Date close],
[Change Requests].x, [Change Requests].y
FROM [Change Requests] INNER JOIN [Change Requet-1] ON ([Change
Requests].[Date close] = [Change Requet-1].[Date close]) AND ([Change
Requests].[Date open] = [Change Requet-1].[Date open])
WHERE ((([Change Requests].y)<>[Change Requet-1].[y])) OR ((([Change
Requests].x)<>[Change Requet-1].[x]));

--
KARL DEWEY
Build a little - Test a little


Chris said:
Hi, I have two tables, where the child table has two primary keys to link it
to the parent table. I'm trying to create an unmatched query where it will
find any records on the parent table that don't have corresponding rows on
the child table, but I'm stuck. Does anyone have some example SQL?

Thanks,
Chris
Click to expand...
Click to expand...
Click to expand...
 
C

Chris

Hi Karl,

I'm not sure what I'm doing wrong, but I'm still getting no results. Maybe
I'm not explaining it well? On the parent table (Fund Table), there are
several thousand rows, each with a unique IM Number/IM Fund combination. The
Client Fund Table stores several thousand rows, identifying an IM Number/IM
Fund combination by client (but several clients have the same IM Number/IM
Fund combination). What I'm trying to find is the IM Number/IM Fund
combinations from the Fund Table that no clients have. Does that make sense?

KARL DEWEY said:
You said you were looking for no match so the fields are not null.
SELECT [Fund Table].*
FROM [Fund Table] INNER JOIN [Client Fund Table] ON ([Fund Table].[IM
Number]=[Client Fund Table].[IM Number]) AND ([Fund Table].[IM Fund]=[Client
Fund Table].[IM Fund])
WHERE ([Client Fund Table].[IM Number] <>[Fund Table].[IM Number]) Or
([Client Fund Table].[IM Fund]) <>[Fund Table].[IM Fund]);

--
KARL DEWEY
Build a little - Test a little


Chris said:
Hi Karl,

I tried the following code and my query's still producting no results... Any
thoughts?

SELECT [Fund Table].*
FROM [Fund Table] INNER JOIN [Client Fund Table] ON ([Fund Table].[IM
Number]=[Client Fund Table].[IM Number]) AND ([Fund Table].[IM Fund]=[Client
Fund Table].[IM Fund])
WHERE ((([Client Fund Table].[IM Number]) Is Null) Or (([Client Fund
Table].[IM Fund]) Is Null));


KARL DEWEY said:
Try this --

SELECT [Change Requests].[Date open], [Change Requests].[Date close],
[Change Requests].x, [Change Requests].y
FROM [Change Requests] INNER JOIN [Change Requet-1] ON ([Change
Requests].[Date close] = [Change Requet-1].[Date close]) AND ([Change
Requests].[Date open] = [Change Requet-1].[Date open])
WHERE ((([Change Requests].y)<>[Change Requet-1].[y])) OR ((([Change
Requests].x)<>[Change Requet-1].[x]));

--
KARL DEWEY
Build a little - Test a little


:

Hi, I have two tables, where the child table has two primary keys to link it
to the parent table. I'm trying to create an unmatched query where it will
find any records on the parent table that don't have corresponding rows on
the child table, but I'm stuck. Does anyone have some example SQL?

Thanks,
Chris
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Pardon me, but I think your query would look like the following. Notice that
I've changed the join from an INNER JOIN to a LEFT JOIN.

SELECT [Fund Table].*
FROM [Fund Table] LEFT JOIN [Client Fund Table]
ON ([Fund Table].[IM Number]=[Client Fund Table].[IM Number])
AND ([Fund Table].[IM Fund]=[Client Fund Table].[IM Fund])
WHERE [Client Fund Table].[IM Number]) Is Null



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hi Karl,

I'm not sure what I'm doing wrong, but I'm still getting no results. Maybe
I'm not explaining it well? On the parent table (Fund Table), there are
several thousand rows, each with a unique IM Number/IM Fund combination. The
Client Fund Table stores several thousand rows, identifying an IM Number/IM
Fund combination by client (but several clients have the same IM Number/IM
Fund combination). What I'm trying to find is the IM Number/IM Fund
combinations from the Fund Table that no clients have. Does that make sense?

KARL DEWEY said:
You said you were looking for no match so the fields are not null.
SELECT [Fund Table].*
FROM [Fund Table] INNER JOIN [Client Fund Table] ON ([Fund Table].[IM
Number]=[Client Fund Table].[IM Number]) AND ([Fund Table].[IM Fund]=[Client
Fund Table].[IM Fund])
WHERE ([Client Fund Table].[IM Number] <>[Fund Table].[IM Number]) Or
([Client Fund Table].[IM Fund]) <>[Fund Table].[IM Fund]);

--
KARL DEWEY
Build a little - Test a little


Chris said:
Hi Karl,

I tried the following code and my query's still producting no results... Any
thoughts?

SELECT [Fund Table].*
FROM [Fund Table] INNER JOIN [Client Fund Table] ON ([Fund Table].[IM
Number]=[Client Fund Table].[IM Number]) AND ([Fund Table].[IM Fund]=[Client
Fund Table].[IM Fund])
WHERE ((([Client Fund Table].[IM Number]) Is Null) Or (([Client Fund
Table].[IM Fund]) Is Null));


:

Try this --

SELECT [Change Requests].[Date open], [Change Requests].[Date close],
[Change Requests].x, [Change Requests].y
FROM [Change Requests] INNER JOIN [Change Requet-1] ON ([Change
Requests].[Date close] = [Change Requet-1].[Date close]) AND ([Change
Requests].[Date open] = [Change Requet-1].[Date open])
WHERE ((([Change Requests].y)<>[Change Requet-1].[y])) OR ((([Change
Requests].x)<>[Change Requet-1].[x]));

--
KARL DEWEY
Build a little - Test a little


:

Hi, I have two tables, where the child table has two primary keys to link it
to the parent table. I'm trying to create an unmatched query where it will
find any records on the parent table that don't have corresponding rows on
the child table, but I'm stuck. Does anyone have some example SQL?

Thanks,
Chris
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

unmatched record query question 2
Help! Unmatched Data Query 1
Unmatched query question 3
compare and unmatched 8
delete parent records where child fields are blank 2
Joins in unmatched query 2
NOT IN (SQL) or Find Unmatched (+ other stuff) 21
Find unmatched query with selected date 2

Top