report or query to get the difference between two queries

[jaf]

Hello all,
I have the following views in Access 2000:
vw_AllRooms - returns 222 rows
name vc(35),
number integer,
description vc (50)
returns all the room records in the DB

vw_AllOccupiedRooms - returns 22 rows
name vc(35),
number integer,
description vc (50)
returns all the room records that are occupied by an employee

These views are built by an application and I cannot create a view that
returns all rooms that are not occupied. Is their a way to create a report
or query that will join the two views and only return those rooms that are
not occupied (200 rows)? Any input would be greatly appreciated. Thanks

 

[Nikos Yannacopoulos]

John,

Open a new query in design view, add vw_AllRooms, drag all the fields you
need down to the grid so they are displayed as output, and then go to the
first Criteria line under the 'name vc' field and type in the following
expression (actually a subquery):

Not In (SELECT [name vc] FROM vw_AllOccupiedRooms)

This should do it.

HTH,
Nikos