Re: How get records not in another table?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

John,

Small correction to your example. The Join was in the wrong direction, what you posted would probably show no records (assuming that ID is a required field)

SELECT <whatever you want to see>
FROM bigtable RIGHT JOIN smalltable
ON bigtable.ID = smalltable.ID
WHERE bigtable.ID IS NULL;


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John W. Vinson wrote:
On Wed, 23 Apr 2008 17:53:00 -0700, mscertified <rupert@xxxxxxxxxxxxx> wrote:

I need to list the records in one table (8,000 rows) that are NOT in another table (40,000 rows).
I tried SELECT ... WHERE NOT IN (SELECT...) but it took forever to run and I had to cancel the query.

The Query design window has an "Unmatched Query" wizard which does exactly
this. To roll your own, use a "frustrated outer join" query like

SELECT <whatever you want to see>
FROM bigtable LEFT JOIN smalltable
ON bigtable.ID = smalltable.ID
WHERE bigtable.ID IS NULL;

.

Relevant Pages

  • Re: Corstabquery or normal query
    ... This is my SQL for Query: ... "John Spencer" wrote: ... IF you have problems with the crosstab query, ... 2007 Deacon Bob 1 ...
    (microsoft.public.access.reports)
  • Re: Corstabquery or normal query
    ... "John Spencer" wrote: ... Try to run the query. ... IF you have problems with the crosstab query, ... 2007 Deacon Bob 1 ...
    (microsoft.public.access.reports)
  • Re: Removing Formatting Symbols from Phone numbers
    ... "John Spencer" wrote: ... Dim strOut As Variant ... For intCount = 1 To Len ... When I attempt to do the Update Query where I copy and past your code into ...
    (microsoft.public.access.queries)
  • Re: Using If expressions with an Or expression, and a Count questi
    ... The addition of the commas inside the was to ensure exact matches to Bill and Steve and preclude a record with STE being returned. ... "John Spencer" wrote: ... You did not give us any table or field names so what I wrote was a generic example of a query. ... To do this in design view, you would put the IIF statement into a field "box" and the Like into a criteria box under the IIF. ...
    (microsoft.public.access.queries)
  • Re: Removing Formatting Symbols from Phone numbers
    ... "John Spencer" wrote: ... Sometimes the Design View (query grid) will insert quotes in the update to ... Does Access add quote marks around the table and field names or around ... Dim strOut As Variant ...
    (microsoft.public.access.queries)