Sex & Gender
By Richard Rost
2 years ago
Store Multiple Sex & Gender Options in Microsoft Access
In this Microsoft Access tutorial, we will learn how to store multiple options for sex and gender inclusively in your database. We'll discuss the importance of not using free-form text fields and how to create structured lists with an other option for additional flexibility.
Monica from Brookfield, Wisconsin (a Platinum Member) asks: Our office is making an attempt to be more inclusive, so management wants to start tracking our employees and customers by gender, and allow for a variety of responses such as male, female, non-binary, and so on. How do I go about storing this in my database? Do I just make it a text field now?
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, inclusive database design, storing gender options, Access gender options, multiple gender values, gender identity database, non-binary data storage, inclusive employee tracking, sex and gender fields, gender combo box, customize gender options, Access table design gender, other gender option, relational combo box Access, employee gender tracking, customer gender tracking
Subscribe to Sex & Gender
Get notifications when this page is updated
Intro In this video, we will talk about how to store sex and gender inclusively in your Microsoft Access database. We'll discuss the drawbacks of using free-form text fields, explore how to create a structured list of options with an "other" entry for flexibility, and demonstrate why triple state checkboxes can help address unclear responses. You'll also learn about setting up separate tables for sex and gender, along with using relational combo boxes to keep your data accurate and inclusive. This video is aimed at users with some experience in Microsoft Access who are looking to make their databases more comprehensive. This is part 1.Transcript Today we're going to talk about storing sex and gender in your Microsoft Access database. Now, if you're happy with just storing male and female, yeah, it's pretty easy to do that. But if you want to be inclusive and include the additional genders and sexes that have come about in the 21st century, well, we're going to talk about how to do that in today's video.
Today's question comes from Monica in Brookfield, Wisconsin, one of my Platinum members. Monica says, our office is making an attempt to be more inclusive, so management wants to start tracking our employees and customers by gender and allow for a variety of responses such as male, female, non-binary, and so on. How do I go about storing this in my database? Do I just make it a text field now?
Well, Monica, that's definitely one option, but I don't like just making it a free-form text field because then you get all kinds of crazy stuff. It's the same problem that you run into when you make country, city, state, all those kinds of fields just a text field, instead of forcing people to pick from a list. I just recently went through to try to figure out which countries I have the most students from, and so I had to go through and fix all the older records where people could just type in their country and you would not believe how many people I have from Australia that don't know how to spell Australia and they live there.
Okay, so I'm not for using just a text field to type something like this in. Also, in the interest of being inclusive, you want to be able to include whatever people want to have, you know, as their identifier. So, I like a list of the options that you know about- you know, the standards and then give people an "other" box where they can type in something that might not be on the list. That's the best way, I think, to go about it.
And I don't really want everybody just being able to modify the main list, too. You know, we've talked about the list items edit form in the past. Right, this guy. Right, where you can make a little button here and they can edit the main list. Again, if you allow people to edit the big main list, you're gonna get all kinds of crazy stuff. So that's why I suggest having one list for the items you definitely want to have available and then offer an "other" option. And they can type the other into a text box. Then you can go through once every couple of months or once a year and see what other items are being added. Right, and if you get a bunch of them that are the same, then maybe you can add that to your main list and then replace all those values.
Now, when I first started teaching Access back in the 90s- the 1890s. I'm old! You know back then we didn't have all this consciousness that we have today of being inclusive of different types of gender. So it was basically male and female. And in my earliest classes, and yes, I actually went back and found my old course materials that I used to teach in the classroom. I used to just have one field. I'd make it gender or sex, whatever you want, and I'd make it a yes-no field.
Okay, now the problem with a yes-no field is that you're limited to yes and no, but that also doesn't give you the option to say I don't know. Right? You always want that third option available which, you know, 20-year-old me didn't know this when I was teaching it in my classes. Now, if we save this table and we go to our customer form and we add that in here. Form design, add existing fields, there's my gender field, we'll drop it down here. Okay, save that and let's take a peek at what we got. Save changes, yep. Okay, here's my gender box. I got yes and I got no. Alright, male and female is the way I used to teach it. But this doesn't give me the option for, I don't know. If I turn it off, then well, is this person just assumed to be female?
So, you can do a triple state checkbox. Okay, what that means is it gives it three states. Open this guy up and if you go to data there's a triple state option right here. Now the default is no. If you set that to yes you'd think it's gonna work but it's not. Watch what happens. This is a known problem with Access. If I click on it now it doesn't do anything. Okay the trick is what you have to do is switch gender to a number field now. All right, so go back to Customer T, Design View, take gender and make this a number. Because if you remember, yes-no values basically store a number. It's either zero for no or negative one for yes. Why? That's a whole different discussion. But it's zero or negative one. Basically, look for a zero or not zero. But now null will give us the ability to store that value in there. So now my triple state checkbox actually works. See that? Right? Null looks like that with a little dash and there's yes and then there's no. Okay? The key is you gotta remember to set it to a number-type field.
And if you want to learn more about triple state checkboxes, I got a whole separate video on them. Here's a link right there. But again this only gives us those two options, basically, right, male and female. And I don't know. So what we're going to do is we're going to add in a second and a third related table to store sex and gender, right, or one or both or neither if you want, depending on what you want. And then we're going to have that as an option we can pick from with a combo box. And if it's other, we'll let the user type in other.
Now, first thing we have to do, let's get rid of this checkbox here first. We'll get rid of you. Goodbye. All right, we'll come back to this in just a minute. Before we get started, a couple of quick prerequisites for you. This is an expert-level video.
What is expert? Well, it's a little more than beginner and it's not quite developer. We don't need any programming for this stuff, but you will need to know some relationships and making combo boxes and some other more not advanced stuff, but beyond the beginner stuff. So here's a couple of videos for you to watch if you haven't watched these yet. Make sure you understand relationships between tables. Alright, and go watch this video on making a relational combo box. It's where you use a combo box to pick a value from a different table or query. That's what we're going to be doing today. These are both free videos. They're on my website, they're on my YouTube channel, go watch those and come on back.
Alright, so the first thing we're going to do is set up tables for both sex and gender. Alright, remember sex refers to the biology, reproduction, physical differences between people, right, anatomy, chromosomes, whatever. Whereas gender is more about identity and expression. So it's all a matter of what you want to store in your database, whatever works for you. No one's telling you what you gotta do. It's your database, these are your Legos, you put them together however you want. However inclusive you want to be, that's totally up to you.
Now I'm going to create two tables to keep it simple, but you could put these together in a single table if you want to. In fact, I got a separate video called Helper Data where you could take all the little teeny tiny tables that you have for things like, you know, a list of prefixes, Mr., Mrs., Ms., suffixes, Junior, Senior, right, titles, even a list of states and cities, all that stuff. You can put it in one table if you want to call it a helper table. I don't want to blow anybody's brains though so I'm going to keep it simple for today. But if you want to learn more about this concept, go watch this video.
So let's make two real simple quick tables. Create table design and we will continue with this in tomorrow's video. So tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now because one of the member benefits is they can watch videos as soon as they're posted and I'm going to record it in just a few minutes. But that's gonna be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
TOPICS:
Storing sex and gender in Microsoft Access
Making sex and gender fields inclusive
Issues with free-form text fields
Creating a controlled list with an "other" option
Using list items edit form
Implementing a triple state checkbox
Switching a field to a number type for triple state
Setting up separate sex and gender tables
Understanding sex (biology) vs. gender (identity)
Creating relational combo boxes
Prerequisites for the tutorial
COMMERCIAL:
In today's video from Access Learning Zone, we're diving into how to store sex and gender inclusively in your Microsoft Access database. I'll walk you through why using a text field can be problematic and how to create a structured list with an 'Other' option for flexibility. You'll see how to set up triple-state checkboxes and convert fields to handle more options easily. By the end, you'll know how to establish related tables for sex and gender, ensuring your database is both inclusive and accurate. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.Quiz Q1. Why does the tutor prefer not to use a free-form text field for storing gender responses?
A. It reduces processing time.
B. It prevents spelling errors and inconsistency.
C. It enhances database security.
D. It eliminates the need for additional data entry fields.
Q2. What is one suggested method for capturing additional gender options that are not on the predefined list?
A. Including a multiple-choice dropdown only.
B. Adding an open text box for further input.
C. Using a radio button for each new entry.
D. Enabling users to edit the main list directly.
Q3. What was the issue with using a yes-no field to store gender information, according to the tutorial?
A. It was too complex to implement.
B. It did not accommodate a "do not know" option.
C. It required advanced programming skills.
D. It made the database unstable.
Q4. How is storing yes and no values represented in a number field in Access?
A. As 1 and 0.
B. As true and false.
C. As negative one and zero.
D. As one and null.
Q5. What additional functionality does a triple-state checkbox provide?
A. It can change colors.
B. It supports a third state (null) besides yes and no.
C. It logs date and time of the entry.
D. It requires user authentication.
Q6. What does the tutor recommend after a period for handling entries in the "other" category?
A. Automatically delete them.
B. Ignore them to prevent data clutter.
C. Review and possibly add them to the main list.
D. Export them to a separate database.
Q7. What is the difference between sex and gender as defined in the tutorial?
A. Sex is physical, gender is identity and expression.
B. Sex is psychological, gender is biological.
C. Sex and gender mean the same thing.
D. Gender is static, while sex can change.
Q8. What level of difficulty does the tutor describe this tutorial as having?
A. Beginner
B. Intermediate
C. Expert
D. Developer
Q9. Which prerequisite knowledge is necessary to understand the tutorial fully?
A. Basic understanding of programming languages.
B. Understanding relational tables and combo boxes.
C. Advanced Excel skills.
D. Knowledge of database encryption.
Answers:
1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-A; 8-C; 9-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.Summary Today's TechHelp tutorial from Access Learning Zone is all about storing sex and gender information in your Microsoft Access database in an inclusive and practical way. This comes up often, especially as workplaces and organizations look for ways to be more respectful and accurate in how they record data about employees and clients.
If your needs are simple and you only want to store male and female as options, that is easy enough to set up. However, as we become more aware of the range of sex and gender identities, you may want to allow for additional choices such as non-binary or other identifiers. I am going to explain how to design your database so it can handle these options without getting messy or unreliable.
Let me start with the way many people approach this, which is to just make sex or gender a plain text field. While this seems flexible, it creates a lot of headaches. Free-form text fields allow users to type anything. In practice, this leads to inconsistencies. I have seen this myself when I had to clean up country data in my own systems. People would misspell their own country, which made accurate reporting difficult. The same problem happens with other free-text fields like city or state.
To avoid these problems, it is better to provide users with a controlled list of choices. This way, you decide what goes into your database and keep your data clean and structured. At the same time, you want your list to be flexible and respectful to all, so I recommend having a list of known, standard options, and then including an "Other" choice. If someone selects "Other," you can allow them to specify their own entry in a separate field. From time to time, you can review these other entries, and if you consistently see the same entry being reported, you can add it to your main list to keep things current and inclusive.
Now, a related topic is managing who gets to edit your main list of options. If you let everyone edit the main list, you end up with the same problem as freely-typed text. This is why you should keep your master list under tighter control and only allow "Other" inputs to flow into a separate log or field for review.
When I first started working with Access, the approach to gender fields was much more limited, usually just a single field set as yes/no or true/false. For example, in the past, I would use a yes/no field for gender or sex, which essentially means one option (say, yes for male and no for female). But this model has limitations, even beyond inclusivity, because it does not allow for situations where the information is unknown, or where a person identifies outside those two categories.
To allow for an "unknown" option, Access does offer a triple-state checkbox, which means a field can hold yes (male), no (female), or null (unspecified or unknown). However, to do this correctly in Access, you must store the field as a Number type instead of a Yes/No type. That allows the field to support three states: yes, no, and null. If you use the standard Yes/No type, you get only two possibilities. So, although this helps provide an additional choice, it is still not enough if you want to track more gender or sex identities.
That is why I suggest going a step further and using related tables—one for sex and one for gender if you like. Sex is typically thought of as biological attributes, while gender is about identity and expression. You can customize how many options to include in your table, so your database structure fits the needs and goals of your organization.
This approach means building small lookup tables for sex and gender. When entering a person's data, you use a combo box in your form, pulling choices from these tables. Include an "Other" option, and use an additional text box for users to specify a value not on the list. Over time, you can review what people are entering as "Other" to decide if it should be added to your main options.
Before moving forward, make sure you have some experience in building relationships between tables and using combo boxes to select related records. If you are not comfortable with these steps, I recommend watching my introductory videos on table relationships and combo boxes. These are free on my site and YouTube channel, and they cover the foundational knowledge you'll need.
For organization, I prefer having separate tables for sex and gender. Still, you could combine these into a single helper table if you have a lot of small lookup lists. This is just a matter of how you want to build and manage your own database structure.
In summary, avoid free-form text fields for sex and gender where possible, and use controlled lookup tables with an "Other" option to keep your data both accurate and inclusive. Remember, your database is your toolkit, and you can structure it as inclusively and flexibly as you need.
If you want to follow along with every step and see this process implemented live, 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 Storing sex and gender inclusively in Access
Problems with free-form text fields
Creating a controlled list with an "other" field
Restricting user ability to edit the main options list
Triple state checkbox for yes no unknown values
Converting a yes no field to a number for triple state
Creating separate sex and gender tables
Explaining difference between sex and gender
Creating relational combo boxes for sex and gender
Setting up data entry with combo box and textbox for other |
