Other Names
By Richard Rost
4 years ago
Maiden Names, Nicknames, Middle Names, etc.
In this video, I will show you how to properly display maiden names, nicknames, and middle names in Microsoft Access.
Chester from New London, Connecticut (a Gold Member) asks: I'm making some modifications to your excellent Genealogy database. I've added a field for Maiden Name. How can I get that to display as "First (Maiden) Last" in my reports, if the person is female?
Pre-Requisites
Links
Recommended Courses
Usage
FullName: [FirstName] & (" """+[NickName]+"""") & (" "+[MiddleName]) & (" "+[LastName]) & (" ("+[OtherLastName]+")")FullNameAbbrMid: [FirstName] & (" """+[NickName]+"""") & (" "+Left([MiddleName],1)+".") & (" "+[LastName]) & (" ("+[OtherLastName]+")")
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, maiden name, middle name, nickname, nick name, birth name, name at birth, married name, other names, alias, aliases, penname, pen name, nom de plume, professional name, screen name
Subscribe to Other Names
Get notifications when this page is updated
Intro In this video, we'll talk about how to handle other names in Microsoft Access, including how to add and display maiden names, nicknames, middle names, and additional last names in your database. I'll show you how to set up new fields in your tables, use calculated query fields, and apply string concatenation and null math to display names correctly on your reports or forms. We'll also cover ways to display abbreviated middle names and tips for showing multiple other names, giving you flexible options for tracking name variations in your Access applications.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to deal with other names. We're going to see how to handle maiden names, nicknames, middle names, and whatever other names you can think of in Microsoft Access.
Today's question comes from Chester in New London, Connecticut, one of my Gold members. Chester says, I'm making some modifications to your excellent genealogy database. Thanks for the compliment. I've added a field for maiden name. How can I get that to display as first and then in parentheses maiden and then last in my reports if the person is female?
Well, Chester, I can definitely show you how to do that, but let me give you my thoughts first.
First off, I personally think that the term maiden name is a little outdated. Some people even find it condescending and sexist. Not going into all that though, I prefer using other last name or even last name at birth, which is especially good if you're doing genealogy.
Another reason why is that a different birth name doesn't necessarily only apply to women. Some men, for example, change their name when they marry. My fiancée is still trying to get me to take her last name. If you think DeAngelo is a lot cooler than Rost, I think Rost is awesome. It's nice and short and sweet and I've had it for 50 years, so no, I'm keeping it.
Also, adopted children sometimes keep their birth name, and they want to be able to track both for legal reasons.
Now, while we're at it, the same techniques I'm going to show you in this video can also be used to track nicknames and middle names. Why is nickname one word, but middle name is not, or maiden name or other name? Well, because nickname has been around since like the 1300s and it's a misspelling of something from Old English and it's crazy. I'll put a link down below; you can check out what I found on Wikipedia. But yes, nickname is one word. Middle name is two words.
I'll also show you how to abbreviate if you want to put in the full middle name and then abbreviate it, like James T. Kirk.
Plus, you might have multiple other names if a person has been married a few times, for example, and you want to track all of them. You can put them in the other names field and then put those in parentheses like Beverly, Bev, nickname, Picard, married name in an alternate future. Remember the series finale? And then she was Howard as her birth name, maiden name if you prefer, and then she was married to Jack Crusher, so she's got two other names. Her legal name is Picard in this alternate future. So you have nickname, legal last name, and other last names that the person has had in the past.
There are all kinds of crazy things you can do and you can do whatever you want - it's your database. You don't have to do it the way I'm doing it, but I'm going to show you a couple of different techniques. My job is to show you how to put different Legos together, and you can arrange them however you want - it's your database.
So, how do we do all this stuff? The interesting thing is, and this is why I always use the Lego analogy, I've already covered the techniques to show you how to do this stuff in a couple of my other videos. But in this video, I'm going to put those together in a different way. Like I said, playing with Access is like playing with Legos. The techniques are there; now, you just have to arrange them in a different way.
I learn things like this myself. I look at some things I see people do online and I'm like, oh, wait a minute, that's this plus that put together a different way.
First, you need calculated fields because we're going to use a calculated query field to put these different fields together - first name, last name, nickname, and so on. Go watch this video.
Next up, string concatenation. Putting together first name and last name, for example, that's concatenation. Go watch this if you've never done that before.
Go watch the Double Double Quotes video. I have to update the screen for this one; the top says concatenation, but the other one really does concatenation. This one is about the double double quotes problem. This is a big one. A lot of people get stuck on this when you put double quotes inside of quotes. You have to use double double quotes. I'm going to do some of that today. Go watch this video for more information.
Finally, go watch my video on null math. Learn how null values work when you add them together.
In a nutshell, if you add null to anything, the result is null. We're going to use that. By knowing this technique, we can do a lot of this stuff without any functions at all. In fact, we can do all of this without any functions at all. The only function we'll need is if you want to take that left character for the abbreviation for the middle name. You'll see what I'm talking about in just a minute.
Go watch all these videos. These are all free. I'll put links down below that you can click on. They're on my website and my YouTube channel. Go watch any of those if you don't know what any of this stuff is, and then come on back.
OK, so here I am in my TechHelp free template. This is a free database. You can download a copy of it off my website. Here's my customer table. Generally, for my customers especially, I only track first name and last name. For what I do, computer training, that's all I need. I am seriously going to add a nickname field, though, to my database very soon, because I've been using a notes field that's right in the middle of the customer form, like I've got here.
I keep a little notes field so I can see whenever I pull someone's record up. If someone's name is David, you don't want to just call them Dave. Some people might get offended. I have a friend named James; he does not like to be called Jim. His name is James. He wants to be called James. That's fine. My name is Richard. I don't care. Call me Rick, call me Rich. I had a coach that called me Richie. I don't really care - call me Dick if you want. My best friend growing up - we were both Richards. I was Ricky, he was Dickie. I really don't care. It doesn't matter to me. If you're going to call me by my proper title on the website, it's Fleet Admiral. Just kidding.
Anyway, so I put, if someone is okay with it, I'll put Rick here. But I want to add that as a nickname field to my database, so when it comes up and when I hit reply, it automatically goes boop, pops their nickname in there.
So how do we do all that with this? First, we have to add some fields to our table for whatever you want to track. Let's go to Design View.
I'm going to throw in here - insert a row - middle name. OK. We have last name. I'm going to throw nickname in there - insert another row. You can put them at the bottom and drag them up or whatever. Nickname, OK. And then other last name at the bottom - insert rows. Other last name, or like I said, if you're doing genealogy, you might want to put birth name in there. That's fine. For me, other last name is good; you can put multiple names in there and just put a comma between them. I know it's not proper relational database practice - you want to have a separate table. If this is a legal thing, like if you're tracking DMV records, sure, make a separate table to track additional last names or additional first names. However you want to do it. Again, it's Legos; it's however you want to set it up.
This is what I'm going to show you in this video. If you want to see how to do other last names as a separate table, post a comment down below and let me know. The squeaky wheel gets the grease. The more people want to see something, the more likely I'll be to make a video about it.
If Deanna had multiple married names, you might want to put them here - she was a Riker and then a Worf or whatever. You can put those in there and that's how they'll show up when you print it. Totally up to you.
OK, I'll leave that there for now.
Let's put some data in here. I have data. I always give a chuckle when I'm different because I've got all kinds of Star Trek stuff.
Rick. Middle name is Dennis.
Let's say James, Jim. Tiberius.
Let's do William as Will. Middle name is Thomas.
Where's Beverly Crusher? I don't think I have Beverly. Let's switch Wesley. Sorry, you're getting changed. This is Beverly. Nickname is Bev. Middle name - and yes, even I'm not this nerdy, I had to Google it - her middle name is Cheryl. Last name, Crusher.
She was born Beverly Howard and she also had Picard. We'll put Picard here, because in the alternate timeline they got married and then divorced, so maybe she went back to Crusher. I think she was called Captain Picard or Admiral Picard in the episode. I have to watch that episode again. Anyway, multiple names.
Pretty sure Tom was Eugene. I'm doing these by memory now. Anyway, you get the point.
Let's go and make a query to throw all this stuff together in the ways we want.
Create - Query Design - and yes, we want to use query fields for this. I'm going to bring in the customer table. Let's bring in just the stuff we care about: first name, last name, middle name, nickname, other last name. There we go. Run it; that's what we have so far.
Let's start with bringing in the nickname. I'm going to Shift+F2, zoom in so you can see better. We'll call this the full name.
We're going to bring in the nickname. The nickname may or may not exist. Nickname might be null, and if it is null, we can then use null math because if we add anything to it, it will just not be null.
So it's going to be first name. Normally, we'd go first name and a space and last name. To throw in the nickname, I'll say inside of parentheses, put some stuff, and then add on the rest.
In the middle, whatever is inside those parentheses, if that evaluates to null, you'll just get nothing there. That's the beauty of null math.
So what do I need in there? Well, I need a space - so quotes space. Then I need a set of double quotes. That's where the double double quotes come in. I need to go double double quotes to put a set of double quotes around Rick, but I have to close that string, so I need another set of double quotes. You get how that works? We have a space, and then this will evaluate to a single set of double quotes inside that string.
After that space open quote, I'm going to add nickname. Why add? Because if I use addition, I'm doing null math, and if this whole thing evaluates to null, nothing will display there. If I use concatenation, I'll still get the space and then the quotes in there.
So, again, I'm going to add after that another set of close quotes: quote, quote, quote, quote, quote, so on. This will evaluate to a set of double quotes inside a string, added to nickname, added to the space quotes up there in the front, and this whole thing will evaluate to null and won't display at all if nickname is null. Got it? Hit OK.
Let's save this query as CustomerFullNameQ and run it. There we go. There's our full name over here. Anybody who's got a nickname will now see the nickname in there: Richard Rick Rost, James Jim Kirk, William Bill Riker. If they don't, it's blank.
All right, so let's add the other last name or names in there. Let me get rid of - let me just make this one right. She never really technically married. There are so many alternate timelines going on in Star Trek it's hard to keep track of who did what.
I'll just add another couple here just for the sake of it. We'll put a Smith down here and go down there or whatever, just so we have some variations.
Let's put that in parentheses after their current legal last name.
Again, back to Design View. We're going back into our full name here. Shift+F2 to zoom in. We're going to add that at the end. Now, we want to use concatenation here. We don't want to use a plus here because if we plus that to anything after it that's null, then last name goes away too. Anything added to null is itself null. So, we want to use concatenation here.
In parentheses, we'll put the stuff for the other names, maiden names, whatever you want to call it.
Now, we're going to start off with quote space and then parenthesis, so put a set of double quotes there. Our stuff is going to go inside that.
What do I want to actually display? I want a space and an open parenthesis. It gets kind of crazy and weird looking with all those other parentheses around. After here, I'm going to add to that other last name, and then add to that quote, close parenthesis, quote.
So, what do we got? We got other last name. In front of it is a space. Let me do it; there we go, that's better.
We're going to concatenate on whatever is inside these parentheses. Here we've got a string that's got a space and an open parenthesis. We'll add to it other last name, which if that's null, this whole thing evaluates to null. Then add onto the end of that a close parenthesis.
Ready? There you go.
Beverly Bev Crusher Howard Picard.
If you're not subscribed yet, subscribe.
Let's tack on that middle name. Come back in here. Middle name is going to be just like nickname, except we don't need the quotes after it.
So, I'm going to copy this whole thing right here, copy and paste it right there. Let me rearrange this a little bit.
So now we have nickname, and right here we want a space, if there is a middle name. Get rid of those quotes, change this to middle name, and then we don't need anything after it. Just like that.
So, give me the nickname, and if there is a middle name, put a space there and the middle name.
We could technically do the same thing with the last name, because a lot of times with this old school method - and I only show the old school concatenation method in the beginner classes so as not to confuse people, because it's easier to teach them this than null math - but we could do the same trick with last name. We could put parentheses around this like that, and we could say and a space plus last name. That way, if there is no last name, you won't get that extra space in there, and you don't have to trim it off later.
So, you get: first name, ampersand, space, quoted nickname if a nickname exists; ampersand, space, middle name if a middle name exists; ampersand, space, last name if a last name exists, and the rest of it if other last name exists. There's a space there too. All these names are conditional.
You could, if you wanted, technically put that around first name too, but if first name is null, then that's just blank because you don't need a space in front of it.
Let's see how this whole thing works now. Run it. There you go. So now if we don't have Crusher here as a last name, let's get rid of this. You won't see that extra space in there. That works out nicely.
What if you want to see that middle name abbreviated? Let's make another field here. Let's just copy this one, copy and paste it. Now Shift+F2, zoom in.
Let's go full name abbreviated, maybe abbr_mid, whatever you want to call it. This is where we do need a function. We want to take the left of middle name, comma 1, and maybe add on to that a period, so you get T. for James T. Kirk.
Run that. There you go. Let's adjust this a little bit for space.
So, there's William Will T. Riker, James T. Kirk. Everyone thought when it was announced he was William T. Riker they were going to do an homage to the Tiberius, but they didn't. It's Thomas. Remember the episode where you've got his duplicate?
Now, if you do want to flip this around and put the married name in the middle, that's easy to do. All you have to do is just change the order that these appear in. If you want to put something else in here with the other last name, like a lot of times with maiden name, you'll see something like this; let's say you put the word "nee" in there like that.
I got an extra space in there I don't need, but you see the point. You could throw that in there if you want. All kinds of things to do.
You wouldn't want that if there's a space in front of the parentheses there, but I'm going to undo that. Let's come back out here and put it back the way it was.
If you want to insist that this extra name only works with women, you can add a gender field to your table, which I show how to do in my genealogy video. You can use an immediate if function to only have that show if gender is female, but I wouldn't go through that because, again, men can have changed names from their birth name as well. So that's how I would do it. Just don't put something in this field and it won't show up there.
So there you go. Chester, I hope that answered your question. If you have any more, feel free to post them down below or on my website. I hope you learned something and I'll see you next time.Quiz Q1. What is the main purpose of the techniques discussed in this video tutorial?
A. To manage and display multiple types of names for individuals in an Access database
B. To create security settings for databases
C. To automate report generation for sales data
D. To import data from Excel into Access
Q2. Why does Richard recommend using "other last name" or "last name at birth" instead of "maiden name"?
A. "Maiden name" is considered outdated and can be seen as sexist
B. "Other last name" requires less data entry
C. "Maiden name" is only relevant for genealogical records
D. "Other last name" is a default field in Access
Q3. Which Microsoft Access feature is used to combine multiple field values into a full name display?
A. Data macros
B. String concatenation in queries
C. Table relationships
D. Crosstab queries
Q4. Which technique is recommended for displaying nicknames in reports?
A. Store nicknames only in a notes field and ignore them in queries
B. Include the nickname in parentheses using string concatenation and double double quotes
C. Use a lookup field to select the nickname
D. Add the nickname only when printing reports
Q5. What is the purpose of using "null math" in the queries discussed in this video?
A. To prevent duplication of records
B. To avoid displaying blank spaces or parentheses when certain fields are empty
C. To filter out invalid data
D. To automatically format fields as uppercase
Q6. What is the importance of using double double quotes in Access queries?
A. It prevents SQL injection attacks
B. It allows you to include quote characters inside strings for display
C. It creates multi-line text fields
D. It enables currency formatting
Q7. How can multiple previous or alternative last names be stored, according to the video?
A. Store them as comma-separated values in one text field or use a separate table for relational accuracy
B. Always use multiple columns for each possible last name
C. Ignore previous names as they are not important for most applications
D. Use a drop-down list to select previous names
Q8. How can you abbreviate a middle name in the concatenated display?
A. Only display the middle initial using the Left function
B. Require manual entry of the abbreviation
C. Remove the middle name if it's longer than three characters
D. Use the UCase function to abbreviate
Q9. What happens if a field being concatenated is null when using the addition (+) operator in Access?
A. The other fields are still displayed
B. The result is null and the entire concatenated string is blank
C. The field is automatically replaced with "N/A"
D. The process triggers an error
Q10. If a user wants to only display 'other last name' for females, which field should be added?
A. Age field
B. Gender field with an immediate if function in queries
C. Country of birth field
D. Status field
Q11. What analogy does Richard Rost use throughout the video to describe building database solutions?
A. Building a house
B. Putting together Legos
C. Baking a cake
D. Solving a puzzle
Q12. What is the primary function of concatenation in Access queries as taught in this video?
A. To join multiple field values into a single string for display
B. To sum numerical columns
C. To remove spaces from text fields
D. To encrypt user data
Q13. According to the video, why might it be better to use a separate table for multiple names in some cases?
A. For proper relational database design and to track names more systematically
B. To speed up query processing
C. Because Access does not allow text fields longer than 20 characters
D. To prevent data loss during imports
Answers: 1-A; 2-A; 3-B; 4-B; 5-B; 6-B; 7-A; 8-A; 9-B; 10-B; 11-B; 12-A; 13-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 focuses on how to manage additional names in your Microsoft Access databases. I will show you how to incorporate and display maiden names, nicknames, middle names, and any other alternate names that you might want to track.
This topic comes from a question about modifying a genealogy database to display a person's first name, then maiden name in parentheses, followed by the last name, specifically for females. Before jumping into that, let me offer my own perspective.
Personally, I find that the term 'maiden name' feels outdated, and in some circles, it is seen as condescending or sexist. Instead, I like to use terms such as 'other last name' or 'last name at birth.' These are especially useful in genealogy work because a birth name can be significant for anyone, not just women. For instance, some men choose to change their last name when getting married, or an adopted child might retain both their birth and adopted names for personal or legal reasons.
The techniques I demonstrate here also apply to other types of names, such as nicknames and middle names. By the way, if you have ever wondered why 'nickname' is a single word while 'middle name' and 'maiden name' are two, it actually goes back to the history of the word which traces to a misdivision in Old English. There is a great explanation on Wikipedia if you are interested.
In addition to showing how to handle these name variations, I will explain how to abbreviate middle names, as in 'James T. Kirk.' If you need to track multiple alternate names (for example, if someone has been married more than once or uses various legal names), you can enter those in an 'other names' field, separated by commas. While this method does stretch good relational database design, if your database is not for strict legal records, it's often easier and more flexible for general record-keeping.
My approach is to give you the tools. Think of working in Access like using Lego blocks. I will show you simple tricks and you can decide how to arrange the pieces for your own requirements.
To handle these different names, you'll need to be comfortable with creating calculated fields in queries. That is how we bring together fields such as first name, last name, nickname, and so on into the desired format. If you're not yet familiar with calculated fields, string concatenation, handling double quotes inside strings (known as the double double quote problem), and understanding how null values behave in calculations, I recommend checking out my other free videos on these topics before continuing. Links to them are available on my website and YouTube channel.
For this example, I'll use my free TechHelp template database, which you can download from my website. Normally, I keep the customer table quite simple since most of my training business only involves a first and last name. However, I've decided to add fields for middle name, nickname, and other last names to make tracking multiple identities easier. If you want to see what a more robust relational approach looks like, with a separate table for alternate names, let me know in the comments and I may create a separate lesson on that in the future.
Once you have your table updated with the desired fields, it's helpful to enter sample data that simulates the variety you might encounter. You might have 'James' with a nickname of 'Jim,' a middle name of 'Tiberius,' and so on. In the case of a character like Beverly Crusher from Star Trek, you might include alternate last names like 'Howard' (her birth name) and 'Picard' (acquired after marriage in an alternate universe). Again, your database's requirements will shape exactly how you use these fields.
The next step is to create a query that combines these fields into a nicely formatted full name. By setting up calculated fields in your query, you can dynamically include or exclude the nickname, middle name, or alternate last names depending on whether those fields contain data. For instance, if a nickname is present, you might display it in quotation marks within parentheses after the first name. Similarly, alternate last names can be shown in parentheses after the primary last name. Using null math and careful string concatenation allows you to keep the formatting clean, so you do not get unnecessary spaces or parentheses appearing if a field is empty.
If you would like to abbreviate a middle name (for example, show 'James T. Kirk' instead of 'James Tiberius Kirk'), you can use a string function in your calculated field to extract just the first letter of the middle name and append a period. This is the one case where a function is useful, but for most of the combining logic, you can avoid functions altogether using null math and proper concatenation.
You can also rearrange the order of names or insert other descriptive text as needed (for example, adding the word 'nee' to indicate a maiden name if you wish). If you only want to display alternate names for certain individuals, such as women, you can expand your data structure to include a gender field and use conditional logic in your calculated field, though I recommend avoiding gender assumptions where possible since name changes happen for many reasons.
All of these techniques help you produce well-formatted full names dynamically, regardless of which alternative or additional names are present in your records.
If you want step-by-step help with any part of what I have covered here, there is a complete video tutorial on my website where I demonstrate every concept in detail. You can find the link below.
Live long and prosper, my friends.Topic List Handling maiden names and other names in Microsoft Access
Adding maiden, nickname, and middle name fields to a table
Designing tables to track multiple names for genealogy
Using calculated fields to combine name components
String concatenation in Access queries
Handling null values with null math in name fields
Using double double quotes for quotes in strings
Conditionally displaying nicknames in parentheses
Appending additional last names in reports
Abbreviating middle names using string functions
Creating full name display queries with conditional fields
Customizing name order and display formatting in queries
Considerations for relational design vs. single field for multiple names |
