Solved

Access - Excluding records from one table based on another table

Posted on 2015-01-26
2
494 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
2 Comments
 
LVL 84

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now