filter for values in another table

[Qaspec]

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?

 

[Dale Fye]

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

 

[Qaspec]

"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]

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]

Dale I'm trying method 1 and I am getting an error message on the ON
statement. Here is my actual SQL

SELECT AAJuly07Data.*
FROM AAJuly07Data LEFT JOIN Marketing

 

[Qaspec]

I forgot the brackets around Created By. This worked -

SELECT AAJuly07Data.[Created By]
FROM AAJuly07Data LEFT JOIN Marketing ON AAJuly07Data.[Created By] =
Marketing.CDSID
WHERE Marketing.CDSID IS NULL;