Query to EXCLUDE items?

Hi Experts,

I have 1 query that extracts e-mail addresses from clients that have made a booking, in order to e-mail them for their feedback:
SELECT First(Clients.Lead_Name) AS FirstOfLead_Name, First(Clients.First_Name) AS FirstOfFirst_Name, First(Clients.Title) AS FirstOfTitle, Clients.[E-Mail], Clients.Client_ID
FROM bookings INNER JOIN Clients ON bookings.Client_ID = Clients.Client_ID
GROUP BY Clients.[E-Mail], Clients.Client_ID
HAVING ((Not (Clients.[E-Mail]) Is Null));

I have another query that extracts e-mail addresses from clients that have requested quotations so that a follow up can be sent:
SELECT First(Clients.Lead_Name) AS FirstOfLead_Name, First(Clients.First_Name) AS FirstOfFirst_Name, First(Clients.Title) AS FirstOfTitle, Clients.[E-Mail], Clients.Client_ID
FROM Clients INNER JOIN quotations ON Clients.Client_ID = quotations.Client_ID
GROUP BY Clients.[E-Mail], quotations.Booking_ID, Clients.Client_ID
HAVING ((Not (Clients.[E-Mail]) Is Null) AND ((quotations.Booking_ID)=0));

My problem is that I don't want to send a quotation follow up to anyone that has booked. I understand how I could extract that data by creating a new query of client id's that appear in both of the above queries ie: if there's a matching client_id, then they are in both the booking and quotation list.

My question: is it possible to modify the quotations list query to exclude the matching entries using a query? I know that I could do this in a module by ignoring the matching entries but it would fit much better into the existing system if I could filter the data beforehand.
redpoppyAsked:
Who is Participating?
 
Kelvin SparksConnect With a Mentor Commented:
Something like
SELECT First(Clients.Lead_Name) AS FirstOfLead_Name, First(Clients.First_Name) AS FirstOfFirst_Name, First(Clients.Title) AS FirstOfTitle, Clients.[E-Mail], Clients.Client_ID
FROM Clients INNER JOIN quotations ON Clients.Client_ID = quotations.Client_ID
GROUP BY Clients.[E-Mail], quotations.Booking_ID, Clients.Client_ID
HAVING ((Not (Clients.[E-Mail]) Is Null) AND ((quotations.Booking_ID)=0) AND Clients.Client_ID NOT IN(SELECT Client_ID FROM bookings INNER JOIN Clients ON bookings.Client_ID = Clients.Client_ID));

Kelvin
0
 
redpoppyAuthor Commented:
Hi Kelvin - that's fantastic, worked a treat, first time! Thanks a lot
0
All Courses

From novice to tech pro — start learning today.