Matching title and gender

[1961templar]

tbl_Employee
pkEmployee_ID (autonumber)
strTitle (4 char string) "Mr", "Mrs", "Miss" or "Ms"
strGender (1 char string) "M" or "F"

I need to ensure consistency between Title and Gender.

What is the best way to do this? Validation rule?

Thank you.

 

[Kryst51]

Have a separate table for titles with its own pk, that is an fk in your employee table. Have a separate gender table for your two genders with its own pk and an fk in your titles table.

Set up the ralationships in the relationship screen and voila!

 

[1961templar]

I can see how that would work, but I don't want a separate table for Title.

I need to include both Title and Gender in the Employee table but ensure that they match up.

 

[dbDamo]

Why do you need to store them in the Employee table?

 

[Kryst51]

If you set it up right, it's exactly the same as storing them together, it just makes things easier. I don't see why you wouldn't want to do it that way.

That's why a relational database is so great.

 

[1961templar]

I have been specifically asked to create these two fields, Title and Gender in the Employee table and ensure consistency between them.

 

[dbDamo]

I am sure that whoever has asked you does not understand that storing them in different tables is no different to storing them in the same table as long as your relationships are set up correctly, apart from being able to perform the validation in a simple way if you store them separately.

Any form you create would be based on a query that links the three tables. You really should go with kryst51's suggestion...

 

[1961templar]

I can't see why I can't put all Employee details in one table?

I have 5 tables in all, but I need to enter all Employee details in one table. Surely its not so hard to ensure Title and Gender are entered consistently?

 

[Kryst51]

I don't know of any other way. I don't understand why you can't make separate tables. That's what a relational database is for. To make life easier. Your end result will still be the same, plus it makes data entry on your forms easier. I'll post a sample, and you can see what I mean.

Some one else can probably give you a different way, but it will be much more complicated I'm sure.

 

[dbDamo]

If you really do want to store these fields in the Employee table then you can use validation on the form used to enter the data, place validation in each controls BeforeUpdate event and also in the forms BeforeUpdate event.

Of course this assumes that you will be using a form to input data.

I have never been a fan of table level validation.

 

[Kryst51]

Also, you can use a queryto see it all as if it were in one table! but the validation would be built in.

Edit: Here's my sample

 

[Mr. B]

Getting consistancy for the Gender can be done without the extra table. Just use a combo box and hard code the "M" and "F" as the only choices available. Set the "Limit To List" property of the combo box to "True". To set this up, create your combo box, then change the "Row Type Source" property to: "Value List" and then put the values you need to use in the "Row Source" property: M;F

As long as you know that the only values that you will want chosen for the "Title" are: "Mr", "Mrs", "Miss" or "Ms" then you can do the same to force consistance for this field as well.

Just be aware that if, in the case of the "Title" field, you ever need to have any additional value to be available, you will have to go into design mode and manually add any needed values, like "Dr", etc.

Using the method that has been described by others is definately the most advantageous way to go, but you can do it the way just described.

 

[1961templar]

I need to ensure that "Mr" in Title always = "M" in Gender and "Mrs", "Miss" or "Ms" in Title always = "F" in Gender.

 

[dbDamo]

You already have in my opinion the best 2 solutions available. If you really don't want to split the data into multiple tables then form validation is the way to go, but it is definitely simpler to go with Kryst51's suggestion, especially if you are not familiar with VB.

 

[1961templar]

I need to ensure that "Mr" in Title always = "M" in Gender and "Mrs", "Miss" or "Ms" in Title always = "F" in Gender.

I am limited in the number of tables I can use therefore none of the solutions suggested so far can be used to ensure consistency.

 

[Kryst51]

Why are you limited to your number of tables?

 

[boblarson]

I am limited in the number of tables I can use...

Let me guess - School Project?

 

[dbDamo]

Using form validation means you do not have to use more tables - if you really are restricted to the number of tables you can use (I really do not see why anyone would risk good Database design by giving you this restriction) then this is the best way of validating your data.

 

[1961templar]

I am looking for a solution to this problem, does anyone have the answer?

 

[Kryst51]

Let me guess - School Project?

Yeah, that thought occurred to me, which is why I asked about the limitation of tables.

Mr. B's suggestion can work, without adding more tables. But you will need to do some work and research on that method first. And come back with some specific questions about it.

Especially if this is a homework assignment, no one wants to spoon feed you, as you are supposed to be learning from the assignment.

Edit: Mr. B's suggestions with the validation suggested by dbDamo