Solved

Query to EXCLUDE items?

Posted on 2008-06-10
2
1,317 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:redpoppy
2 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 21749795
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
 

Author Closing Comment

by:redpoppy
ID: 31465671
Hi Kelvin - that's fantastic, worked a treat, first time! Thanks a lot
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question