Solved

Query to EXCLUDE items?

Posted on 2008-06-10
2
1,319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

751 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