Data input design problem 1

[JamesMack]

My personnel and training database consists of the following tables:

tblPersDetails
EmployeeNo
Name
Initials
DOB
Address
etc

tblPerSTrainingObjectives
EmployeeNo
TrainingObjectiveNo
DateAchieved
PerformanceLevel

tblTrainingObjective
TrainingObjectiveNo
Description

tblPersonalAnnualQualification
EmployeeNo
AnnualQualificationNo
Pass/fail

tblAnnualQualification
AnnualQualificationNo
Description

Training objectives are achieved at any time and in no real set order throughout a persons career.

Annual qualifications as they suggest have to be achieved annually by all employees.

I need to design a form with attached code to allow me to populate tblPersTrainingObjective with: EmployeeNo, TrainingObjectiveNo, dateAchieved and performanceLevel.
There are over 85 employees and when a training objective is achieved it can be by as few as 2 persons to as many as 85.
Currently the tblPersTrainingObjectives is unpopulated as to manaully input existing records would be a nightmare!

 

[ghubbell]

Hello James,
I'll start you off with a most wonderful trick. Go to your table tblPerSTrainingObjectives. Normally when you look at this table in datasheet view you'd probably see a list of numbers for your employeeID? If you see the names then you're a step ahead of this, but if do see numbers, go to design view, click on the EmployeeNo field. Go to the lookup tab below. Choose "Display control" and set it to combobox, "Row Source type": Table/Query. "Row Source": tblPersDetails. Now click on the "..." button beside and Yes, you want to build a query based off of this table. Add in to the queries grid EmployeeNo, Name. Sort as you choose but by Name ascending is usually good. Hit the save (floppy disc) button and save as "qryComboEmpNo" (or what ever you'd like.) I'll take a moment here and explain that first, in a seperate query you have locked in a sort order. If you move around the position of the fields in your table, the query doesn't care and either will your combo box. Second, you now own a recyclable query that I can see will be very useful in your Db.
Close the query and continue back on the lookup tab. You have to set the bound column to 1, column count 2 (or more if you chose more than No. and Name), column widths like this: 0;1.5 for inches or 0;3 for Cm. (Hides your No. Column). Set the list width to 1.6" or 3.1 Cm, Limit to list Yes. You're done. Switch to datasheet view (saving) and check out your EmployeeNo field. Even though you're storing the number, you're seeing the Name.

Don't quit yet because there is a method to my madness!
You can do exactly the same idea for TrainingObjectiveNo based off of tblTrainingObjective. Difference here is maybe you want to always see the number, and only the description when you open the combo. If so set your column widths to something like 1;3 for inches or 2.5;7 Cm. Make sure your list width is the sum of the columns (plus a little at least).

Where are you going with this Gord?
Here's the answer:

By default any form you now create based off of your table(s) will automatically inherit these properties. You will never have to make a combobox on a form again.

You could use a table for your performance levels. How about tblPerformanceLevel with a PL ID and some sort of description. If you only have 2 levels then "can" that idea and base it off a value list in a ComboBox that you define in your tblPerSTrainingObjectives.

Finally...to the form!

Use a form wizard. Base your form off of tblPersTrainingObjective and choose all the fields. Tabular style.
Choose Modify design and set the Tab Stop property to No for TrainingObjectiveNo.

Use the wizard again and add a combo to the Form Header section of this new form. Base it off of your tblTrainingObjective or your saved "qrycomboTrainingObjectiveNo" if you did make it.
Bound column is the No. but the combo is not bound to any field on the form. You just want to remember its value for later use. It will ask you what caption you want. What ever.. but once it is created you have to Name it CboSetTONo. In its properties dialog find Events tab, "After update", select "Event Procedure", click "..." to open the Visual Basic module "behind" the form and automatically label up a sub for you. Add this in between the title and the "end sub" :

Me.TrainingObjectiveNo.DefaultValue = Me.CboSetTONo

(This will automatically change the default value in the TrainingObjectiveNo field when you change the value in the combo.)

Because it seems your priority is the TrainingObjectiveNo, you don't want to have to retype it everytime you add a person to your table. You could use the same setup if the dates and or performance levels were all the same too.

In summary, you build some combos that store a default value for your real field value. Well you will have to pick which employee gets on to the list be it 2 or 85 but you won't have to keep entering TrainingObjectiveNo (and maybe date and performance level) over and over.

Although it looks laborious, all this can probably be built in a half hour. Somehow, somewhere, someone has to key in some data ("some" word on sale today). Automatically created combos help, after all it's best to make data entry as easy as possible. Hope this does what you need or at least gives you some ideas and tips. Gord
ghubbell@total.net

 

[elizabeth]

James, although you asked about a form I have a suggestion that you first modify your db structure. If you are a novice, let me explain that changing structure after you begin entering production data and building forms is quite a bit more hassle.

As I read it, it seems that Qualification is just an instance of a Training Objective, if you don't get too hung up on semantics. If you enter Qualifications into Training Objectives, you can take eliminate tblPersonalAnnualQualification because you can enter the appropriate data into tblPerSTrainingObjectives instead. And this is an improvement because you reduce complexity AND get a date on Annual Qualification.

You may want to add a couple of add'l fields too - due dates for example, or dates that a qualification is good through. In order to design the database, you might try to think through the operation itself. For instance, what information will the data entry person have? What kind of errors, if any do you want the data entry person to check for (example: duplicate entries), what reports will you want to run, how frequently, and what will you want to select on (dates?) in order to run those reports. Who may you distribute reports to? If to supervisors, you may want to have a dept in the employee details, etc. Of course that would require a Dept table. Also it may be helpful to have phone & email if you will be using those methods to contact the employee with questions or noftifications.

 

[ghubbell]

Fine idea's Elizabeth...where were you when the battle was going on at Thread181-62294? Gord
ghubbell@total.net

 

[JamesMack]

Many thanks again Gord and Elizabeth,
In response to your ideas Elizabeth, I had decided to denormalize the table structure to separate annual quals but you have got me thinking again!
The full structure does however contain fields for 'due date'and others to help plan requalification courses etc.
Gord your help has been invaluable, I am very close now all I need to do is to modify what you gave me to allow me to set the date achieved and the performance level (1,2 or3) so that all I need to do is select the personnel having achieved the TO on the form.
Again many thanks to both of you
PS Yes Elizabeth, I am a beginner having recently qualifified as a systems analyst/designer! how did you guess?