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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
=(nz(Admin)+nz(Finance)+nz
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.
;-)
JeffCoachman
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:
=(nz(Admin)+nz(Finance)+nz
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.
;-)
JeffCoachman
ASKER
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?
TIA
frmSupportEmployees.docx
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?
TIA
frmSupportEmployees.docx
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?
JeffCoachman
ASKER
Yes. That's what I want to do
It looks like that is what your form is doing.
ASKER
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.
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.
ASKER
Sql file attached
Skills-Subform-Source-Query.docx
Skills-Subform-Source-Query.docx
My suggestion was different and didn't involve duplicating anything. But if you're happy with the results we can move on.
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).