query question

[JOM]

I have 2 tables tbla and tblb which are similar to each other. What I want
is to do a query that only selects the acctID from tbla not found in tblb

 

[Larry Linson]

I have 2 tables tbla and tblb which are similar to
each other. What I want is to do a query that only
selects the acctID from tbla not found in tblb

There's a "Find Unmatched Query Wizard" which should let you
just-point-and-click to get the Query you want. On two test tables, where
AcctID is a text field in each, it worked for me and created the following
SQL.

SELECT tblA.AcctID, tblA.SomeInfo
FROM tblA LEFT JOIN tblB ON tblA.AcctID = tblB.AcctID
WHERE (((tblB.AcctID) Is Null));

As is usually the case, there are extra parentheses in the WHERE clause, but
that does not cause any problem.

Larry Linson
Microsoft Access MVP

 

[John Vinson]

I have 2 tables tbla and tblb which are similar to each other. What I want
is to do a query that only selects the acctID from tbla not found in tblb

Create a new Query; use the "Unmatched Query Wizard".

Or, do it manually. Create a Query in the query design window. Add tbla and
tblb; join them by acctID by dragging acctID from one table to the other.
Click on the Join line and choose option 2 - "Show all records in tbla and
matching records in tblb".

Select acctID from tblb into the grid, along with any fields in tbla that
you want to see; put

IS NULL

on tblb.acctID.

This "frustrated outer join" query will find all records in tbla; the join
will match up any records in tblb; the criterion will EXCLUDE those that
match, leaving only those that don't.