Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query to EXCLUDE items?

Posted on 2008-06-10
2
Medium Priority
?
1,321 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 2000 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 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