Sex & Gender 2
By Richard Rost
2 years ago
Store Multiple Sex & Gender Options in Access Part 2
In this Microsoft Access tutorial, you will learn how to store multiple options for sex and gender in your database by creating and linking tables, setting up combo boxes, and adding custom fields for user input. This is part 2.
Members
In the extended cut, I will show you how to make it so the Other boxes only appear if the user selects Other. We'll also ensure the user is required to enter a value if Other is picked.
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!
Prerequisites
Recommended Courses
Keywords
TechHelp Access, storing sex options in access, storing gender options in access, multiple gender entries, multiple sex entries, sex table design, gender table design, relational combo box, creating combo box in access, access foreign key tutorial, hide fields based on selection, access form design tips, sex and gender data entry
Subscribe to Sex & Gender 2
Get notifications when this page is updated
Intro In this video, we continue our look at storing sex and gender information in Microsoft Access. You'll learn how to build simple tables for sex and gender, set up auto number IDs, add a custom sort order, and populate your tables with relevant values. I will show you how to link these to your customer table with foreign keys, add short text fields for 'other' options, and use the form design tools to create and configure combo boxes, hide columns, label controls, update the tab order, and name your controls for clarity. This is part 2.Transcript 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.Quiz Q1. What type of field is used for the "sex ID" in the sex table?
A. Short text
B. Number
C. Auto number
D. Long text
Q2. Why is a custom sort order used in the sex and gender tables?
A. To assign a unique identifier to each entry
B. To allow entries to be ordered in any desired sequence
C. To make the database queries faster
D. To add additional descriptive text to each entry
Q3. What is the "sex other" field type in the customer table?
A. Long text
B. Number
C. Auto number
D. Short text
Q4. Where is the sex ID stored after a selection is made from the combo box?
A. In the sex table
B. In the gender table
C. In the customer table
D. In the form data properties
Q5. How is the sort order column hidden in the combo box?
A. By setting the column width to 1
B. By deleting the column from the combo box
C. By setting the column width to 0
D. By setting the column visibility to false
Q6. What tool is used to place fields on the form in Microsoft Access?
A. Form wizard
B. Combo box wizard
C. Relational combo box wizard
D. Design view with add existing fields
Q7. Why might you want to rename combo box controls such as "combo32" and "combo34"?
A. To improve the sorting speed of the entries
B. To reduce the file size of the database
C. To make it easier to identify their functionality
D. To allow for multi-user functionality
Q8. When is it necessary to add "Sex other" and "Gender other" fields in the form?
A. Only if the user selects "Other" from the combo box
B. If the user selects "Male" from the combo box
C. If there are no matching entries in the sex or gender tables
D. If the user leaves the combo box selection empty
Q9. What should you do to ensure accurate tab order in your form?
A. Automatically generate tab order during form creation
B. Manually adjust the tab order using the tab order feature
C. Only add tab order to mandatory fields
D. Remove all tab orders to prevent field skipping
Q10. What is the advantage of allowing users to type in "Sex other" and "Gender other" fields?
A. To automatically populate pre-defined categories
B. To let users provide data that isn't pre-defined
C. To store multiple values in a single field
D. To reduce database size by avoiding data duplication
Q11. What will be demonstrated in the extended cut of the video?
A. How to create custom queries
B. How to hide "Other" fields unless the user selects "Other"
C. How to add additional gender options
D. How to link multiple tables together
Answers: 1-C; 2-B; 3-D; 4-C; 5-C; 6-D; 7-C; 8-A; 9-B; 10-B; 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.Summary Today's TechHelp tutorial from Access Learning Zone continues on with part two of managing sex and gender information in Microsoft Access databases. If you have not already gone through part one, I recommend you start there so this material makes sense.
In this session, I pick up right where we left off. The first task is to create two straightforward tables: one for sex and another for gender. I open up table design view and build the sex table with a sex ID set as an auto number, a sex description field for the label, and a sort order field as a number. The sort order field allows you to control how these entries will appear in any lists, putting them in whatever sequence you prefer.
I populate the sex table with entries such as Male, Female, Intersex, and anything else appropriate. I assign 'Other' a sort order value of 99, so it will always appear at the end. You can add more options in the middle without disturbing this order.
Next, I repeat the same process to create the gender table. This table has a gender ID, gender description, and sort order. Again, I avoid using reserved words like 'name' in these fields. I include entries like Male, Female, Non-binary, Gender Fluid, and Other. Once finished, I have all the supporting data loaded and saved.
Now, you need somewhere to store this information for each user. The customer table is the appropriate place. I remove any existing gender field to make way for improved fields. I add sex ID and gender ID as number fields that will act as foreign keys to the respective sex and gender tables.
For handling entries not included in the standard list, I add 'sex other' and 'gender other' as short text fields. These allow users to specify a value only if they pick 'Other' from the list. In the Extended Cut available to members, I will show you how to hide these 'other' text fields so they are only visible when someone selects 'Other', as well as how to require users to enter something in those fields if they choose the option.
After setting up the tables, I move on to updating the form. I use design view and the combo box wizard to add dropdown lists for sex and gender. The data for these lists comes directly from the sex and gender tables. All three fields, including sort order, are brought into the combo box for sorting purposes, but I do not want the sort order field displayed to the user. By setting its column width to zero, it remains hidden, the same way we often hide key columns.
The selected value from each combo box is stored back in the customer table in the sex ID or gender ID field. When labeling each dropdown, I use clear labels like 'Sex' and 'Gender' for clarity. I use the format painter to keep the form's appearance consistent.
Although you can copy and modify an existing combo box to create the second one, it is often easier for beginners to use the wizard again. I encourage repeat practice here since performing these steps multiple times will help you get comfortable with the process.
With the combos set up, I then add the 'other' entry boxes to the form. Using the form design tools, I bring in the sex other and gender other fields. Grouping and moving fields together is made easier by using the shift key, allowing both boxes to be dragged at once. I recommend deleting and repositioning labels to keep things tidy and organized.
The next important step is to update the tab order, since adding controls can disrupt the natural flow. Setting a sensible tab order improves user experience. One thing to note: when using the combo box wizard, Microsoft Access assigns generic names like Combo32 or Combo34 to the controls. I prefer giving these controls meaningful names, such as 'sex combo' and 'gender combo'. Changing the control source is necessary if you make any mistakes here, but remember that the control name is entirely up to you and can be changed in the property sheet.
Once named, I again check and adjust the tab order so that users move logically from sex combo to sex other, then to gender combo and gender other, as they work through the form. I have a separate video focused entirely on managing tab order if you want more detail.
With that complete, I save and close out design view, then test the form. The combo boxes now function as intended, allowing you to select an option or choose 'Other' and type in a custom value. These entries are properly stored in the customer table along with the appropriate IDs.
This system can be adapted to other data types with similar requirements, such as prefixes, suffixes, or any field where you want to offer users a standard list but also allow them to specify something unique. It is a good idea to leave list editing out of the hands of end users to avoid a long, messy list. Instead, you can periodically run a query to review the 'other' values entered by users. If you spot frequently repeated entries, you might decide to add them to the main table yourself and update existing records efficiently with an update query.
As I mentioned earlier, in the Extended Cut members-only video, I will demonstrate how to hide the 'other' text boxes unless the user selects 'Other,' and how to require the entry of a value if 'Other' is chosen. This makes the form cleaner and less confusing for your users. Silver members and up get access to all of my extended cut videos, and gold members have even more benefits including downloadable databases and access to the code vault filled with useful examples.
For everyone else, that wraps up today's TechHelp session. If you would like to watch the complete video tutorial, where I go through all of these steps in detail, you can find it on my website at the link below.
Live long and prosper, my friends.Topic List 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
Adding combo box fields to form with wizard
Hiding key and sort order columns in combo box
Storing selected values from combo box in table
Adding custom labels to combo box fields
Using format painter for field consistency
Adding other textboxes to the form
Moving and grouping fields on the form
Renaming combo box controls for clarity
Adjusting control source for combo boxes
Changing the tab order for form navigation
Testing form data entry and storage
Using the other option for flexible data entry
Reviewing and managing other entries in table |
