Access - Excluding records from one table based on another table

In a query with two tables, is there a way to pull records from one table based on excluding records from the second table?  For example, I have one table with medical claims and a second with a list of hospital ID's for one group of hospitals.  I want to pull all of the claims that DIDN'T go to any of the hospitals listed in the second table.  Also, I'd like to do this without using SQL statements.

Any help would be appreciated!

T_Van
T_VanAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure what you mean by "without using SQL statements" ... that's really the only way to exclude them, unless you're going to loop through the entire recordset and ignore those that don't have an associated Hospital record.

You can do a LEFT JOIN, and then filter for any that have NULL values:

SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.HospitalID=Table2.HospitalID WHERE Table2.HospitalID IS NULL

If there is no corresponding HospitalID in Table2, that column would be NULL ...

Obviously, you'd have to change the Table and Field names to match your own structure.
0
 
T_VanAuthor Commented:
Scott,

By following your SQL statement, I was able to figure out what you did.  In a simple Access query, it's just a matter of adding the hospital ID from the second table as part of the criteria, and putting "IS Null" for the criteria for that field.

It worked perfectly.  Thanks for the help!

T_Van
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.