Access Data Setup

If I have a spreadsheet of employees with 5 columns assigning percentages to different functional skills per employee, so for each record, the data input should look to the user as

Administrative [user adds percentage]
Financial [user adds percentage]
Legal [user adds percentage]
Research [user adds percentage]

The categories are fixed and i dont want the user to have to select them from a drop-down each time...would I have separate fields for each skill per employee in the employee table or have a separate table?

If its a separate table, then the percentages would be in one dimension ion the table as FunctionalType for example with values in a value field, and would force me into a comboBox where users have to pick the categories which i dont want.
RonBudget AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This is a repeating group and storing the fields in a single table violates first normal form as you seem to recognize.  I know it is tempting.  There are only five of them and I'm sure you've been told that there will never be any more but once you make that commitment, you're in trouble if you have to add a sixth category.  The reality is that storing them in a separate table ultimately makes using them easier and allows for easy expansion should it come to that.  To solve the problem of not wanting users to pick from combos, you can "fix" the rows so you always have five rows.  To do this, you create a separate table that defines the universe of categories.  At this time, the table will contain only 5 rows.  To add a sixth category, this is the only place you would have to change.  You would simply add a sixth record to the table.  If the users swear it won't happen, don't bother making a form to do it since it is very easy to do behind the scenes in a hurry if you need to.

Then to use the category table, each time you have to do a new evaluation, you run an append query that copies the "five" rows from the definition table and appends them to the employee evaluation table.  Use a subform sized to show five entries.  You can disguise it so it doesn't even look like a subform although I would still include a scroll bar.  The subform should be set to prevent additions and deletions since you always control the records with the append query.  All the user does is enter the evaluations.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
No points please.

Concur with Pat.  It can be extremely tempting to go horizontal in this situation, but I strongly recommend that you not do it.  Go with the separate table containing Functional Skills with fields (FSID, FS_Desc).  You might even add Eff_Date and Thru_Date to that table so that if they change the name of a functional skill or add/remove one you will always be able to recreate the list as it existed during a particular period of time.

And a third table EmpSkillPctg with fields like (ESPID, EmpID, FSID, Pctg).  If you have to do this more than once, you would need another field to either identify the year or some other period, or to link these values to a particular evaluation).
Jeffrey CoachmanMIS LiasonCommented:
No Points here either...

For me, the biggest drawback to doing things like this horizontally, is that it creates a problems if you ever need to "aggregate" this data.
(May not be relevant for percents, but...)
For example to get the "average" percent, you would have to do something like this:
The problem here is that if you ever add, delete or edit a category, you will need to edit this formula(s) manually every time.
(Here it is also not clear how you would get the average for each year, month, ..etc, or see a history of the percentage changes...)
I know each db is different, ...but every time I have done this in a design, ...I have lived to regret it.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RonBudget AnalystAuthor Commented:
Thanks very much for your comments. The appending a data group concept is great; I appreciate the guidance.

Attached is a screenshot of the main form showing the Administrative Employee subset of tEmployee. The form exists so Managers can assign (in the subform) the percentage of Admin FTE allocated to various professional staff.

I'm attempting to translate the Excel data entry process from the 2 lines of the spreadsheet (image below the form in attached word doc) to the Empl Skills subform. I'm expecting the answer is the sbfEmplSkill (shown) will display the Empl Skill percentages without spaces, but the managers will enter the values on a similar or duplicate popup sbfEmplSkillPct form so that only the cells with entries in the skills group can be appended to tEmplSkillPct table.

I just haven't figured out yet how to the subform and the popup data entry subform need to be structured to make it work. Am i on the right track?

Jeffrey CoachmanMIS LiasonCommented:
I'm expecting the answer is the sbfEmplSkill (shown) will display the Empl Skill percentages without spaces, but the managers will enter the values on a similar or duplicate popup sbfEmplSkillPct form so that only the cells with entries in the skills group can be appended to tEmplSkillPct table.
I'm confused...
It looks like your form already does what you ask...

So lets be clear...
Is your ultimate goal here to have the ability to select a Person, and see all (4 in this case) percentage categories.
Then assign the percentages of each category for that Person?

RonBudget AnalystAuthor Commented:
Yes. That's what I want to do
It looks like that is what your form is doing.
RonBudget AnalystAuthor Commented:
Not yet.

The subform source query outer joins the Skills and Employee tables to the Percentage Table on Employee Skill ID (ESID), and EmplID, so the results includes all skill categories even of there's no Pctg/EmplID's entered. But, when the subform is bound to the master form on EmplID, the records with no Pct/EmplID are filtered out, so I don't any categories until percentages are entered.

I solved this problem by adding a 2nd EmplID field, [EmplIDApp] to the query which grabs the EmplID from the open form (see attached Sql). This fixed the display of records on the subform.

For data entry, I created a duplicate modal subform with a control button to kick off an append query after the percentages are entered. I now have to filter the source query to only retrieve the last update so I'm not looking at multiple percentages per category

You may have had a different concept in mind but at my skill level this was what I came up with from your guidance.
RonBudget AnalystAuthor Commented:
My suggestion was different and didn't involve duplicating anything.  But if you're happy with the results we can move on.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.