RE: filter for values in another table

"2. The other way is to use the IN Clause in your SQL statement. It would
look like:

SELECT TableA.*
FROM TableA
WHERE TableA.ID NOT IN (SELECT DISTINCT ID FROM TableB)"

Would I just enter all of the ids into the parenthesis? or would i create a
select statement for each id that i want to exclude from the results?



"Dale Fye" wrote:

Sure,

There are a couple of ways to do this.

1. The first, and probably fastest with regards to how quickly your query
runs is to create a query that some people call a "frustrated outer join".
In this type of query, you are looking for those records in one table that
are not in another. The syntax looks something like:

SELECT TableA.*
FROM TableA LEFT JOIN TableB
ON TableA.ID = TableB.ID
WHERE TableB.ID IS NULL

To create this query in the query grid, bring both tables A and B into a new
query.
Create a join between the ID fields of the two tables, then right click on
the join line and select Join Properties. In the dialog that pops up, select
the option that say "Include ALL records FROM TableA and only those records
from TableB where the joined fields are equal". I know this may seem counter
intuitive based on the description, but trust me.

Drag the fields you want from TableA, and the ID field from TableB into the
query grid. If you run the query now, the column corresponding to the
TableB.ID will contain some values, and other NULL values. If you want the
records where the ID is in A and not in B, then in the criteria row for
TableB.ID, type "Is NULL" (without the quotes).

2. The other way is to use the IN Clause in your SQL statement. It would
look like:

SELECT TableA.*
FROM TableA
WHERE TableA.ID NOT IN (SELECT DISTINCT ID FROM TableB)

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Qaspec" wrote:

In my query i have records that contain a field named "id" from all the
records in table a. In table b i have a list of ids that i would like to
exclude from the list of records that return from table a in my query. Can i
set the filter to look for the values in my table instead of typing out 50
ids in the criteria section? How would that be done?
.

Relevant Pages

  • Re: Problem with Dual subselect
    ... So I've constructed a UNION QUERY as follows. ... FROM TABLEA AS A ... B.STATUS_DATE FROM [TABLEB] As B ... A Lookup property on the foreign-key field ...
    (microsoft.public.access.queries)
  • Re: use a result as a FIELD in the design grid
    ... I have a key TableA that maps each station's flow (and ... Station: FlowField: TankLevel: PumpRun: ... OK, now from the previous query, I know that the FlowField ... So in a new query grid where TableA is linked to TableB ...
    (microsoft.public.access.queries)
  • Re: Advanced query issue
    ... and then write a query linking just by your new ... FROM tablea As a RIGHT JOIN tableb as b ... That assumes tableb has all the possible occurrence of the two ... I also used UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Finding matching records
    ... >> An inner join in a query will return only records where the join ... Suppose you have two tables, TableA and TableB, with these fields: ... A_ShouldMatch has a match in the B_ShouldMatch field in TableB. ... create a new query in Design View and add both TableA and TableB to the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help with INSERT
    ... If tableA has three records, a, b, and c, while tableB as four records, 1, ... from a SELECT query, no obligation to necessary use a table). ... > I'm very new to Access, and trying to learn SQL at the same time. ...
    (microsoft.public.access.queries)