Have Not Ordered
By Richard Rost
4 years ago
Show Who Has Not Ordered a Specific Product
In today's video, I'm going to show you how to generate a list of customers who have not purchased a specific product. This is especially handy for marketing purposes. Want to send an email to every Starship captain who hasn't purchased a photon torpedo? Now you can.
Simon from Fresno, California (a Platinum Member) asks: Once a month, I like to send a marketing email out to all of my customers who aren't currently signed up for my maintenance plan. Is there a way to get a list of customers who have not purchased a specific product?
Members
Members will learn how to apply this technique to the Gold Member version of the TechHelp database. We have a product table where we can pick from a list of products and add those to an order. I'll show you how to make a combo box on the customer list form where you can see all customers, or only those customers who have purchased a product, or have not purchased a product.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Update
- Access Developer 36 covers selecting multiple products. You can filter the customer list based on those who have purchased any, all, or none of the products selected.
Suggested Course
Links
Keywords
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Customers Without a Product, Customers Who Don't Have a Product, Have Not Purchased, Query customers who have never ordered specific product, unordered products, customers without contacts
Intro
In this video, I will show you how to create a query in Microsoft Access to find customers who have not ordered a specific product. We will talk about using outer joins, working with multiple tables like customers, orders, and order details, and how to set up parameter queries so you can search for different products by name. I will walk you through the process step by step, including how to manage searches with two tables or three, using "is null" to find records that do not exist, and how to avoid duplicate results with aggregate queries.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to design a query in Microsoft Access showing customers who have not ordered a specific product.
Today's question comes from Simon in Fresno, California, one of my Platinum members. Simon says, "I bet you get that a lot, Simon." Simon says, "Once a month, I like to send a marketing email out to all of my customers who are not currently signed up for my maintenance plan. Is there a way to get a list of customers who have not purchased a specific product?"
Well, Simon, of course we can do that with a little query magic. I will show you in just a second. But first, if you do not know these things, go watch these videos first. If you do not know what an outer join is, go watch my outer join video. These are all free videos. I will put links down below in the description under the video. You can click on those, watch these videos, and then come back.
Outer joins - do we need that in order to do what we are going to do today? If you do not know what "is null" is, go watch that video. And optionally, if you do not know what a parameter query is, go watch that video too. So go watch these three videos, then come on back.
Here I am in my TechHelp free template. This is a free database. You can download a copy off my website if you want to. Before we get into orders, before I show you how to make a list of people who do not have a particular product ordered, let me show you something a little simpler with two tables, because to do this with orders, it involves three tables.
We have customers, we have orders, and we have order details. Let's do it with just two tables first. Let's do it between customers and contacts. A contact in my database is every time we talk to a customer. If I go to the customer form and open up contacts, I can see here all the times I talked with this customer. That is called a contact.
On 12/12/2020 talked about a job. And on that same day, came in for an interview. Whatever it is, these are all contacts.
So let us make a list of all of the customers who do not have contacts.
Let's see, Jim Kirk. He has one contact. Let's see, Deanna Troi. She has one. What else do we have here? John McPrickard. He has no contacts. That is the new blank record right there. Who else do we have in here? Will Riker, no contacts. And Malcolm Reynolds. So the last three do not have any contacts.
The way we find out who does not have a contact is that we join the two tables together and see where the contact ID is null.
So let's make a query. Create, query design. I am going to bring in my customer table and my contact table. There they are. Let's bring in the customer ID, first name, and last name. And let's also bring in the contact ID, contact date, and description.
If I run the query right now, I only see the records that match from both tables. In other words, every customer has to have a contact and every contact has to have a customer. In order to see all of the customers, whether or not they have a contact, that is where our outer join comes into play. That is why I wanted you to watch that outer join video first.
By turning this into an outer join, technically an outer left join, now we will see all of the customers. If they happen to have a related contact, it will show that next to it. Now when I run this, there we can see all the customers. But Jean-Luc, Will Riker, and Malcolm Reynolds do not have any contacts. This is null. That is what we are looking for.
Back to design view, and under contact ID, put a criteria "is null." Now when I run it, I will see only the customers who do not have any contacts.
I will save that as "customer no contact q." I like to keep all my names singular. I know it is weird, but when you get into programming and more advanced stuff, you have got to stop and say to yourself, "Was it customers no contacts, or was it customer?" So I just keep everything singular. It just makes everything so much easier.
All right, "customer no contact." There we go. So that is two tables. That is pretty easy.
Now, let's try the same technique with a three-table join, because in order to do customers to order details, order detail is where our product information is. If I go to a customer, go to orders, here is the order table and here is the order details table. Order details are the products, the line items. These are just typed in. This is free text.
In the advanced members-only version, we actually have a product table, so you can pick a product from the list and it stores a product ID, but this is the simple version. So we will look for a product by name, by the text of the name.
Simon, you are a member, so in the extended cut, I am going to go into more detail and show you how to do it with a combo box, so you can pick a product. But for everybody else, working with the name is just fine.
If you want to see how I built this database, that is a free video too, it is my invoicing database. There is the blank template, there is the contacts where I add that contacts stuff, and then there is the invoicing part. These are all free videos too, you can go watch these and learn how I built this entire database.
So let's go try that technique. Let's go back here. Let's go create query design. This time I am going to add in the customer table, the order table, those are linked, and then the order detail table. Let's bring in the customer ID, first name, last name. Let's bring in an order ID to see it, and let's bring in the order detail ID and a product name.
Let's run that. There we go. There are all the customers that have orders. Not everyone does, as you can see only customers one and two, so me and James Kirk are the only ones that actually have orders, and there are all the products on those orders.
Now let's make them outer joins. Left join that one. Left join this one. Let's run it now. I am seeing this stuff here. Now how do I check to see if they have a specific product? I can use the product name right here for example, as a criterion.
So let's go to design view, and right here where it says criteria, put "cupcake." Let's save this real quick. Let's call this "customer with product q." Customers with a product. We will make this generic in just a minute, but let's just work with cupcake for right now.
If I run that, now I see all of the customers that have purchased a cupcake. Let's give someone else a cupcake too. Let's see, Malcolm Reynolds has a cupcake.
All right, so let's go to orders. We will go to new order in here and we will put a cupcake on here for them. Cupcake, and $10. The expensive cupcake. Whoops, okay.
Now let's run that query again, "customers with product." Now I am seeing Richard and Malcolm Reynolds, all the customers that have a cupcake.
Now how do I get a list of the customers that do not have cupcakes? Let's go back here again. What if I try and put the word "not" there? Not cupcake.
If I run that, I am still seeing Richard and James Kirk. What does this mean? I am basically seeing all of these lines that do not have cupcake in them, but it is not necessarily a list of all of the customers that do not have cupcakes. Because it is each line item, it is each order detail that does not have cupcakes.
So what you actually need to do is leave this as just "cupcake." Here is a list of all the customers that have cupcakes. Now what you have to do is join this query to your customer table again and do the same trick that we did before, looking for null.
This is a list of customers with the product. Let's close this. Save changes. Yes.
Let's make another query now. Sometimes the best way to do things is to make a query and another query. They are two queries together. That is just the easiest, the best way to do this.
Another query, bring in your customer table, and then go to queries and bring in that customer with product q. Now again, customer first name, last name. If I bring in over here now, let's bring in order ID, order detail ID and product name just to see what we got. Run it. There they are.
Now if we left outer join this, now you will see all of the customers, and if they are on this list, you will see that information too. So there are all of the customers that have cupcakes, and guess what? If order ID is null, or order detail ID is null, that means they do not have a cupcake. See, it is a singular list of customers linked to whether or not they have that cupcake.
Come in here and for order ID, put "is null" and then run it, and there you go. There is your list of customers without a product.
I will save this as "customer without product q." Now if they happen to have multiple orders, let's say for example me, I have got one order with a cupcake here. Let me make a second order. I do have a second order. I will put cupcake on my second order too. If that happens, let us see, if I run "customer without," okay, that is good. If I run the "customer with," I do show up twice here.
So if that is a problem, you can just aggregate this. Your list without should not have duplicates because these people will only have one null record. But if you want to have a list of customers with a product, but you are getting duplicates in here, then just feed this into an aggregate query.
Of course, if you do not know what an aggregate query is, there you go. Another free video for you.
But it basically looks like this. Again, we are going to feed this query into another query. Do not try to modify this one. This one is good. It works. Do not touch it.
Go to Create, Query Design. We are going to bring in that customer with product q, and then just the field that you want to see unique, so customer ID, first name, last name. Turn on the totals over here. We now have an aggregate query. When I run that, there is a unique list. You can sort that, do whatever you want with it. Maybe sort by last name, first name, and then save "customer with product unique q." There is a unique list of customers with a particular product.
Now, how do we control what product that is without putting "cupcake" right inside the query? Go to Design View; that is where that parameter query thing comes in. Right down here, we will type in left bracket, "Enter product name," right bracket, just like that. Here I will zoom in so you can see it better. F2, zoom in, that is what you type in, or whatever you want that to say, any prompt. Nine times out of ten, if you see that prompt, "Enter parameter value" and you were not expecting it, you have got something typed in wrong, you have something spelled wrong. I have a whole video on Enter Parameter Value, go watch that if you want to.
Now if I type in "phaser," I do not have any phaser sold. Let's close and save changes. Yes. Let's take a look at the orders here real quick. What do I have? Training mouse, cupcake, test cupcake. I have a lot of cupcakes. What do I have in the order details here? Let's see, oh, phaser bank, oh, that is why, that is why it would have to be exactly what you type in, not just phaser, it has to be phaser bank.
If you want to make that a wildcard search, you can do that too.
"Customer with product," which one do we have to modify here? This one, design view. Here this is a little trickier, it is bonus material. This has to be an exact search, but if you want to make it so it can be any part of that name, type in "Like," and that is going to be "quote, star, quote, ampersand," then your prompt, "ampersand, quote, star, quote." That says, take whatever I type in here, like the word phaser, concatenate it, put it between two asterisks, and then use the Like keyword.
There are a whole bunch of different videos here. This is parameter query, this is called string concatenation, and this is called a wildcard search. I will put links to all these down below. There are links down below; go click on those if you do not know this stuff.
Now when I hit OK, save this, close it, and let's run it. Now if I type in "phaser," I get "phaser bank" because it is part of the larger string. I could type in "photon," like just photon, and there is "photon torpedo." That is for doing text searches.
So there you go, that is how you search for someone who does not have a particular product. Again, if you have got a situation with just two tables, it is pretty easy to do. Just put the "is null" in there. If you have three tables, you have to join them together, then you have to reverse that and say, "I want to find all the people who have this product," and then match that up with the whole list of customers, then you can put your "is null" on that. You have to kind of find who has it first and then reverse that list.
Now members, in the extended cut, your database is different because you have a list of products that we can pick from a product table. What we will do is we will do a search with product, without product, or we will see all customers. We will put an actual filter on the customer list form, and then we will pick whether you want to see all customers with a product, without a product, then we will actually pick the product itself.
We will pick a "pickled product" from the pick list. Here is your member database. Here is your customer list. I scaled it down a little bit. We just have first name, last name on here. There are all customers with a product. Which product is it? Let's say phaser bank, then we will filter that list. Or we will go without that product, and we will refilter it again. Or I will pick "triple bait." Who has got triple bait? Or this is, this is who does not have triple bait; this is who has triple bait. Then you can go back to all customers if you want, that box will hide.
That is neat stuff. That is all covered in the extended cut for the members. Silver members and up get access to all the extended cut videos. There are well over 200 of them now, like 250-something. Gold members get access to download these databases that I build in the TechHelp videos, and you get the code vault with all kinds of extra VBA code and goodies and stuff like that, and lots more.
So how do you become a member? You will be told in just a second, but I hope you guys learned something today. We will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, a free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free Expert class each month after you have finished the Beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions, access to all of my full Beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout-out in the video and a link to your website or product in the text below the video and on my website.
But do not worry, these free TechHelp videos are going to keep coming as long as you keep watching them. I will keep making more and they will always be free.
Quiz
Q1. What is the main goal of the query demonstrated in this video?
A. To display orders containing a specific product
B. To show all contacts for each customer
C. To list customers who have NOT ordered a specific product
D. To create a list of all available products
Q2. Which database concept is especially important for this technique?
A. Aggregate functions
B. Cross joins
C. Outer joins
D. Data macros
Q3. When working with only two tables to find customers without contacts, which criterion is used?
A. =0
B. >1
C. Is Null
D. Not Null
Q4. In the second example using three tables, why is a two-step query process needed?
A. To join employees and contacts
B. Because product data is entered as free text in order details
C. To update customer information automatically
D. To import data from Excel
Q5. What is the purpose of using a parameter query for the product name?
A. To restrict access to certain products
B. To hardcode product names in the query
C. To prompt the user to input the desired product name at runtime
D. To randomly select a product
Q6. If you want to allow partial matches for product names in your parameter query, what keyword do you use?
A. Group By
B. Between
C. Like
D. Sum
Q7. What is added to the query criterion for wildcard searches with partial product name matching?
A. %
B. *
C. #
D. $
Q8. What is the main reason to use an aggregate query in this scenario?
A. To delete duplicate records
B. To ensure the customer without product list contains only unique customers
C. To calculate total order values
D. To export results to Excel
Q9. In a three-table join to find customers without a specific product, which field should be checked for null values?
A. Customer First Name
B. Order Total
C. Order ID or Order Detail ID
D. Product Price
Q10. If you mistakenly see a parameter prompt and did not expect it, what is the likely cause?
A. A missing database file
B. A misspelled field name or incorrect parameter
C. A corrupted query
D. An invalid join type
Q11. What is the final step to customize the query for any product?
A. Change the join type to inner join
B. Hardcode the product name in the criteria
C. Use a parameter or wildcard search in the product name criteria
D. Remove the customer table from the query
Q12. What benefit do Gold members get in addition to the others?
A. Access to the code vault and downloadable sample databases
B. Free lifetime access to all videos
C. Ability to make unlimited edits in the community forums
D. Direct phone support from Richard
Q13. In the simple two-table example, what is the result when you run a query with an outer join but without an "Is Null" criteria?
A. All customers including those without contacts, with contact info if available
B. Only customers who have contacts
C. Only customers who do not have contacts
D. All contacts for all customers
Q14. What does 'left join' mean in the context of this Access query?
A. It only shows records from the right table
B. It shows all records from the left table and matching records from the right table
C. It combines only non-matching records from both tables
D. It sums up values from both tables
Q15. What does entering [Enter product name] as criteria do in your query?
A. Deletes all product data
B. Requires the user to type the product name when running the query
C. Hides the product name field
D. Sorts results in alphabetical order automatically
Answers: 1-C; 2-C; 3-C; 4-B; 5-C; 6-C; 7-B; 8-B; 9-C; 10-B; 11-C; 12-A; 13-A; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Access Learning Zone covers how to design a query in Microsoft Access to display customers who have not ordered a specific product. I am your instructor, Richard Rost.
Recently, a question came in that I think is common for a lot of businesses. Simon in Fresno contacted me wondering how he could easily get a list of customers who are not currently signed up for his maintenance plan, or more generally, how to find customers who have not purchased a specific product.
This scenario is a great example of how powerful Access queries can be, especially when you are comfortable with concepts like outer joins and handling null values. If you are new to these ideas, I recommend watching my videos on outer joins, the "is null" criterion, and parameter queries first. These resources are all available for free on my website and will help you understand the methods I am about to explain.
To build up to the solution, let's start with a simpler example using just two tables: customers and contacts. In my database, a contact record is created every time someone interacts with a customer. By linking the customer and contact tables and applying an outer join, we can identify customers who have never been contacted by checking for null values in the contact ID field. When the contact ID is null, it means there is no associated contact for that customer.
Creating a query is straightforward here. Add both the customer and contact tables, bring in customer details like ID, first, and last name, alongside contact information, and switch to an outer join so all customers are shown. In the criteria for the contact ID, set it to "is null," and this will produce a list of customers who do not have any contacts. I label this type of query with singular names for consistency and clarity, which I recommend as your databases grow more complex.
Now let's transition to the scenario with three tables: customers, orders, and order details. The order details table holds the individual items within each order, so product information is stored there. In a basic version of the database, the product names are entered as plain text rather than using a dedicated product table with proper product IDs. For this setup, you will search by the text of the product name.
This example is more involved. To find which customers have purchased a "cupcake," for instance, set up a query that joins customers to orders and orders to order details. Use outer joins to make sure customers without orders or order details are still displayed. Add a criterion of "cupcake" to the product name. If you run the query, it lists all customers who have purchased a cupcake.
But to find customers who have NOT purchased a cupcake, the process requires another step. "Not cupcake" in the criteria will not give you the correct list; it will only return order details that are for products other than cupcakes. We need to identify customers who are completely missing any order for the desired product.
To do this, create a query that finds all customers who have purchased the product in question (for example, "customers with cupcake"), and then create a second query that links the full customer list to the "with product" query using an outer join. You set the criterion "is null" on the relevant order ID or order detail ID fields. This produces the list of customers who have no matching records for that product – in other words, customers who have not purchased it.
If a customer has multiple orders containing the product, you could end up with duplicates in your "customers with product" query. You can resolve this with an aggregate query, which groups by customer and returns a unique list. Activate the totals option in the query builder, specify which fields to group by (such as customer ID, first name, and last name), and save the query to get unique results.
Another useful tip is making the product search dynamic by turning your static product name filter into a parameter. Instead of typing "cupcake" directly, use a parameter prompt such as [Enter product name] in the product name criteria. When you run the query, Access will ask you which product to search for. For even more flexibility, you can set this up as a wildcard search, allowing users to search for partial product names.
A few free videos on my website go into more detail about string concatenation, parameter queries, using wildcards, and handling "enter parameter value" prompts – all helpful additions if you want to expand the utility of your queries.
So to recap, finding customers who have not purchased a certain product typically involves creating two queries: one that finds customers who have the product, and another that links the customer table to the first query, then filters for nulls to find those without the product. For two-table situations, a single query with an outer join and "is null" usually does the trick. With three tables, you need the extra step of comparing your product owners against your total customer list.
For members, in today's Extended Cut, I go further and work with a database where products are managed in a dedicated table. We implement a selector to easily pick products, and add a filter to the customer list form to show either customers with or without the selected product. Members also learn how to toggle between all customers, those with a product, or those without, using a pick list for products.
Silver members and above get access to all Extended Cut videos, while Gold members can download all the sample databases and access my code vault. Platinum members enjoy all previous perks plus access to full beginner courses for various Microsoft Office tools and Developer classes. Membership details and benefits are explained on my website.
Remember, all my TechHelp videos are available for free on my site, with step-by-step video tutorials covering everything discussed here. Visit the link below for more information.
Live long and prosper, my friends.
Topic List
Designing a query to find customers without a specific product
Using outer joins to display all customers regardless of contacts
Filtering records using the Is Null criteria
Creating a query to list customers with no related contacts
Building queries across customers, orders, and order details tables
Filtering by product name in order detail queries
Understanding the issue with Not criteria in multi-table joins
Creating a query to find customers with a specific product
Joining queries to identify customers without a specific product
Saving and naming queries for clarity
Eliminating duplicate records through aggregate queries
Converting queries to parameter queries for flexible product search
Using wildcard searches with parameter queries in Access
Concatenating strings for flexible search criteria in queries
Step-by-step demonstration of query design and criteria application
|
