Too many fields, suggestions?

[WolfwoodZero]

Hi, I'm afraid I am unable to be overly technical with my question as i've only really had a 2 day access course, sorry in advance!!!

Problem
I am trying to create a skills Matrix, it will need to include approximately 400 different skills which a staff member my or may not be trained on. I started by trying to do the lot on one table and an one input form but hit the 255 field limit. So now i'm messing around splitting it down into approx 7 sections. So, now i have 7 tables of skills and 7 input forms for the user to fill the table with, each table with a duplicated list of ID numbers for the staff. This seems really inefficient to me and i'm basically wondering if someone might give me a couple of tips structure wise. Also, could i just have one table for the staff ID's seperately? I tried to do this but cannot find a way of relating the skills input on the form to the specific ID if they are not on the same table. I know this is all probably really basic (and probably poorly explained and confusing too) but i've scoured the net as per usual and came up with nothing (i got some lovely code online which disabled the scroll button from moving records yey! ), so ive taken the brave step of joining a forum.
Thankyou for your time everyone.

 

[KeithG]

Create two tables with a one to one relationship.

 

[WolfwoodZero]

But does that not mean i will have to update both tables STAFF ID's everytime someone joins or leaves the company and make sure the are exactly the same? Thats like 1000 ID's to keep checkin. If so, theres no point even making a 1-2-1 relationship, i may as well have 2 stand alone tables? I'm confused lol.

 

[KeithG]

Is staffID your Primary/Foreign Key? I assume so, so when you create the relationship enforce Referecntial Integrity and check the Cascade Update Related fields checkbox. So every time the Primary Key is updated the Foreign Key will also be updated.

 

[BarryMK]

But does that not mean i will have to update both tables STAFF ID's everytime someone joins or leaves the company and make sure the are exactly the same? Thats like 1000 ID's to keep checkin. If so, theres no point even making a 1-2-1 relationship, i may as well have 2 stand alone tables? I'm confused lol.

Hello and welcome WolfwoodZero

I'm not one of the experts here, just a hacker with more databases than sense but...

It's a sad fact that if you don't get the underlying table structure right at the start you'll have more grief than you'd ever want. Time spent doing that will be more than repaid by time saved trying to repair a database that's like a car with square wheels.

There's lots of info if you search here on relationships, much of it confusing to a beginner, but if you play around with it and post up what you've got the experts will, I'm sure, help you along.

I speak from bitter experience, avoid my mistakes if you can. Cheers

 

[neileg]

Wrong design, I think. You're trying to create a spreadsheet. In general, tables should be long and narrow.

What you need is three tables, one with a record for every employee, one with 400 records (one for each skill) and a junction table that links employees and skills in a many to many relationship. Do a search in here on many to many and junction tables.

 

[Moniker]

Here's the normalized structure you want:

[b]TABLE: t_Employees[/b]

FIELDS: 
EmployeeID (Primary Key)
Employee_FName
Employee_LName 
.
.
<Other employee details you want here>
.
.

[b]TABLE: t_Skills[/b]

FIELDS:
SkillID (Primary Key)
SkillName
.
.
<Other skill details you want here>
.
.

[b]TABLE: t_EmpSkills[/b]

FIELDS:
EmployeeID (primary compound key)
SkillID (primary compound key)
.
.
<Other employee skill details you want here>
.
.

To make a compound key, highlight two (or more) rows in table design view and click the primary key icon in the toolbar. This means that both fields must be unique in order for it to be a valid entry. That keeps the same employee from getting the same skill attributed to him or her more than once. These are all valid compound key entries:

1 1
1 2
1 3
2 1
2 2
2 3

This would cause an error:

1 1
1 2
1 3
1 1
2 2
2 3

The 1 1 key is a duplicate.

With that structure, you can easily link the tables to each other. For example, If John Smith has an EmployeeID of 12345 and has the skills 100, 232, and 319, then you can query the table t_EmpSkills WHERE the EmployeeID=12345 and you'll get that result. To get the actual skill name, do an INNER JOIN between tbl_EmpSkills and t_Skills on the SkillID field to get to the SkillName value. The same can be done to get to the employee name using the EmployeeID.

~Moniker

 

[WolfwoodZero]

Thanks everyone, i'll try the above suggestions and attempt to improve my understanding a bit. Happy New Year

 

[WolfwoodZero]

Ok, i've set up the above tables. Looks good, very few fields etc.. However, i need this be completed by a user and had designed a form with all the skills listed as tick boxes which just saved a Yes/No for each skill. Setting my tables out this way means all the skills are records not fields, so i cant do this now. Any way around this with regards listing all the skills and having a tick store each yes and no? I need this as it is equally important to know who is NOT trained on things, and also that doing it this way means i can rig up a radio button thing to set a block of skills to Yes or No instead of always clicking all 400 skills individually.

 

[Moniker]

You never want to use checkboxes for a list that can expand and contract at any given moment (like your skill list). It will quickly become a nightmare to maintain.

If you want to be able to select "John Smith" and then have the skill list appear where 1 to X skills can be selected, use a listbox control with "Multi Select" set to "Extended". This allows you to select multiple items from the listbox in any order you want. Alphabetize the skills to make it easier to find the skills you want to select. Above the listbox, put a reminder to hold the control key down to select multiple skills so that the end users do it correctly.