Matching title and gender

[1961templar]

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.

As far as I can see the Form Validation only restricts the entries to "M" or "F" but doesn't ensure consistency with "Mr", "Mrs" etc.

 

[Kryst51]

As far as I can see the Form Validation only restricts the entries to "M" or "F" but doesn't ensure consistency with "Mr", "Mrs" etc.

You can do more validation with vba or the control's query to ensure the consistency as dbDamo suggested. In addition to restricting the available entries.

 

[1961templar]

Restrict the entries how exactly?

 

[dbDamo]

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

Form validation, I will not keep saying it!!!

I do not like table level validation, not least for the fact that you can only provide one message. Not a good idea.

You state that you are limited to the number of tables you are allowed to use, I cannot see why but must take your word so Kryst51's suggestion, while being the best option, is a no go.

Mr. B's suggestion can work with some tweaking, but as Kryst51 says you will really need to read up on the method and isn't very straightforward.

Form validation, while requiring the use of VB, is actually very simple.

 

[Kryst51]

Restrict the entries how exactly?

Search Google and the site, for info. I still think this could be ahomework assignment and am unwilling to do the work for you. Mr. B gave you an excellent start.

Try searching for things like combo box values, limit to list. Which will gain you a start and might help lead you to more search terms for the gender bit.

Edit: maybe a search on form validation.

Edit2: Microsoft site had and ecellent page for learning validation rules and how to use them.

 

[1961templar]

Perhaps someone can be more helpful and provide me with an answer?

Thank you.

 

[boblarson]

Perhaps someone can be more helpful and provide me with an answer?

Perhaps you could answer our questions too???

 

[Kryst51]

Perhaps someone can be more helpful and provide me with an answer?

Thank you.

In addition to what Bob said, you need to understand something. Your FIRST STEPS have already been given by Mr. B and dbDamo, You are not willing to take them, nor to learn. It is doubtful if anyone will help you if you do not take the suggestions given to help you achieve things the way you want them to be done.

Also, back to Bob's comment of:

Perhaps you could answer our questions too???

Is this a homework assignment?

 

[dbDamo]

Perhaps someone can be more helpful and provide me with an answer?

Thank you.

I'm sorry, but are you being serious? I have never seen someone ask a question like this after being provided with a number of solutions that WILL work.

We have given you the answers already. It is now down to you to make a decision as to which solution is best for you. Once you have made that decision you can then ask more specific questions on how to achieve it, and if you haven't offended us too much by then you will get an answer.

 

[wiklendt]

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?

if you have a separate table for titles, you can add the gender to a third field in that table and thus they will all stick together.

i.e.;

tblTitles
-------------------
titleID (autonumber, PK)
title (text)
gender (text)

values for title and gender would stay in the same record

title     gender
---------------
mrs      F
miss     F
mr       M

but there are instances where you might come across problems.... like for a person who uses the title "doctor"... you've no idea what the gender ought to be there.

thus the gender MUST be added separately to title, to facilitate these weird titles.

so.... that leads me to recommending what everyone else has... a separate table for titles, and a separate table for gender. these would appear in the employee table as a 'foregin' key (but effectively you still get what you wanted - all the info in one table).

i.e.,

tblEmployees
--------------------
employeeID (autonumber PK)
titleID (number, FK)
genderID (number, FK)
.
.
.

then in your relationship view you would create links between your two "titleID"s and two "genderID"s (and make sure you check 'referential integrity' so that a person cannot be attached to a non-existant title or gender!)

then in you form, where you do the data entry, make the title a combobox (or dropdown box) whose source is the tblTitles but which saves the data into the titleID field of the tblEmployees table.

same with the gender field.

so, on the form, you don't even KNOW that they're three different tables. to you, to your boss, to your peers, it all looks like you've magically done it one table (in fact, the keys are all stored in the one table anyway!)

does that help explain it better for you?

in the examples given above, there is no "coding" - you don't have to maintain anything and it's all done for you by access because of the relationships you've stipulated in the relationship window. it also makes new titles and new genders (such as "U" for "undisclosed") easier to add - and there would be no need to have to go back to the code and re-program for a new title/gender...

 

[Mr. B]

Sorry for not completely understanding what you really wanted to do.

Create a combo box for the Title. Set the "Limit To List" property to true. Set the the "Row Type Source" property to: "Value List" and then put the values you need to use in the "Row Source" property like: Mr;Mrs;Miss;Ms no quotes.

Create a Text box for the "Gender" field and set the "Locked" property to true.

In the "AfterUpdate" event of the Title combo box put the follow code:

If Not IsNull(Me.cboTitle) Then
    If Me.cboTitle = "Mr" Then
        Me.txtGender = "M"
    Else
        Me.txtGender = "F"
    End If
End If

Change "cboTitle" to the actual name of your Title combo box and change "txtGender" to the actual name of your Gender text box.

This will set the Gender value based on the selection made from the Title combo box.

 

[wiklendt]

Sorry for not completely understanding what you really wanted to do.

Create a combo box for the Title. Set the "Limit To List" property to true. Set the the "Row Type Source" property to: "Value List" and then put the values you need to use in the "Row Source" property like: Mr;Mrs;Miss;Ms no quotes.

Create a Text box for the "Gender" field and set the "Locked" property to true.

In the "AfterUpdate" event of the Title combo box put the follow code:

If Not IsNull(Me.cboTitle) Then
    If Me.cboTitle = "Mr" Then
        Me.txtGender = "M"
    Else
        Me.txtGender = "F"
    End If
End If

Change "cboTitle" to the actual name of your Title combo box and change "txtGender" to the actual name of your Gender text box.

This will set the Gender value based on the selection made from the Title combo box.

yup, that will do it too, but i would only do it this way if i was REALLY not able to use extra tables.

 

[wiklendt]

if this really is an assignment, then i am baffled as to why they are forcing you to use BAD database design techniques.

 

[boblarson]

if this really is an assignment, then i am baffled as to why they are forcing you to use BAD database design techniques.

THAT doesn't surprise me. We see that all of the time here. The "instructors" are commonly not schooled in database design and are teaching things which are not good practice. It has been one of those on-going items that we cringe at.

 

[dbDamo]

I said the same earlier, which is why I am not sure that is the case. It just doesn't make sense placing a limit on the number of tables that can be used as it could utlimately lead to bad design.

 

[Mr. B]

Having been one of those instructors, as well as a developer, I would just mention that there are times that you are attempting to teach one specific thing and not yet attempting to totally approach the "best practices" issue. In this case, if this is an assignment, then it could be that the goal is to have the student learn how to use the various controls even when the data is not already available in a table.

I know that I am reaching way out on this one, but it is not totally bad, just not what most of use who do this sort of thing evey day would do.

I am currently working on a demo file that I thing I will have ready shortly that takes the concept of managing small lists like Prefix, Suffix, Title, etc. from a single table which has been demonstrated by Allen Brown, to one more level and allows for custom sorting of each list. I'll be posting the demo to the forum as soon as I have it ready. I will also have several other commonly discussed issues demonstrated in the same file. I will have documentation with it so as to try to help others who need to know how to manage info like this.

 

[1961templar]

This is indeed an assignment set for my students by someone else, hence the restriction on number of tables. However I need a solution myself in order to guide my students towards a solution.

 

[dbDamo]

What is the assignment supposed to achieve? Obviously it is not to teach the students good Database design. Is it just to learn about validation?

It would help if you could post the assignment, then we can see what the aim is and base our solutions on this. It would also help to know the level of the students.

3 solutions have been provided. These are the ways that professional programmers validate fields, so it would be good practise to pass this on to your students. You can't use Kryst51 solution, so you have to use my form validation suggestion, or Mr. B's combo box suggestion.

 

[1961templar]

What is the assignment supposed to achieve? Obviously it is not to teach the students good Database design. Is it just to learn about validation?

It would help if you could post the assignment, then we can see what the aim is and base our solutions on this. It would also help to know the level of the students.

3 solutions have been provided. These are the ways that professional programmers validate fields, so it would be good practise to pass this on to your students. You can't use Kryst51 solution, so you have to use my form validation suggestion, or Mr. B's combo box suggestion.

Thank you for your suggestions. Unfortunately as this is a 'live' assignment I am unable to post full details. I think that there is a problem with the assignment as the simplest suggestion of an additional table doesn't fit within the criteria; the other two solutions are beyond the level of the students.

 

[wiklendt]

This is indeed an assignment set for my students by someone else, hence the restriction on number of tables. However I need a solution myself in order to guide my students towards a solution.

why is someone else giving YOUR students an assignment? can you not negotiate with this 'someone else' about freedom of design? or at least NORMALISATION of the database?