Today is part two of my Sex and Gender in Microsoft Access. How to store that stuff in your database. And this is part two so if you haven't watched part one yet, go watch that first. Duh! You'll find a link to it down below.

We now join our program already in progress. So let's make two real simple quick tables. Create table design. We'll do our sex table first: sex ID (that's our auto number), sex description (which will be a short text). Now I want to do a custom sort order so I'm going to put sort order in here and we'll make that a number. That way you can put them in whatever order you want.

I could go on with some really rude, not rude, but crass jokes. I'm not going to. All right. So here we go. We've got male, female, what else we got here? Intersex, and add whatever you want, and then other. OK? I'll put other down at 99. So if you add more in the middle there, you're good.

All right, save that. We'll do the same thing for gender real quick. Create, table design, gender ID, gender description. Remember, don't use the word name in here. Name is a reserved word. And then sort order. Save it. Gender T. And then, what do we got in here? We also have male, female, we got non-binary, we've got maybe gender fluid, and then other. Okay, all right, here we go. Save it, close it. We got our supporting data.

Now, we need a place to store that data once the user picks it. Right? So in my customer table this is where that data is going. All right. We've already got, let's get rid of that gender. We've already got gender in there. Goodbye. We're gonna do a sex ID. That'll be a number. That's the foreign key. Right? That's gonna point to the sex ID in the sex table. Same thing with gender ID. OK.

Now we also need places for the user to store the other option. So sex other will be a short text. Gender other will be short text. OK and they'll only have to type in those fields if they pick other. In fact, in the extended cut for the members, I'm going to show you how to make it so those don't even appear unless they pick other. We'll get to that in the extended cut. All right, save it, close it.

Now let's put the fields on our form. All right right click design view, and if you watch my relational combo box video, this is pretty straightforward stuff for you, right. Find the combo box, we're going to use the wizard, drop it down here. Look up the values from a table or query. Where are you getting the data from? We'll do sex first. All right bring over all three fields because we need that sort order. Next, now I don't wanna see the sort order when I open up the combo box. So just like the key column is hidden. The key column's hidden by making its width zero. We're gonna make the sort order column width zero. I like to make sure I go past that line just a little bit so it makes sure that it's zero.

Next, we're gonna store that value in the sex ID field in the customer table. We're picking from a list of sexes from the sex table, storing it in the customer table.

Next, what label would you like to give it? Sex. And then we're done. A little conditional formatting, I'll click on this guy, hit my little format painter, and make that guy black.

Now, could you copy this guy and modify it for gender? Yeah, you could. It's just as easy to run through the wizard again. Especially if you're a beginner. So do that again. Table the query, gender T this time, bring over all three fields. This stuff gets easier by the way the more you do it. So one of the reasons why I do repeat stuff in my videos sometimes is because I want you to do it three, four, five, ten times. You'll get the hang of it.

What are we sorting by? The sort order. Next, hide the sort order field by making it width 0. Next, store that value in the gender ID in the customer table. Next, give it a label of gender and finish.

Once again, we'll do a little format painting. I think I put the format painter up on my quick launch toolbar, the quick access toolbar up here. If you can't find it, it's on the home tab right there. OK, I put it up. I got a whole video on how to set this thing up. All right, so these are all set and now we just need to add our other boxes on here. Now, if you go to form design, add existing fields, you'll find them right down here. Sex other, gender other.

What I did there was I clicked on the first one, hold the shift key down, click on the second one, let the shift key go, and then click and drag and those both go up there. Now you can delete labels and slide these boxes right up next to there, get nice and cozy just like so. And the last thing we got to do is adjust our tab order because things are gonna be wacky down here so go to tab order. Now the one thing I don't like is that the wizard doesn't ask you for a name for these two things. It's combo32 and combo34. I always forget that stuff until I get to about here. So let's cancel out of this real quick. Let's give these guys good names.

This is going to be the sex combo. You could call it sex ID if you want to, but I like to know what I do. Oh, I changed. Be very careful. I'm going to leave this in the video because I do this all the time. Be very careful. I just changed the field exists. That was my goof. I messed up. So come back in here, change this control source back to sex ID, go to the all tab and reassign the name of it right there. Sex combo.

Remember the control source is where you get your data from in the table or query that the form is bound to. The name could be anything you want. You could call this guy James Bond and it would be the same thing. OK, do the same thing for the gender combo. All right, come down here, change that to gender combo. There we go.

Now if I change my tab order, everything looks a little better. Come down here. OK, so we're gonna go, we're gonna put the sex combo, then we're gonna put the sex other after that one. See how I did that? Click on it, and then let it go, and then click and drag, and you can put it wherever you want. I've got a whole separate video on tab order if you don't know how to set this thing up. All right.

Save it. Close it. Open it. And here we are. Drop it down. Male, gender, non-binary. There we go. Next guy. Other. Whatever. Type some whatever value you want in there. All right, gender, female. Got it? OK, pretty straightforward, pretty simple.

And now if you look in your table, come all the way over here, you'll see there's your values. You get an ID for sex and gender, and then the other fields are available to type in whatever you want. You can also do the same thing with other things like prefix, suffix, right, the senior, junior, all that stuff, do the same thing. If you don't want your end users typing in things directly into the main table, because like I said, you can do a list items edit form and let people modify this list, right, this list, but then you're going to get a whole bunch of junk in here. That's why I've always loved the other option. So your end users can type whatever they want and then once a month or every six months or whatever you just make yourself a query on the customer T and just take a look at all of the different others that are in there. If you see that there's a bunch of them that are added the same, you know you can then decide yourself if you want to add that into the main table and then you can just run an update query to delete all these and change that to a six or whatever it has to be.

Now in the extended cut for the members, we're gonna hide that other box unless the user picks other. Then they'll see that it pops up. Otherwise, you'll just pick male and it will be blank. It's not confusing that way to your end users. And also, if they do pick other, we're going to force them to have to put something in. You can't just pick other and leave it blank. You got to put something in there. OK? That's going to be covered in the extended cut for the members. Silver members and up get access to all my extended cut videos. All of them, not just this one, all of them. And of course, gold members can download these databases and you get the code vault which's got all kinds of cool stuff in it. And members if you think you already know this stuff, I'm going to include a couple of the little bonus tips in there too. So it's not just the stuff we've done before. So hang on, we're gonna get some cool stuff.

All right, but for everybody else that's gonna be your TechHelp video for today. I hope you learned something. Live long and prosper my friends. I'll see you next time.

TOPICS:
Creating Sex and Gender tables
Setting up auto number IDs
Adding custom sort order field
Populating Sex table with values
Populating Gender table with values
Modifying customer table with foreign keys
Adding short text fields for "other" options
Using design view to modify form
Creating combo boxes with wizard
Hiding key and sort order columns in combo box
Storing combo box values in customer table fields
Adding labels to form fields
Using format painter for consistency
Adding "other" fields to form
Grouping and moving form fields
Modifying tab order in form
Naming combo box controls for clarity
Adjusting control source for combo boxes
Testing form functionality with sex and gender data
Discussing use of "other" option for data entry
Plan for extended cut tutorial on conditional fields

COMMERCIAL:
In today's video from Access Learning Zone, we dive into part two of "Sex and Gender in Microsoft Access." If you missed the first part, go check it out first!

We start by creating simple tables for sex and gender, complete with custom sort orders. Then, we set up our customer table to store these values, including options for 'other' entries.

Next, I'll show you how to add these fields to your forms using combo boxes and ensure everything gets stored correctly.

You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.