how to CORRECTLY query ONE item against SEVERAL possible values

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

Guest

(NOTE: I am using the XP version of Access...)

Here's my situation: i have two tables...

TABLE ONE: a RECOMMENDED guideline

For example:
Start Point, End Point, NextPoint

TABLE TWO: the ACTUAL movements used, showing me the same type of items as
the RECOMMENDED table

Assuming that my "Start Point" and "Next Point" on both tables are EQUAL,
I'm trying to write a query that returns all records on the second table (of
the ACTUAL movements) where the "End Point" does not match ANY of the
possible "End Points" listed on table one.

I don't want to return a list where it compares ONE:ONE... does anyone know
how i can compare ONE: SEVERAL?
 
(NOTE: I am using the XP version of Access...)

Here's my situation: i have two tables...

TABLE ONE: a RECOMMENDED guideline

For example:
Start Point, End Point, NextPoint

TABLE TWO: the ACTUAL movements used, showing me the same type of items as
the RECOMMENDED table

Assuming that my "Start Point" and "Next Point" on both tables are EQUAL,
I'm trying to write a query that returns all records on the second table (of
the ACTUAL movements) where the "End Point" does not match ANY of the
possible "End Points" listed on table one.

I don't want to return a list where it compares ONE:ONE... does anyone know
how i can compare ONE: SEVERAL?
Click to expand...

A "unmatched" self join query will do the trick here. This will find
all records in the table where the end point in TableTwo is not found
in any record (whatever the start or next points) in TableOne:

SELECT [Start Point], [End Point], [Next Point]
FROM [TableTwo] LEFT JOIN [TableOne]
ON [TableTwo].[End Point] = [TableOne].[End Point]
WHERE [TableOne].[End Point] IS NULL;

This "frustrated outer join" query returns all records in TableTwo,
and any records with matching End Point in TableOne; it then rejects
those records which *do* have a match, leaving only those which do
not.

John W. Vinson[MVP]
 
Thanks for the suggestion. I believe that the SQL code you recommended gives
me the same result as the "Find Unmatched Query Wizard". I'm going to try
and provide a specific example of what I have and what I'm aiming for in the
hopes that it will be clearer of what I'm trying to yield.

Table 1: ACCEPTABLE moves

START NEXT END
51 3 3
51 3 9
51 3 37
51 3 46

Table 2: ACTUAL moves (only 2 were made)

START NEXT END
51 3 3
51 3 3

USING QUERY, THE RESULT:

START NEXT END
51 3 9
51 3 46
51 3 37
51 3 37
51 3 9
51 3 46

The DESIRED result:
START NEXT END
(none) (none) (none)

Given the ORG and NEXT, I want to return the information from Table 2
(ACTUAL) if -and only if- the DST is NOT found in TABLE 1(ACCEPTABLE). In
the example that I provided above, that means that b/c Table 2's info is one
of the DSTs in Table, the query should return nothing.

Is there a way to do that???

John Vinson said:
(NOTE: I am using the XP version of Access...)

Here's my situation: i have two tables...

TABLE ONE: a RECOMMENDED guideline

For example:
Start Point, End Point, NextPoint

TABLE TWO: the ACTUAL movements used, showing me the same type of items as
the RECOMMENDED table

Assuming that my "Start Point" and "Next Point" on both tables are EQUAL,
I'm trying to write a query that returns all records on the second table (of
the ACTUAL movements) where the "End Point" does not match ANY of the
possible "End Points" listed on table one.

I don't want to return a list where it compares ONE:ONE... does anyone know
how i can compare ONE: SEVERAL?
Click to expand...

A "unmatched" self join query will do the trick here. This will find
all records in the table where the end point in TableTwo is not found
in any record (whatever the start or next points) in TableOne:

SELECT [Start Point], [End Point], [Next Point]
FROM [TableTwo] LEFT JOIN [TableOne]
ON [TableTwo].[End Point] = [TableOne].[End Point]
WHERE [TableOne].[End Point] IS NULL;

This "frustrated outer join" query returns all records in TableTwo,
and any records with matching End Point in TableOne; it then rejects
those records which *do* have a match, leaving only those which do
not.

John W. Vinson[MVP]
Click to expand...
 
Back
Top