Finding complimentary data sets in MS Access Using Queries

[User 2084]

Hello all. I'm an access newbie trying to learn how to do basic
database data manipulations. I had a hard time searching the archives
on this question as I don't really know what I'm looking for in this
case, so please refer me to previous posts that address my question.
Thanks in advance.

My question relates to finding complimentary data sets using queries.
For the sake of discussion, lets say that I have a database that
tracks automobiles for a rental car agency. There are two tables - one
table lists all of the automobiles tracked and the other table lists
associated transactions with the automobiles - when the automobiles
were last serviced, whether or not they are currently rented, whether
or not they have been sold, etc. Now lets say that I want to find out
whether or not an automobile was on my car lot on a particular date. I
define whether or not a car is on the lot by saying that if the car
wasn't rented or the car hadn't been sold prior to that date, the car
must have been on the lot. I can easily build a query to show all the
cars that were rented on that day or sold prior to that date using MS
Access. However, what I want is the complimentary data set; all the
data in the Automobiles table that is NOT in my query.

In other words, if I define all the automobiles in the system as Set
A, and the query that I can build as set B, what I want is the Set C
such that an element is in set C if it is in Set A but not in Set B.
If someone can let me know how to do this using the GUI or SQL view in
access, I'd really appreciate it. Even if it's simply providing the
search terms for me to read up on.

Thanks.

[Tom van Stiphout]

On 29 Dec 2004 19:02:59 -0800, us******@hotmail.com (User 2084) wrote:

There are several ways you can do this. Here is one, using the IN
clause:
select * from Automobiles
where AutomobileID not in (select AutomobileID from RentedOrSoldQuery)

-Tom.

Hello all. I'm an access newbie trying to learn how to do basic
database data manipulations. I had a hard time searching the archives
on this question as I don't really know what I'm looking for in this
case, so please refer me to previous posts that address my question.
Thanks in advance.

My question relates to finding complimentary data sets using queries.
For the sake of discussion, lets say that I have a database that
tracks automobiles for a rental car agency. There are two tables - one
table lists all of the automobiles tracked and the other table lists
associated transactions with the automobiles - when the automobiles
were last serviced, whether or not they are currently rented, whether
or not they have been sold, etc. Now lets say that I want to find out
whether or not an automobile was on my car lot on a particular date. I
define whether or not a car is on the lot by saying that if the car
wasn't rented or the car hadn't been sold prior to that date, the car
must have been on the lot. I can easily build a query to show all the
cars that were rented on that day or sold prior to that date using MS
Access. However, what I want is the complimentary data set; all the
data in the Automobiles table that is NOT in my query.

In other words, if I define all the automobiles in the system as Set
A, and the query that I can build as set B, what I want is the Set C
such that an element is in set C if it is in Set A but not in Set B.
If someone can let me know how to do this using the GUI or SQL view in
access, I'd really appreciate it. Even if it's simply providing the
search terms for me to read up on.

Thanks.

[user2084]

Tom:

Thank you very much. This was exactly what I was looking for.
Tom van Stiphout wrote:

On 29 Dec 2004 19:02:59 -0800, us******@hotmail.com (User 2084) wrote:
There are several ways you can do this. Here is one, using the IN
clause:
select * from Automobiles
where AutomobileID not in (select AutomobileID from RentedOrSoldQuery)
-Tom.