Trying to compare 2 queries with results only from 1 1

[glthornton]

I'm using MS Access 2000 and I'm trying create a query (or table if needed) to provide me all the Appointment ID's that are still acive and not marked as "cancelled". I have one query (Booked_Appts) providing me with just the "Booked" Appointment ID's (ApptID). The other query (Cancelled_Appts) provides me with all the Appointment ID's (ApptID) that have been marked "Cancelled".

I would like some assistance as to how I can use both queires to tell me which Appointment ID's (ApptID) that are still "active".

(Booked_Appts.ApptID)          (Cancelled_Appts.ApptID)
123                            456
456
789
1011

So based upon the above type example, I would only like to have ApptID's 123, 789, 1011 as a result. Because not only is ApptID 456 "booked", but it is also marked as "cancelled". This is caused because the database that I am linking to, has an entry for each occurrance the status of an appointment is changed.

I would appreciate any suggestion or recommendation that anyone could provide.

Thank you,

GlThornton

 

[Golom]

Don't know your table structure so this is just a template

Select ApptID
From myTable
Where ApptID NOT IN (Select ApptID From myTable Where ApptCanceled)

where you need to provide the specification for ApptCanceled determining if an appointment has been canceled.

 

[PHV]

SELECT B.*
FROM Booked_Appts B LEFT JOIN Cancelled_Appts C ON B.ApptID = C.ApptID
WHERE C.ApptID Is Null

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

 

[glthornton]

Awesome PHV!!

Worked like a charm!! I haven't been using SQL in quite a few years and now that I'm getting back into it. I looked at your suggestion and I said to myself "what a dope?". Very simple and easy resolution.

Tek-Tips is an incredible resource that no one should be without!

Thank you,

GlThornton