querying exclusive records

  • Thread starter Thread starter Dave Cullen
  • Start date Start date
D

Dave Cullen

I need a query that will give me the rows in one table that are NOT in
another table or query.

Example: Hospital admissions application. There's a Rooms table listing
all rooms in the hospital. ROOM_NO is the primary key. Another table,
HospitalVisits, lists all patient visits past and present. ROOM_NO is
one of the columns, along with RELEASE_DATE. A query of CurrentVisits
selects all HospitalVisits records where the release date is null.

I need to make a query to select a room that is NOT currently in use.
When I join Rooms and CurrentVisits, there's no selection criteria for
EXCLUSIVE values. I can only get a list of rooms currently in use, not
the others.

The SQL looks like this:

SELECT Rooms.ROOM_NO
FROM Rooms INNER JOIN [CurrentVisits]
ON Rooms.ROOM_NO=[CurrentVisits].ROOM_NO;

How do I stipulate "not contained in" instead of = ?

Thanks
 
Try this SQL:

SELECT Rooms.ROOM_NO
FROM Rooms LEFT JOIN [CurrentVisits]
ON Rooms.ROOM_NO=[CurrentVisits].ROOM_NO
WHERE [CurrentVisits].ROOM_NO Is Null;
 
Thanks, but ROOM_NO is never null. All current visits have a room
assigned.

I was able to do this with NOT IN, to get only the rooms that are NOT
contained in the CurrentVisits query. Too bad it's not one of the join
properties selections in Access. Seems like this would be a common
thing.

drc


Ken said:
Try this SQL:

SELECT Rooms.ROOM_NO
FROM Rooms LEFT JOIN [CurrentVisits]
ON Rooms.ROOM_NO=[CurrentVisits].ROOM_NO
WHERE [CurrentVisits].ROOM_NO Is Null;

--
Ken Snell
<MS ACCESS MVP>

Dave Cullen said:
I need a query that will give me the rows in one table that are NOT in
another table or query.

Example: Hospital admissions application. There's a Rooms table listing
all rooms in the hospital. ROOM_NO is the primary key. Another table,
HospitalVisits, lists all patient visits past and present. ROOM_NO is
one of the columns, along with RELEASE_DATE. A query of CurrentVisits
selects all HospitalVisits records where the release date is null.

I need to make a query to select a room that is NOT currently in use.
When I join Rooms and CurrentVisits, there's no selection criteria for
EXCLUSIVE values. I can only get a list of rooms currently in use, not
the others.

The SQL looks like this:

SELECT Rooms.ROOM_NO
FROM Rooms INNER JOIN [CurrentVisits]
ON Rooms.ROOM_NO=[CurrentVisits].ROOM_NO;

How do I stipulate "not contained in" instead of = ?

Thanks
Click to expand...
Click to expand...
 
Dave,
Did you try the suggested code? It should work to solve your problem as stated.


What happens with the LEFT JOIN is that any Room_No in the Rooms Table that does
not have a match in the CurrentVisits table will show the all fields in the
CurrentVisits columns as NULL values.

I am guessing what you really want is a little more complex then what you stated.

Dave said:
Thanks, but ROOM_NO is never null. All current visits have a room
assigned.

I was able to do this with NOT IN, to get only the rooms that are NOT
contained in the CurrentVisits query. Too bad it's not one of the join
properties selections in Access. Seems like this would be a common
thing.

drc

Ken said:
Try this SQL:

SELECT Rooms.ROOM_NO
FROM Rooms LEFT JOIN [CurrentVisits]
ON Rooms.ROOM_NO=[CurrentVisits].ROOM_NO
WHERE [CurrentVisits].ROOM_NO Is Null;

--
Ken Snell
<MS ACCESS MVP>

Dave Cullen said:
I need a query that will give me the rows in one table that are NOT in
another table or query.

Example: Hospital admissions application. There's a Rooms table listing
all rooms in the hospital. ROOM_NO is the primary key. Another table,
HospitalVisits, lists all patient visits past and present. ROOM_NO is
one of the columns, along with RELEASE_DATE. A query of CurrentVisits
selects all HospitalVisits records where the release date is null.

I need to make a query to select a room that is NOT currently in use.
When I join Rooms and CurrentVisits, there's no selection criteria for
EXCLUSIVE values. I can only get a list of rooms currently in use, not
the others.

The SQL looks like this:

SELECT Rooms.ROOM_NO
FROM Rooms INNER JOIN [CurrentVisits]
ON Rooms.ROOM_NO=[CurrentVisits].ROOM_NO;

How do I stipulate "not contained in" instead of = ?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top