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.