RE: filter for values in another table

You could do it either way, if you have a short list, that won't change,
typing in the IDs that you want to exclude would work. If they are numeric,
it would look like:

WHERE TableA.ID NOT IN(1,3, 5, 9)

If they are string values, it would look like:

WHERE TableA.ID NOT IN("1", "3", "5", "9")

But you indicated that you wanted to exclude all of the values where the ID
was in TableB, so I would just create the SELECT statement. Actually, I
prefer method 1 for speed reasons. With method 2, Access has to process the
select statement, eliminate the duplicates (DISTINCT), and then process the
IN clause.

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

email address is invalid
Please reply to newsgroup only.



"Qaspec" wrote:

"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: filter for values in another table
    ... runs is to create a query that some people call a "frustrated outer join". ... FROM TableA LEFT JOIN TableB ... In table b i have a list of ids that i would like to ...
    (microsoft.public.access.queries)
  • RE: Has any Informix DBA had to do the following?
    ... I've never had that problem myself using IDS, granted then I was using c and esql/c for the connections so I knew every piece of code and had no thread issues. ... Disk access - busy disks but no predominant ... duration of the query. ... Has any Informix DBA had to do the following? ...
    (comp.databases.informix)
  • Re: IF THEN in query..Maybe IIf
    ... assuming that there is a primary key for the existing records in the data ... table, which i'll call TableA, that matches a primary key in the imported ... now turn the query into an Update query. ... if the data file has the person's home ...
    (microsoft.public.access.queries)
  • RE: Help: Cleaning Messy Table Data
    ... My suggestion would be that you have a table of clean data with unique ids ... The second stage would be to run an update query each time to update ... duplicate names with different IDs, and duplicate IDs with duplicate names, ...
    (microsoft.public.access.queries)
  • Re: Limit numbers in primary key
    ... autonumber field per table. ... the same properties as the corresponding field in TableA. ... Add a field or whatever your primary key field is and set it as ... Create a Query. ...
    (microsoft.public.access.gettingstarted)