Hi there,

I have a table that does not seem to be as normalized as it could be.  In it, a particular record may show up several times, each time having a different entry in  one particular field.  For example:
ID     STATUS     OTHER
rec1   exists     data
rec1   removed    data
rec1   destroyed  data
rec2   exists     data
rec2   removed    data
rec3   conceived  data
rec3   exists     data
rec4   destroyed  data

There's a ton more info for each record, but these are the fields I'm worried about.

What I need to do is query this table such as to return only the data that:
1) contains the status "exists";
2) does NOT contain the status "removed" or "destroyed";
3) may contain the status "conceived"

I am able to do this through 3 seperate queries, including one which joins the table to itself (in MS Access).

QUERY1 ) Get all the records with "EXISTS":

SELECT table.ID, table.STATUS
FROM table
WHERE (table.STATUS)="EXISTS";

QUERY2) GET all of the records that contain statuses I don't want:

SELECT table.ID, table.STATUS
FROM table
WHERE (table.STATUS) IN ('removed','destroyed');

QUERY3) Using MS Access wizardry, join these 2 queries.  I can filter the results to get only the ones that have a null value in the STATUS field of the filtered results.  This implies that while they did have a status of 'EXISTS'.

This gives results like:
ID     STATUS   STATUS_1
rec1   EXISTS   DESTROYED
rec2   EXISTS   REMOVED
rec3   EXISTS   <null>

Since what I want is just the records where the STATUS is EXISTS, and no match for any of the values I want to filter out (ie QUERY2), I can simply filter on the existence of the null value, and get all of the records I want.  The SQL for all this is simply

SELECT QUERY1.ID, QUERY1.STATUS, QUERY2.STATUS
FROM QUERY1 LEFT JOIN QUERY2 ON QUERY1.ID = QUERY2.ID
WHERE (QUERY2.STATUS) Is Null;

All this brings me to my final questions:
Can someone help me merge these things into a single query?
For the example above, I have downloaded some data and am trying to get it to work in a local Access database.  Eventually, I need to work with the real data, and I can only use the iSQL+ Oracle interface, which (to my understanding) only allows me to paste in my SQL query.  There is too much data for me to download it all and partition it neatly using multiple queries.

This has been bugging me all day.  If anyone can help me turn this into a single query for either MS Access or Oracle, i would be much obliged.

Thanks folks,
Mike