Thank you for your thoughtful consideration.
I am running Access 2000 (9.0.3821 SR-1)
This query works:
Expand|Select|Wrap|Line Numbers
- SELECT [id], [name]
- FROM table1
- WHERE id NOT IN (SELECT document_type_id FROM table2 WHERE case_type_id=case_type_id ) AND id NOT IN (SELECT document_type_id FROM table3 WHERE case_number='case_number')
- ORDER BY name;
Expand|Select|Wrap|Line Numbers
- table1
- id AutoNumber (Primary Key)
- name Text
- description Text
- date_create Date/Time (General Date)
- date_update Date/Time (General Date)
Expand|Select|Wrap|Line Numbers
- table2
- id AutoNumber (Primary Key)
- case_type_id Number (Long Integer)
- document_type_id Number (Long Integer)
- date_create Date/Time (General Date)
- date_update Date/Time (General Date)
Expand|Select|Wrap|Line Numbers
- table3
- id AutoNumber (Primary Key)
- case_number Text
- delay_type_id Number (Long Integer)
- document_type_id Number (Long Integer)
- other_document_type Id Number (Long Integer)
- event_open_date Date/Time (Short Date)
- event_close_date Date/Time (Short Date)
- date_create Date/Time (General Date)
- date_update Date/Time (General Date)
Expand|Select|Wrap|Line Numbers
- SELECT [id], [name]
- FROM table4
- WHERE case_number='case_number' AND id NOT IN (SELECT other_document_type_id FROM table3 WHERE case_number='case_number')
- ORDER BY name;
Expand|Select|Wrap|Line Numbers
- table4
- id AutoNumber (Primary Key)
- case_number Text
- name Memo
- date_create Date/Time (General Date)
- date_update Date/Time (General Date)
differences between the tables.
There are 3 records in table4 for a case_number and only 1 is in table3, so
I would expect a return of two records. I get nothing.
If I remove the NOT IN clause it returns all three records from table4.
If I change it to IN I get the 1 record that is also in table3.
This is very strange.
case_type and case_number are values taken from an active form.
I am unable to find discussion regarding SELECT queries with NOT IN clauses.
Thank you for any help.