|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Access Developer 36 Lessons Welcome to Access Developer 36. Total running time is 1 hour, 19 min, plus 1 hour, 12 min of FREE bonus material. Lessons
Database FilesLinks
Resources
Questions?Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you! Subscribe for UpdatesIf you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.
IntroIn lesson 36 of the Microsoft Access Developer series, you will learn how to prevent duplicate records using composite keys, apply the SQL NOT IN clause to filter records, and work with queries to find customers who have or have not ordered specific products. We will cover approaches to handling duplicate products on orders, generate dynamic product lists, use multi-select list boxes, and create logic for various customer-product scenarios to support marketing efforts. You will also see how to use temporary tables, optimize code with loops, and implement user interface improvements for product selection.TranscriptWelcome 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 move on to "show me customers who have ordered any products from a list," "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. I especially 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 today, so watch those three classes if you have not yet. I also recommend you do not skip levels. My classes are designed to be viewed 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 strange what they are doing with the versioning now. Access 365 is subscription-based; basically, you are constantly getting the newest features that they release. 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 have added 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 will probably work going back to 2007, so you are fine. 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; it says "post a new discussion" or "start a new discussion" - something like that. 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. 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, and 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, which 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, and 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 can 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 things we are going to do. We are going to create a customer temp table 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, and if no items are selected, we will show nothing. That is all covered in lesson seven. QuizQ1. What is a composite key used for in Microsoft Access as discussed in this lesson?A. To store multiple pieces of information in one field B. To prevent duplicate records based on a combination of fields C. To encrypt sensitive data in the database D. To automatically back up tables Q2. Which SQL clause was introduced to help filter out items that are already part of a set of records? A. JOIN B. ORDER BY C. NOT IN D. SELECT DISTINCT Q3. What is the purpose of using the DCount function and a little bit of VBA in lesson two? A. To generate a random number for each record B. To prevent duplicate entries but give users the option to override with a custom warning C. To lock the table for editing by certain users D. To sum all values in a field Q4. Why is it recommended to view the Microsoft Access Developer lessons in order? A. Because each lesson is independent and unrelated B. Because materials and concepts build on previous lessons C. Only advanced users need to follow the order D. There are no prerequisites for any lesson Q5. What is the advantage of showing customers who have NOT ordered a particular product? A. Allows you to delete them from the database B. Useful for marketing to target those customers with specific promotions C. To export their data for backup D. To show profit margins Q6. In lesson six, what does the multiple select list box help to determine? A. Customers who have paid their invoices B. Customers who have ordered all, any, or none of selected products C. Products that are out of stock D. Employees who processed sales Q7. What is the reason for converting a FOR loop to a WHILE loop as explained in lesson seven? A. WHILE loops are required for VBA B. WHILE loops run faster and are more efficient in some logic C. FOR loops cannot be used in Access D. It is required for composite keys Q8. How can you post questions about the class material? A. By emailing Richard directly B. By posting in the discussion box at the bottom of the page C. By texting an administrator D. By calling the Access customer support number Q9. What is one function of the customer temp table created in lesson seven? A. Permanently store customer phone numbers B. Store temporary data for processing customer-product queries C. Delete all products from inventory after each order D. Automatically generate reports Q10. What is the main functionality being added to the product combo box with the NOT IN clause? A. Show all products regardless of order B. Remove products already on order from the list C. Highlight discounted products D. Add new products to inventory Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-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. SummaryToday's video from Access Learning Zone covers Microsoft Access Developer Level 36. I am your instructor, Richard Rost. In this class, we focus on strategies to prevent duplicate entries in an Access database, working extensively with composite keys and the SQL NOT IN clause to identify if a record already exists in a set.One practical example we explore is how to ensure you cannot add the same product to an order more than once. We will also look at creating lists of customers who have not placed an order for certain products. We begin by examining single products and then expand to more complex scenarios, such as finding customers who have ordered any, all, or none of the items from a selected group of products. This functionality is especially useful for marketing purposes, like targeting customers with promotions based on their purchase history. Before beginning this course, it is important to have completed Access Developer Level 35 and all prior developer courses, including the beginner, expert, and advanced series. The material in today's lesson builds on what was taught in earlier levels, so I recommend watching them sequentially. In particular, reviewing Developer Levels 15, 17, and 19 will be helpful, because those lessons detail how to work with multi-select list boxes, a key component of today's material. Today, I am using Access 365, which is similar to Access 2019. There have been updates to Access 2021, but the differences are minimal and will not affect the lessons. In general, the concepts and techniques we cover today are compatible with versions back to Access 2007. If you have questions about the class material, please submit them at the bottom of the webpage you are on, using the "post a new discussion" or "start a new discussion" option. Avoid sending questions by email so that our team of moderators and admins can assist you promptly—often even faster than I can respond. For questions on unrelated Access topics, you can use the forums available on my site. Here is an overview of what we will cover in each lesson of this class: Lesson one demonstrates how to prevent duplicate products from being added to an invoice by utilizing a composite key. Lesson two introduces another approach for preventing duplicate values without relying on a composite key. This time, we employ VBA along with the DCount function. With this method, users can choose to add duplicates if necessary, and we can provide a custom warning message instead of the default one. Lesson three focuses on the SQL NOT IN clause. By modifying the product combo box on our form, we restrict its options so that users only see products not already included in the current order, reducing the risk of duplicate entries. Lesson four explains how to build a query that generates a list of customers who have not purchased a specific product. For this part, we allow users to enter the product name, and the search identifies relevant customers. In lesson five, we expand on the "have not ordered" concept with a more advanced database structure. This allows you to select products from a list and see which customers have or have not ordered them, which is useful for targeted marketing campaigns. Lesson six moves into working with a multi-select list box. Here, you'll learn how to identify customers who have ordered all of the products you select. Subsequent lessons cover identifying customers who have ordered any or none of the selected products. In lesson seven, we cover several critical enhancements. We create a temporary table to store customer data, refactor our code for better performance by swapping a for loop with a while loop, and add buttons for quickly selecting or clearing all products in the list. We also implement logic for handling the various query types (all, any, or none of the products), create a function to count the number of selected items, and make sure that no results are shown when nothing is selected. A complete video tutorial with step-by-step instructions for everything discussed here is available on my website at the link below. Live long and prosper, my friends. Topic ListPreventing duplicate products on an order with composite keysUsing VBA and DCount to prevent duplicate values Displaying custom warning messages for duplicates Using SQL NOT IN clause to filter product combo box lists Removing selected products from combo box options dynamically Querying customers who have not ordered a specific product Building queries based on entered product names Selecting products from a list to find unpurchased items Identifying customers who have ordered a selected product Using multi-select list boxes to select multiple products Finding customers who have ordered all selected products Finding customers who have ordered any selected products Finding customers who have ordered none of the selected products Creating a temporary table to store customer results Optimizing loops by converting for loops to while loops Adding buttons to select all or clear all product selections Implementing logic for "all," "any," or "none" product selection Counting the number of selected products in a list box Displaying no results when no products are selected |
||||||||||||||||||||||
|
| |||
| Keywords: access developer 36 lessons PermaLink How To Prevent Duplicates With Composite Keys, SQL NOT IN, And Customer Product Queries in Microsoft Access |
