AND NOT subqueries 2

[Rob596]

I'm trying to get a query to give me a list of items where they are not also in another list, essentially the Oracle MINUS function but in Access. The closest I can get is an error saying "At most one record will be returned by this subquery" but both the main and sub queries work properly by themselves.

Code below:

SELECT model_identifiers.IT_ID
FROM make, model, model_identifiers
WHERE  make.Make=[forms]![Rental_Out]![subFormHardware]![cbomake] AND model.Model=[forms]![Rental_Out]![subFormHardware]![cboModel] and make.makekey = model.makexref AND model.modelkey = model_identifiers.modelxref
AND NOT (SELECT [rental_log].[IT_ID]
FROM rental_log, make, model, model_identifiers
WHERE [rental_log].[date borrowed] Is Not Null And [rental_log].[date returned] Is Null And [make]=[forms]![Rental_Out]![subFormHardware]![cbomake] And [model]=[forms]![Rental_Out]![subFormHardware]![cboModel] And [make].[makekey]=[model].[makexref] And [model].[modelkey]=[model_identifiers].[modelxref] And [model_identifiers].[IT_ID]=[rental_log].[IT_ID]);

Thanks in advance

Rob

 

[SkipVought]

Hi,

1) Get a LIST of the items that you want

2) use the resultset from that to exclude
[tt]
Select A.* from Table A LEFT JOIN Table B ON A.Item=B.Item
Where B.Item is Null
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

or replace this:
AND NOT (SELECT
by this:
AND NOT EXISTS (SELECT

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244