|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Highlight Duplicates By Richard Rost 
6 years ago
Highlight Duplicate Values with Conditional Formatting Learn how to use conditional formatting to show duplicate values in a field / column at a glance in both Microsoft Access and Excel. Jerome from Argentina (a Silver Member) asks: In Excel, you can show values in a column that are duplicates using Conditional Formatting – Highlight Cells Rules – Duplicate Values. Is it possible to do the same thing in Access? MembersI'll show you how to create a query to show duplicate customers based on any number of fields you choose. So you can say "show me all of the customers that have the same first name, last name, and address." You will learn how to create a Cartesian 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! LinksDCount: https://599cd.com/dcount
IntroIn this video, I will show you how to highlight duplicate values in a Microsoft Access form using conditional formatting, similar to the duplicate highlighting feature in Microsoft Excel. You will learn how to create a query with a calculated field using the DCount function to count duplicates, adjust the form's record source, and apply conditional formatting rules to display duplicate last names. This tutorial is a step-by-step guide to making duplicate values stand out in your Access forms.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor.In today's video, I'm going to show you how to find duplicates with conditional formatting. Today's question comes from Jerome in Argentina, one of my Silver Members. Jerome says in Excel, you can show values in a column that are duplicates using conditional formatting, highlight cells rules, duplicate values. Is it possible to do the same thing in Access? Well yes, Jerome, it definitely is possible although it requires a little bit more work. Let me show everyone else what you mean by how this works in Excel first. Here I am inside of Microsoft Excel and I've got a list of names here, first name, last name, and I want to see at a glance which ones of these names are duplicates. So I'm going to simply highlight the column, under conditional formatting, highlight cells rules, duplicate values, and you can see right away they show up in red, with light red fill and dark red text. There you go. It's that simple in Excel. Unfortunately, in Access, you can do it but it's not that easy. You have to first count up how many of those last names exist in that field and then we can use that value to do some conditional formatting. So let me show you how it works now in Access. I'm going to start with my blank customer contact database and you can download a free copy of this template from my website. I'll put a link down in the description below the video. In this database, I've got a customer list form right here. This is a continuous form and I want to see which last names are duplicates. First, we have to create a query that has a count of how many times this last name shows up. To do that, we're going to use the DCount function. If you've never used DCount before, it's very similar to DLookup. I'll put links to videos for both of those down below in the description. Go watch those first then come back here once you have an understanding of how to use DCount and DLookup. I'm going to close this. Let's go over and create a query. Create, query design. Got a lot of stuff showing up here. Let's close this property sheet. We don't need that. Here's my query right there. I'm going to add my customer table to the query, that's all we need. Now in here, I need the fields that I need to have inside of that customer list form, which right now I believe are just first name and last name. Let's take a look. ID, first name, and last name. Okay, so let's bring those fields into here. So we've got customer ID, first name, and last name. Now in the next column over here, I need a calculated field. I've got videos on making calculated fields in queries. If you're not sure how to do that, I'll put links down below. But it's going to look like this: We're going to create a field called lncount: It's going to be equal to DCount, and then inside of parentheses, inside of quotations, what are we counting up? The last name field, comma, from what table, CustomerT, and what's my criteria? Last name equals the current last name. So it's going to be quote, quote, quote, ampersand last name, ampersand, quote, quote, quote. If you don't understand why I've got all those quotes, I have other videos explaining why this works. It's when you need double double quotes inside of other quotes. Essentially, we have to say last name equals quote, Rost, unquote. These little double double quotes in there become the opening quote and these over here become the close quote. I have to have all those enclosed inside of quotes. Again, watch that video for more explanation on how this works. Now I'm going to save this as my CustomerQ. If I run this query now, go to datasheet view, you'll see I get a count over here for how many times this last name appears. Let's change one of them. Let's make this James Rost. Now you can see that goes to two. If I click on this one up here, that'll update as well. That's the count of how many times this name appears in this column. Now I can use lncount in my conditional formatting. It's got to be in the underlying query in order for you to use it. So let's close this. Let's go back to this customer list. Now in the customer list, we're going to have to change the record source to the CustomerQ, the query that I just made. Now that I've done that, I can use that lncount value for my conditional formatting. Click on last name, go to Format, Conditional Formatting. Last name is shown up here. We're going to add a new rule. Down here, we're going to pick "Expression Is." I have other videos explaining what this "Expression Is." Go watch those videos first before you continue. What's our expression in here? We're going to check to see if lncount is greater than one. Make sure you put lncount inside of brackets. Otherwise, Access will put quotes around that looking for the actual value "lncount." That's not what we want. Now set whatever you want your format to be. Let's say I want them to be bright yellow. Hit OK. Hit OK again. Save it. Close it. Open it back up again. There you go. Now any last name that is a duplicate value will show up in bright yellow. Want to learn more about finding duplicates? In the extended cut for members, I show you how to put together a query where we can go through our customer table, or any table you want, and find duplicate records based on any number of fields that you want. So if you want to see all of your customers that have the same first name, last name, and state, or first name, last name, and address, or zip code, or email address, or any of those fields - whatever fields you got - I'll show you in this extended cut video how to put together a query. It'll show you, OK. Customers 5 and 1 are duplicate values based on the fields you selected. We'll create something called a Cartesian product. That's in the extended cut for members. How do you become a member? Click on the join button below the video. All of our members will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the join button you'll see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free. QuizQ1. What feature in Excel allows you to highlight duplicate values in a column?A. Conditional Formatting using Highlight Cells Rules B. Data Validation using Custom Rules C. Sort and Filter options D. Pivot Tables Q2. In Microsoft Access, what extra step is necessary before highlighting duplicates compared to Excel? A. You must manually sort the data B. You must count the occurrences of each value using a function C. You must export your data to Excel D. You need to enable macros Q3. What built-in Access function is recommended to count how many times a value appears in a table? A. DCount B. DSum C. DMin D. DFirst Q4. When using DCount to count duplicates, what part of the expression finds the current field value? A. Concatenating quotes and the field with & B. Typing the value directly C. Using the field without brackets D. Using numerical field indexes Q5. Why should you use brackets around lncount when setting up the conditional formatting rule in Access? A. Otherwise Access may interpret it as a string and not a field name B. To enable sorting in the query C. To highlight the field in blue D. To make the formatting more visible Q6. What type of form was used in the demonstration to display the customer list? A. Continuous form B. Split form C. Single form D. Datasheet only Q7. Which expression is used in conditional formatting to highlight duplicates in Access? A. Expression Is [lncount] > 1 B. Value Equals "Duplicate" C. Field Is Not Null D. lncount = 0 Q8. What action do you need to take after creating your query with lncount to use it in your form's conditional formatting? A. Change the form's record source to the new query B. Run a macro C. Add a new table D. Convert the form to a report Q9. What will happen in Access if you do NOT add lncount to the underlying query before using it in conditional formatting? A. The conditional formatting will not work B. The database will crash C. It will ignore the formatting rule and show a warning D. It will sort the data incorrectly Q10. What advanced topic related to finding duplicates is mentioned as being in the extended cut for members? A. Building a query for duplicate records based on any combination of fields B. How to create custom data entry forms C. Exporting Access data to Excel D. Using VBA to automate sorting Answers: 1-A; 2-B; 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. SummaryToday's video from Access Learning Zone demonstrates how to identify duplicate values using conditional formatting in Microsoft Access, similar to what many users are familiar with in Excel.One question I often receive is whether Access can highlight duplicate values in a column, just as Excel does with its built-in conditional formatting feature. In Excel, you select a column, use the conditional formatting menu, choose highlight cells rules, and select duplicate values. Immediately, duplicate entries are visually highlighted, making them easy to spot. Access, on the other hand, does not offer such a straightforward, built-in solution. Achieving the same effect requires some additional work, but it is entirely possible. Let me walk you through the process step by step. I am working with my customer contact database, which is available as a free template on my website. Within this database, I have a form displaying a list of customer records in continuous form view. My goal is to highlight any last names that appear more than once, indicating duplicates. The first step is to determine the frequency of each last name in the table. To accomplish this, we create a query that counts how many times a given last name appears. We use the DCount function for this purpose. DCount operates much like DLookup if you are familiar with that function. If you have never used DCount or DLookup, I suggest checking out my supporting video tutorials on these functions for a more thorough understanding before proceeding. Once you are comfortable, return to Access and create a new query in design view. Add the customer table to this query. You only need to bring in the ID, first name, and last name fields—these are the relevant ones for our current purpose. Next, we create a calculated field to determine how many times each last name occurs. This field, which I call lncount, uses the DCount function. The criteria set in the function ensures we count only records with the same last name as the current row. If the method for writing this criteria statement, with all its extra quote marks and ampersands, is confusing, be sure to review my video specifically covering how to structure such expressions in Access. It is important to get this part right for the function to return the correct results. After creating the lncount calculated field, save the query as CustomerQ. When you run the query and view the datasheet, you will see a new column showing the count of each last name. If a last name appears more than once, its count will reflect this. For example, if you change one of the last names so that two records share the same last name, both will display a count of two. Now that we have a way to determine duplicates, we can utilize conditional formatting. The key is that any field you want to use in conditional formatting must be present in the form's underlying query or record source. In this case, make sure the customer list form is now using the CustomerQ query you just saved as its record source. With that set up, select the last name control on your form and open the conditional formatting options. Create a new rule and select "Expression Is" as the rule type. The expression should check if lncount is greater than one, indicating the value is a duplicate. Be careful to place lncount inside square brackets so Access interprets it correctly. Then, set your desired formatting, such as a bright yellow background, to make duplicates stand out visually. Save and close everything, then reopen your form. Any last names appearing more than once should now be clearly highlighted, making it easy to spot duplicates at a glance. For those interested in exploring this topic further, in the Extended Cut for members, I provide an in-depth look at finding duplicate records based on any combination of fields. For example, you can search for duplicates by first name, last name, and state, or by email address, or by any fields relevant to your situation. I cover how to build queries capable of identifying these duplicates and discuss how a Cartesian product can assist with this process. To access extended cut content and other bonuses, members can join the site by following the instructions you will find below the video. However, all my regular TechHelp tutorials, like this one, remain free for everyone for as long as you keep watching. 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 ListDemonstrating duplicate highlighting in Excel with conditional formattingCreating a query in Access to count duplicate values Using the DCount function in Access queries Building a calculated field for duplicate counting Saving and running the query to show duplicate counts Updating the continuous form's record source to use the new query Applying conditional formatting in Access forms based on query results Using the "Expression Is" rule for conditional formatting Formatting fields to highlight duplicates in Access forms |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access find duplicates, show duplicate records, conditional formatting, excel, msexcel, #excel, #msexcel, highlight cells rules, duplicate values, dcount, dlookup, calculated query fields, double quotes, expression is, cartesian product, display PermaLink Highlight Duplicates with Conditional Formatting in Microsoft Access |
