|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Combining Names By Richard Rost 
5 years ago
Combining Names of Couples with Nested IIFs In this video, I will show you how to combine the names of couples if their last names are the same OR display them separately if different. We'll use Nested IIF functions. William from Dallas, Texas (Paid TechHelp Question) asks: I'm working on a membership database where I would like to show a single name if there's only one person. If it's a couple, I'd like to combine them if their last names are the same. For example, I could have: John Smith OR John and Amy Smith OR John Smith & Amy Jones. How can I do this in Access? MembersMembers will see how to write a custom VBA function to perform the same task, which I prefer.
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!
Links
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, married, couples, spouses, combine names, partners, iif function, nested iif functions, combine first last names couples, concatenate, same last name, different last name
IntroIn this video, I will show you how to combine the names of couples for use in addresses or greetings in Microsoft Access. We will work through creating a query using nested IIF functions to handle different scenarios, such as showing a single name when there is only one person, combining names when the last names match, and formatting them correctly when the last names differ. You will learn how to set up these fields in your table and use conditional logic to display names the way you want in your queries.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In this video, I am going to show you how to combine the names of couples in addresses or greetings in Microsoft Access. Today's question comes from William from Dallas, Texas. This is a paid TechHelp question. I do offer paid questions if you want to get your question to the top of the queue. You will find a link down in the section below. William says, "I'm working on a membership database where I would like to show a single name if there's only one person. If it's a couple, I'd like to combine them if their last names are the same. For example, I could have John Smith or John and Amy Smith or John Smith and Amy Jones. How do I do this in Access?" Well, William, there are a couple of different ways you can do it. You can do it with a couple of tricky nested if functions. I am going to show you that in just a second, or you can do it with a custom VBA function, which I will show in the extended cut. Let's see how to do it with if functions first. If you have never used the if function before, the immediate if, I have got another video. Go watch that first. On my website, there is the link. I will put a link down below. You can click on it. Go watch that so you understand the if function. This is going to be a little trickier. This is going to involve nested if functions, an if function inside of another if function. Go watch that one first and then come back. All right, so let's take a look at how we are going to set this up. Here is my free TechHelp template. You can download a copy of this off my website if you want to. It's free. There is a link down below in the link section. In here, I have got a customer form. Now let's say you are doing memberships and you want to have, in addition to the primary customer, the customer's partner or spouse or whatever. Now generally, like I talk about on my genealogy databases, I like keeping people as separate records. You would have a link to them, maybe pick it with a combo box or whatever, and link it to a second record. That is the best way to do it. However, if it's a simple database, you just want to put the husband and wife together in one table, that's fine too. A lot of people do that. Let's go to the customer table, design view. All right, I have got first name and last name here. Let's insert two rows and we will put a second person in here. Again, there is nothing really wrong with doing it this way. If you have got a simple database, you don't have to keep everything perfectly database normalized and all that. I am going to put in here other first and other last. I like to keep the terms generic. You don't know which one is which: husband, wife, or if it's two husbands, or it could be an unmarried couple. It doesn't matter. All right, so you have got spots in here for two people. Now, other might be blank. That is something else you have to think of in consideration. So let's save this and let's put some data in here. All right, let's go to Datasheet View or click Datasheet View. I will put my girlfriend in here. Do not tell her I put her in here. She will get mad at me. And then let's put in James Kirk is Carol Marcus. And Deanna Troi. Now, we have got Deanna Troi and Will Riker in the table already. Let's say that here we are going to put in William Riker right there. And Jean-Luc was never married, but let's put in here an Anna Picard just so we can see two similar names combining together. Will Riker and Malcolm Reynolds will leave those blank. Just so we can see what it looks like when they are blank. Now, let's head over to make a query and we will use a couple of if functions. First we will say, if this is blank, either one of these, let's say you have got to have both names in order for this to work. If either one of these is blank, then just display this. If the last names are the same, I want to see Jean-Luc and Anna Picard. And if they are different like these guys, you want to see James Kirk and Carol Marcus. Here is how we do that. Let's go over to Create Query Design, bring in the customer table. Bring in first name, last name, other first, other last. Here is where our greeting is going to go. Now I am going to Shift F2 to zoom in. This is going to get a little crazy. I kind of actually prefer doing this with VBA, which is going to be covered in the extended cut. This gets kind of nuts, but it's not that bad. The benefit of the VBA function is you can use it anywhere. You can use it in forms, reports, whatever. This just works in this query, so if you want to use this in a form or report, you have got to base it on this query, or you have got to copy this function in other places. If you do it as a VBA function, you can just use it once and call it anywhere. So let's tackle the single person first. So if IsNull(other first), now you could put Or IsNull(other last). Let's just say if there is no first name for the second person, then forget about it. If IsNull(other first), what to put in here if that is true: we are just going to put first name, last name, so it's going to be first name and a space and last name. Otherwise, in here is where we are going to put if it's a couple. Let's just test this first. So if IsNull(other first), just put in here first name and a space and last name. This is concatenation, by the way. If you do not know concatenation, go watch that video. But if you watched the if video, then you have watched the concatenation video because that's a prerequisite to the other one. Every video has prerequisites, so you have to watch all the prerequisites. Otherwise, we will just throw xxx in here just for now, just to test it, just to make sure that what we have got here so far works. Let's save this as couple query or whatever. Run it. So if they are missing a first name, you get just Will Riker. All of these are xxx because they are couples. Now we will deal with the couples. Back to design view. I just put a little shortcut buttons up here on my quick launch toolbar. You can just open up the full menu there if you want to. That's so you can fit more on the screen for you. Let's go back in here. Zoom in, Shift F2. Now let's deal with this part of it. This is going to be where the nesting goes in, another if function. If we get to this point, that means we have got a couple. Now I am going to say if, and then open/close parentheses and put something in the middle here. I like to do that because then I do not end up with unmatched parentheses at the end. I always get that problem. Now I am going to say if the last name is the same as other last, then put them together that way. If last name equals other last, and notice how I do not have to put in all those square brackets myself because I did not use spaces in my names. If last name equals other last, that means it's the same. Let's do first name & " and " & other first & " " & last name. Just the xxx's are just a placeholder. So what I have said here is that if the last names match, I want to say Jean-Luc and Anna Picard. We are taking it one step at a time. Oh, invalid syntax. What did I miss? I probably missed a function here. I got something out of place. I am missing an ampersand right there. There we go. Hit OK, and now run it. These two last names match and are the same, so there is Jean-Luc and Anna Picard. Now everything that is left is you have got two different last names, so now we will just put that together like so. Replace this. This is going to be first name & " " & last name & " and " & other first & " " & other last. I told you it was going to get a little crazy. Other last. There is the final formula. Hit OK. Make sure it works and then run it. And there you go. Now, I do not want the word 'and' in there. I want to replace that with another ampersand, but it gets a little confusing to do it the first time that way. So I am going to take the word 'and', which is right there, and replace it with one of those. It just looks better. And then this one is a little more confusing because you have got the ampersand actually inside the string. There is your final function. Write it down. Copy it. Whatever you want to do. You can download this database. Hit OK, and now run it. There is your greeting. James Kirk & Carol Marcus. Jean-Luc & Anna Picard (same last name). Will Riker, right? Nobody is in this field. Now that's the way to do it with a query. In the extended cut for the members, I will show you how to make a custom VBA function that you can call from anywhere in the database. In the extended cut for the members, I will show you how to build a VBA function called couple greeting where you send it the first name, last name, other first, other last, and then it will put them together for you and return the proper greeting. The benefit of this is you can use it anywhere in the database. You can use it in forms, reports, other queries, and so on. That's in the extended cut for members. All of our members and up get access to all of my extended cut videos. Gold members can download all the templates. How do you become a member? Click the join button below the video. After you click the join button, you will see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. 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. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the show more link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. And if you like Level 1, Level 2 is just one dollar. And it is also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the main purpose of this video tutorial?A. To teach how to combine names of couples for addresses or greetings in Microsoft Access B. To demonstrate importing data from Excel into Access C. To discuss database normalization in depth D. To show how to print labels from Access Q2. What is the initial suggested method for combining couple names in Access? A. Writing a macro B. Using nested if functions in a query C. Designing a report D. Using an external script Q3. Why is it sometimes acceptable to keep both partners' data in the same table in simple databases? A. It makes the database faster B. It is easier for simple needs and not every database requires strict normalization C. It always prevents data entry errors D. It allows unlimited partners Q4. What fields were added to the sample customer table to support a second person? A. PartnerName and PartnerSurname B. HusbandName and WifeName C. OtherFirst and OtherLast D. SpouseFirst and SpouseLast Q5. In the video, what happens if the "other first" field is blank when creating the couple greeting? A. The query returns an error B. Only the main person's full name is shown C. Both first names are shown D. The record is skipped Q6. How does the formula handle couples with the same last name? A. Shows both first names and the last name once (e.g., Jean-Luc and Anna Picard) B. Shows only the primary person C. Shows both names separately D. Uses only the partner's last name Q7. Which of the following is NOT discussed as a benefit of turning the logic into a custom VBA function? A. It can be used in forms, reports, and queries B. You only have to write it once C. It makes the database run faster D. It can be used anywhere in the database Q8. For situations where last names are different, how does the concatenation display the names? A. FirstName LastName & OtherFirst OtherLast B. FirstName & and & OtherFirst LastName C. FirstName LastName and OtherFirst OtherLast D. FirstName & OtherFirst & OtherLast Q9. What is the main limitation of using the nested if functions inside a query for this purpose? A. It can only be used in the query unless copied elsewhere B. It cannot compare last names C. It does not allow for empty fields D. It requires macros Q10. Where can viewers access the extended cut demonstrating the VBA function? A. In any free video on the channel B. By subscribing to the newsletter C. By becoming a channel member D. At a local Access user group Q11. What are some benefits of becoming a Gold member at AccessLearningZone.com? A. Free hardware support B. Download access to all TechHelp video templates and the code vault C. Free tickets to live events D. Personal mentoring Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-A; 7-C; 8-C; 9-A; 10-C; 11-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 focuses on how to combine the names of couples in addresses or greetings using Microsoft Access. The goal is to display a single name if there is only one person in a record, or combine two names if it's a couple. If both people share the same last name, the names should be merged appropriately (for example, "John and Amy Smith"). If the last names are different, the greeting should read "John Smith & Amy Jones." This technique is useful in membership databases or situations where you want to personalize mailings.William asked how to set this up in Access. There are two main ways to approach this: using nested if functions in a query, or by writing a custom VBA function. In this video, I cover the use of nested if statements, and I'll save the custom VBA function for the extended cut for members. Before getting started, make sure you're comfortable with Access's immediate if function, or IIf. If you're new to it, I have a dedicated lesson on my website that explains how it works. That video also covers string concatenation, which is another key concept you'll need here. In my sample database, I set up a customer table. When handling memberships, you might want to record both the primary customer and another person (like a partner or spouse). In more robust designs, such as genealogy databases, I recommend keeping each individual as a separate record and linking them appropriately, which is better for database normalization. However, for simple databases, you can store both in a single table. To keep things generic and flexible, I suggest naming the fields "Other First" and "Other Last" so you're not restricting yourself by relationship or gender. The process starts with adding those two new fields to the table. Remember, sometimes "Other First" or "Other Last" could be left blank, so any formula you use has to account for empty values. Once there's some sample data in the table, I demonstrate how to set up a query. The logic is as follows: If either name field for the second person is blank, only show the first person's name. If both are filled in, check if the last names match. If they do, use a format like "Jean-Luc & Anna Picard." If the last names are different, output both full names connected by an ampersand, for example, "James Kirk & Carol Marcus." To achieve this, the query uses nested if (IIf) statements. Start by handling the single-person case. If "Other First" is null, display only the primary first and last names. If not, you need to check whether the last names are the same. If they match, combine the first names and append the single last name at the end. If they're different, show each full name with the ampersand between them. I encourage you to test the formula in stages, using placeholders where appropriate to make troubleshooting easier. One downside of doing this with a query is that if you want to use this logic elsewhere, such as in forms or reports, you have to copy the formula or base your forms on the query. This is where a VBA function can be really helpful. With a custom function, you can call it wherever you need in your database just by providing the relevant fields. That's what I'll cover in detail in the extended cut available for members. To summarize, you now have a method to output couple names correctly formatted for both same and different last names using only nested if statements in an Access query. This is practical for simple databases, but as your needs grow, you'll probably want to take advantage of a custom VBA function for more flexibility. In the extended cut exclusive to members, I demonstrate how to build a VBA function that handles this logic. With this function, you can generate the greeting anywhere in the database by simply calling the function with the necessary name fields. This extended content, along with all extended cut TechHelp videos and sample databases, is available to members at various levels, with additional perks as you go up. You can become a member right on my website, and each level comes with different benefits, including access to sample databases, code vault, live sessions, and full courses not only for Access but also for Word, Excel, Visual Basic, and more. Whether or not you choose to become a member, there are plenty of free TechHelp videos available. If you enjoyed this lesson, I invite you to leave a comment, subscribe to my channel, and check the show more section for other helpful resources and related videos. If you have not yet tried my free Access Level 1 course, it is a comprehensive three-hour beginner course, and Level 2 is available for just one dollar or free at any membership level. If you want your questions featured in a future video, visit my TechHelp page to submit them. 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 ListCombining customer names for couples in AccessAdding fields for a second person in a table Using generic field names for partner data Handling blank fields for second person Creating a query for combined name greetings Using nested IIf functions in a query Checking if additional name fields are blank Combining names for couples with matching last names Formatting greeting with "and" or "&" between names Combining names for couples with different last names Concatenating name fields in Access queries Testing and troubleshooting name combination formulas |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access married, couples, spouses, combine names, partners, iif function, nested iif functions, combine first last names couples, concatenate, same last name, different last name PermaLink Combining Names in Microsoft Access |
