Welcome

Welcome! Find Duplicates & No Orders

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Developer Level 36. In this course we will cover how to prevent duplicate records, focusing on using composite keys and the SQL NOT IN clause to avoid adding the same product to an order more than once. We will also work with finding customers who have not ordered certain products, first by a single product and then using multi-select list boxes for various scenarios, such as customers who have ordered all, any, or none of selected products. Additional lessons include using VBA and DCount for custom duplicate warnings and optimizing selection logic in your Access databases.

Navigation

Prev: D36
Next: Lesson 01

Keywords

Access Developer, prevent duplicates, composite key, SQL not in clause, DCount function, VBA duplicate check, multi-select list box, unique records, customer not ordered product, product combo box filter, temporary table, marketing query, select all butto

Subscribe to Welcome
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 36 brought to you by AccessLearningZone.com.

I am your instructor, Richard Rost.

In today's class, we are going to spend some time learning how to prevent duplicates. We will learn about composite keys and the SQL not in clause to determine if something is already in a set of records. For example, we are going to make sure that you cannot put the same product on an order twice.

Then we are going to do some work with have not ordered. We are going to generate a list of customers who have not ordered a particular product. We will start off by doing a single product. Then we will do "show me customers who have ordered any products from a list, or all products from a list, or none of the products from a list." I can pick two or three different products, show all the customers who have not purchased those products, and then I can send them some marketing information.

This class follows Access Developer Level 35, and I strongly recommend you have taken all the developer classes and everything before them - beginner, expert, and advanced - before taking this class. Especially, I suggest you review Developers 15, 17, and 19 if you have not watched them already. In those classes, I cover multi-select list boxes, which we will be using a lot of today. So watch those three classes if you have not yet.

I recommend you do not skip levels. My classes are designed to be taken one after the other. The material builds on the lessons before it, so make sure you watch all of them in order.

I will be using Access 365 at this point. It is roughly equivalent to Access 2019. I should probably update the slide for Access 2021, because that is the latest retail version. It is weird what they are doing with the versioning now. Access 365 is subscription-based. Basically, you constantly are getting the newest features that they release. So 2019 was the last major retail update. 2021 just came out a little while ago. There, I updated it.

Honestly, there is really not a lot of difference between 2021 and 2019. They are pretty much the same. They have had some little things here and there, but nothing that is going to affect the lessons that I am going to cover today. It is all the same stuff. In fact, everything that I have covered today probably will work going back to 2007, so you are good.

If you have questions, just scroll down to the bottom of the page that you are on and post them where you see that box. I think I changed it now as it is "Post a New Discussion" or "Start a New Discussion." It is not like that, but that is where you put your questions. Do not email them to me. Put them there.

I have a great group of admins and moderators on my website, and they are more than helpful, and they will get to your questions usually before I will anyway. So put them there.

If you have general questions about Microsoft Access, not related to the topics of today's class, feel free to post them in the Access Forum.

Let's take a closer look at exactly what is covered in today's class.

In lesson one, we are going to see how to prevent duplicate products from being added to an invoice using something called a composite key.

In lesson two, we will learn a different method for preventing duplicate values without a composite key. We will use a little bit of VBA and the DCount function. This will allow us to give the user the option to add the item twice if they want to. We can use a custom warning message instead of the stock warning message that the composite key gives you.

In lesson three, we are going to learn the SQL not in clause. This way, we can take our product combo box that has a list of items to add to the order, and we can say "only show me the list of products that are not in the current order." In other words, we do not want the same product being added twice. So we will have the items be removed from the product combo box if they are already on the order. That is the SQL not in clause.

In lesson four, we are going to see how to generate a query showing a list of customers who have not ordered a specific product. We are going to base this on the text of the product name. We will just have the user type it in, and I will search for customers without that product.

In lesson five, we are continuing with the have not ordered database. We are going to switch over to a more powerful database that has a list of products. You can pick from a list of products and say which customers have ordered this product, or which customers have not ordered this product. That is great for marketing. You could say "show me all the people who have not ordered a phaser bank" and send them a solicitation for it.

In lesson six, we are moving on to a multiple select list box. We are going to say, "I want to see a list of all the customers who have ordered all of the products I have selected." Then in the next couple of lessons, we will do any of the products and then none of the products. We are going to start with this one first.

In lesson seven, we have a bunch of stuff we are going to do in this one. We are going to create a customer temp T to store this data in a temporary table. We are going to optimize our loop. We are going to convert it from a for loop to a while loop. They run better and you will see why.

We will make buttons to select all of the products and clear all the product selections. Then we will put in the logic to do all products, any products, or no products. There is a whole bunch of if then logic we have to throw in there. We will make a function to count the number of items that are selected. If no items are selected, we will show nothing. That is all covered in lesson seven.

Intro

Quiz

Q1. What is the main purpose of using a composite key in a Microsoft Access table?
A. To prevent duplicate records based on multiple fields
B. To allow duplicate records in the database
C. To use only one field as the primary key
D. To increase database size

Q2. What does the SQL NOT IN clause do in the context of today's lesson?
A. Finds records that match a given list
B. Deletes records that match a given list
C. Finds records not present in a given set
D. Sorts records alphabetically

Q3. Why might you use VBA and the DCount function instead of a composite key for preventing duplicates?
A. To give users a custom warning and more flexibility
B. So you can only add unique records
C. Because composite keys are not allowed in Access
D. To speed up query execution

Q4. In the lessons, why is it important to prevent adding the same product to an order more than once?
A. To avoid duplicate billing and confusion
B. To allow customers to order as many as they want
C. To reduce the number of products in inventory
D. To save space in the database

Q5. Which Microsoft Access feature is primarily used to allow users to select multiple products for filtering customers?
A. Multi-select list box
B. Combo box
C. Text box
D. Radio button

Q6. What is the benefit of creating a query to find customers who have NOT ordered a particular product?
A. For targeted marketing and follow-up
B. To delete those customers
C. To increase the price of the product
D. To limit product visibility

Q7. Why should you avoid skipping levels in the AccessLearningZone.com course sequence?
A. Because lessons build on knowledge from previous levels
B. Because videos do not play out of order
C. To keep your subscription active
D. Because Access only works in sequential order

Q8. What is the main difference between Access 365 and Access 2021, according to the video?
A. Access 365 is subscription-based and always updated
B. Access 2021 has more built-in functions
C. Access 365 no longer allows VBA programming
D. Access 2021 is web-based only

Q9. What lesson covers showing a list of customers who have not ordered a specific product using a text search?
A. Lesson four
B. Lesson two
C. Lesson six
D. Lesson seven

Q10. When optimizing loops to process selected items efficiently, what change is suggested in the video?
A. Switch from a for loop to a while loop
B. Use only for each loops
C. Remove loops entirely
D. Use nested queries

Answers: 1-A; 2-C; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A

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 is Microsoft Access Developer Level 36. I'm your instructor, Richard Rost.

In this class, we're going to focus on strategies to prevent duplicate records in your Access databases. We'll look at practical examples, such as ensuring that the same product cannot be added twice to a single order. We'll also cover composite keys and how you can use the SQL "not in" clause to check if a value already exists among your records.

One of our main goals is to develop methods to make sure no duplicate products are placed on an invoice. To start, I'll show you how to use composite keys, which are made up of two or more fields, to stop duplicate entries at the table level.

Next, we'll explore another method that doesn't rely on composite keys. With a bit of VBA code and the DCount function, we can check for duplicates before adding them. This approach lets you give users a custom warning, allowing them to decide if they want to add a duplicate anyway, rather than just seeing a stock system error.

Another topic for today is learning how to use the SQL "not in" clause with combo boxes. This way, when you're adding products to an order, only products not already on the order will show up for selection. This helps prevent the user from selecting the same product multiple times.

We'll also be tackling the "have not ordered" scenario. I'll show you how to create queries that find customers who have not ordered a specific product. We'll start with a simple example using a single product that the user types in. Later, we'll upgrade to using a product list, allowing users to select products and see which customers have or have not purchased them. This function is great for marketing purposes. For instance, you could quickly find all customers who have never purchased a specific item and then target them with marketing materials.

We'll build on that by incorporating a multi-select list box. This lets you select two or more products and then check which customers have purchased all of the selected products, any of them, or none at all, depending on your needs.

Part of this process involves working with a temporary table to store our results, optimizing our VBA code for better performance by switching from a for loop to a while loop, and adding features like "select all" and "clear selection" buttons for user convenience.

Before taking this class, I highly recommend completing all previous Developer levels, especially Developers 15, 17, and 19, since we rely heavily on techniques like multi-select list boxes that were covered there. My course structure is sequential, so it's important not to skip around since each class builds on the material before it.

I'm currently using Access 365 for these lessons, which is mostly equivalent to Access 2019. Access 2021 is the latest retail version, but there are only minor differences that won't affect what we're learning today. Almost everything here will also work in earlier versions, back to 2007.

If you have any questions about today's material, use the discussion area on the lesson page. My team of admins and moderators is quick to respond there, usually even before I can. For general Access questions not specific to this class, use the Access Forum.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Preventing duplicate products on orders with composite keys
Using VBA and DCount to allow controlled duplicates
Custom warning messages for duplicate entries
Filtering product combo box with SQL NOT IN clause
Generating queries for customers who have not ordered a product
Searching customers without a product using product name text
Picking products from a list to check customer orders
Using multi-select list boxes to analyze customer orders
Finding customers who ordered all selected products
Creating a temporary table to store customer data
Optimizing VBA loops from for to while
Adding Select All and Clear All buttons for product selection
Counting selected items in a multi-select list box
Logic for all, any, or none of selected products in orders

Primary Topics

preventing duplicates, composite keys, SQL NOT IN clause, multi-select list box, DCount, custom validation, customer-product queries

Secondary Topics

VBA usage, temporary tables, loop optimization, user interface features