Excluding records in a query based on info in a table

[djhoneygo]

I am fairly new to Access, so please forgive me if this question is obvious and/or if it has been answered before.



I have two tables that I am pulling information out of in a query. One of the tables (Table Errors) includes records that I would like to exclude from the output of the query. The records in that table are a subset of the data in a much bigger table (Table Main Data), and they contain a field indicating that these records are errors. I would like to use the error field to exclude this subset of records from Table Main Data. However, if I link the appropriate fields in Table Errors and Table Main Data, but then add the "Error" field from Table Errors into the query, it shows me the opposite of what I want: only those records which contain the errors. How do I use the "Error" field in Table Errors to exclude these erroneous records from Table Main Data?

Thanks!

[Dal Jeanis]

In query design view, right-click the line that links the two records. Change the query type to "include all records in the left table that are NOT in hte right table."

[djhoneygo]

Thanks a lot for the response. But I'm not sure this is going to work. When I click on the link, the options are as below (hiatus_results is Table Main Data and Exclusions_test is Table Error):



If I choose the second option, I'm still not using the Error Table to exclude the records in the Main Data Table that match those records in the Error Table.

Moreover, when I did try to do this, I got an ambiguous outer join error, even though there's not another table in the query that's also being joined. But it may not be worth it to figure out what that's happening if it's not going to give me the records that I need anyway.

[lfpm062010]

I think Dal meant #3. To include "Exclusions_test" (Table Error) and only those records from "hiatus_results" (Table Main Data) where the joined fields are equal.

What error are you getting? Can you posted the SQL (right click on the "query tab" and select SQL View)?

[Dal Jeanis]

No, neither of those is right. Please use the unmatched query wizard to create your query from scratch.

[lfpm062010]

After re-read your original post, I misunderstood your request. You are just misisng "Is Null" as your criteria from the Join column from "Error Table". This is should be the same as using the "Unmatched query".

For example,
SELECT MAIN.ID, MAIN.OTHERS, ERROR.ID, ERROR.OTHERS
FROM MAIN LEFT JOIN ERROR ON (MAIN.ID = ERROR.ID)
WHERE ERROR.ID Is Null ;

[djhoneygo]

Thanks a lot for the help so far. Trying to figure this out has uncovered some miscoded data that is affecting the query, so I'll need to fix that first to see if I can get this to work for real.