Introduction

Welcome! Find Duplicates & No Orders

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

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 Introduction
Get notifications when this page is updated

Intro

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.

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

Article

Welcome to this Microsoft Access tutorial where we will focus on techniques to prevent duplicates, use composite keys, and work with the SQL NOT IN clause to manage your data more effectively. The examples in this article revolve around a common business scenario: managing product orders, making sure each product is only added once per order, and identifying customers who have or have not purchased specific products.

We will start by looking at how to prevent duplicate products from being added to an invoice. In a typical order entry database, you might have an OrderDetail table that records each product on each order. It is important to make sure that the same product is not added to the same order more than once, unless you really want to allow this. The most reliable way to prevent duplicate entries is by using a composite key. In your OrderDetail table, you would define the combination of OrderID and ProductID as the primary key, rather than just having an AutoNumber ID. This means the same product can only appear once per order. If a user tries to add the product again, Access will show a warning that this combination already exists in the table, effectively blocking duplicates at the database level.

Sometimes, however, you may want to allow the same product to be added twice, or you want to give the user a custom warning rather than the standard Access error. To achieve this, you can use a little bit of VBA code with the DCount function to check for duplicates before allowing the insert. Suppose you have a form where users add items to an order. In the BeforeInsert or BeforeUpdate event, you could add code like this:

If DCount("*", "OrderDetail", "OrderID = " & Me.OrderID & " AND ProductID = " & Me.ProductID) > 0 Then
MsgBox "This product is already on the order. Do you want to add it again?", vbYesNo
' If you want to block it, use Cancel = True
End If

With this method, you can customize the message and even let the user decide if they want to insert a duplicate.

Another useful technique is to use the SQL NOT IN clause to limit which products are available to add to an order. For example, if you want to prevent users from even seeing products that are already on the current order, you can filter your product combo box so it only shows products that have not been added yet. To do this, use a Row Source similar to:

SELECT ProductID, ProductName FROM Products
WHERE ProductID NOT IN
(SELECT ProductID FROM OrderDetail WHERE OrderID = Forms!OrderForm!OrderID)

This approach means that after a product is added, it disappears from the combo box, removing the risk of accidental duplicates.

Beyond preventing duplicates, you may want to do some marketing analysis, such as finding customers who have not ordered a specific product. You can create a query that uses the NOT IN clause to identify customers who have not purchased an item. For example:

SELECT CustomerID, CustomerName FROM Customers
WHERE CustomerID NOT IN
(SELECT CustomerID FROM Orders
INNER JOIN OrderDetail ON Orders.OrderID = OrderDetail.OrderID
WHERE OrderDetail.ProductID = [Enter ProductID])

With this SQL, you can prompt the user to input a product, and the query will return all customers who have never ordered it. This is great for targeted marketing campaigns.

If you want to work with a list of products instead of just one, you can use a multi-select list box to let the user pick the products they are interested in. Then you can build queries to show customers who have ordered all of the selected products, any of the products, or none of them. For example, to find customers who have ordered all selected products, you would write code that loops through the selected items in the list box, and for each one, checks that the customer has at least one order containing the product. You can store the temporary results in a temp table if needed and use a loop to check conditions. Here is a simplified example of counting matches:

Dim i As Integer
Dim ProductCount As Integer
ProductCount = 0

For i = 0 To Me.ProductList.ItemsSelected.Count - 1
If DCount("*", "OrderDetail", "CustomerID = " & Me.CustomerID & " AND ProductID = " & Me.ProductList.ItemData(Me.ProductList.ItemsSelected(i))) > 0 Then
ProductCount = ProductCount + 1
End If
Next i

If ProductCount = Me.ProductList.ItemsSelected.Count Then
' This customer has ordered all selected products
End If

To select all or clear all products in a list box, you can create buttons with simple code. For selecting all:

Dim i As Integer
For i = 0 To Me.ProductList.ListCount - 1
Me.ProductList.Selected(i) = True
Next i

For clearing all:

Dim i As Integer
For i = 0 To Me.ProductList.ListCount - 1
Me.ProductList.Selected(i) = False
Next i

Handling multiple product selections can get complex if you want to support "all products," "any products," and "no products" options. You will need to add logic to process the selections and filter customers accordingly. For example, for "any products," you would include customers who have purchased at least one of the selected products. For "no products," you would look for customers who have not purchased any of them.

If you are working with temporary results, it may be helpful to use a temporary table (such as CustomerTempT) to store filtered customer IDs. This can make further processing or reporting much quicker.

With these tools and techniques, you can effectively manage duplicates, use composite keys to enforce data integrity, provide users with friendly warning messages, and generate dynamic marketing queries to find targeted groups of customers. Keep experimenting with SQL queries, combo box and list box controls, and VBA to gain full control over your Access applications.