Manipulating Table Fields in a Form? (1 Viewer)

[RipThorn]

Hello. Currently i'm working on a database that has employeetable. It has the usual like first name , last name and it also has about 38 fields that are skills for that employee like firstaid, assemblyskills and so on. I know that we will be adding new skills in the future. Since those aren't records but fields in the table and i don't want to modify it using desing table is it possible to create a form that will display all the fields so that I could add new field when necessary. Also I have a employee form that displays all the fields. How could i make the new field that i just created dynamically display on my employee form without going to desing and manually added to the form.

Thank You very much

Rip

 

[KevinM]

You need to split the db into more than one table (at least 3).
You need a 'Skills table' as a LookUp table first. This will have something like fields called [SkillID] and [SkillDescription] and the the skills will be RECORDS and NOT fields (as you have it now).
You then create a third table ('EmployeeSkills') that stores [EmployeeID] from the Employee table and [SkillID] from the above Skills table.
This is called a 'one to many relationship' and you set this up un the Relationship window.
That way you simply add new skills (as RECORDS) to the skills table and then add them to the EmployeeSkills table via a simple form and subform.
You never need to add more fields again.
This the correct ('Relational') way of storing data.
I'm sorry to be blunt, but the way you have it is WRONG and will only cause you more and more problems as the db develops.

HTH

 

[RipThorn]

Thanks for your reply. I did that before. When I had 2 separate tables i had to create 47 records for each employee. We have about 550 employees. Each Skill also has three values like 1 no experience 2 competent and 3 certified. Those tables were one-to-one relationship. It was simpler just to include those skills right into EmployeeTable. That way when i lookup an employee i can easily modify those skill values. It may not be the best solution but it works. I just have to go to desing view to add more skills when needed. I was hoping to use form instead of desing view. that's all.

Rip

 

[KevinM]

Trust me, USE RELATED TABLES (like you did in the first place) and NOT just the ONE table with multiple fields in.

It may seem like the 'easy option', but believe me you will run into MANY problems later on.

For instance how would you search for a particular skill?

Query on a particular skill?

etc etc.

Also if you are going to constantly add new [Skill fields] (Which incidentally you will have to do at table desgign and NOT form design). then you will also have to go through all your queries, forms, reports etc and re-design all those too !!!
Where as if your table was 'NORMALISED', (lnked tables), you simply ADD the skills as data to the relevant tables and you DON'T need to make any design changes whatsoever.

Surely this is the best and most efficient way!

YOU HAVE BEEN WARNED

 

[kgcrowther]

Rip, if you don't mind a little advice from a junior member I have a suggestion that would ease and quicken your data entry.

I would set up the database as Kevin suggested with the three tables. This allows for even more growth and more powerful updating and querying.

I think the secret then to easy data entry of all your employees creating 3 forms and linking your forms with buttons.

Form 1 Have an initial form with main employee information. Then at the bottom set up a button that says "add skills" this can save the information and open up a new form.

Form2 This form has employee name, skill, and skill level. These can be combo boxes: employee name displays a list from main emplyee info,
the skills displays a list of skills from the skills table,
and the skill level displays 1,2, or 3. You can put a button beside skills that opens up a new form in add mode for entering new skills.

Form3 This form is very simple with only one text box, to add skills, and a button that closes and saves.

The buttons can be most easily created with macros (since you want to perform multiple tasks). With the forms set up this way you can quickly enter all 500+ pieces of data in a short time and still retain the massaging capability of a fully relational database.

Just a suggestion.

Kenneth

 

[RipThorn]

Thank you guys for the info. I'm off to build new database based on your suggestion.
If I get stuck i'll post it again.

Thank you.
Rip

 

[RipThorn]

Me Again.
Ok it works so far, however
I have 47 skills so far. How can I make sure that i don't type the same skill twice?
in other words since the skills are really one-to-one i don't want to have let say AssemblySkill recorded twice. I would need some kind of check to see if I already have a assemblyskill for that employee I should not be able to add another record. If anything I should be just able to modify existing one to change the skill value from 1 to 2 for example.

Thanks Rip

 

[KevinM]

In your EmployeesSkill Table make the EmployeeId field and SkillID field BOTH Primary key fields (what's called a composite Primary key field).
That way it would be impossible to have duplicates of a combination of the two fields.

BTW, you made the right decision in re-designing your db, you won't reget it.

 

[RipThorn]

Hey All. Me Again.
EveryThing seems to working well thanks.
Now can someone tell me how I can use insert into that I would tie to a button
I have the following
mysql="Insert Into EmpJobHistoryTable (EmpID, OldJobId, NewJobId, DateOfChange) Values (" & Me!Text10 & "," & Me!Text12 & "," & Me!Text14 & "," & Me!Text16 & ")"
When I do MsgBox mysql I see all the right values but how do I acually run this thing to insert this record into my EmpJobHistoryTable. I don't want to use macros if possible

Thanks Rip

 

[Carol]

You don't want to insert this information if it is already contained within a table in your database. If you do, then you would have duplicate records. Bring the information together with a query, using a join on EmployeeID.