Solved

Access - Excluding records from one table based on another table

Posted on 2015-01-26
2
839 Views
Last Modified: 2015-01-26
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
0
Comment
Question by:T_Van
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40571083
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
 

Author Comment

by:T_Van
ID: 40571145
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question