"NEGATIVE JOIN" or Dis-JOIN 1

[StarryEyed]

How may I select all records from one table only where there is no corresponding
record in another table. e.g. I have a table1 of completed projects and table2 of
active projects. In both tables the projects are counted and grouped by business unit.

I run a query where I select The business unit, completed count and active count
for all records in table1 and those records in table 2 where the business units are
equal.

I now want to select all records in table2 where the business unit does not match a
business unit in table1. [sig][/sig]

 

[kathryn]

Have you looked at the Find Unmatched Query Wizard? Go to the Query tab and choose New. It is listed in the list box. If that's not what you need, could you post the table designs of your two tables (only need to show relevant fields), along with some sample data and what you want the results to look like? [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[TheInsider]

Hi,
Try an SQL statement like this:

SELECT Table2.* FROM Table1 RIGHT JOIN Table2 ON Table1.field1 = Table2.field1 WHERE (((Table1.field1) Is Null));

[sig]<p>Rob Marriott<br><a href=mailto:rob@career-connections.net>rob@career-connections.net</a><br>[/sig]