Calculated Fields in Forms

[Guest]

I am creating a database that will show specific skills that are used by a
group of our employees. For example, I have a table and form for the skill,
"Events Coordination." Events Coordination has approximately 60 elements
that are included on the table and form as check boxes. The form is set up
so that if an individual checks one of the boxes, it is counted in the Total
field and also converted to a percentage of checked in the Percentage field.
Everything works fine. Now the problem. I need to be able to use the
calculated Percentage field from "Events Coordination" on other forms/queries
so that I can show the results for all 80 employees. By the way, I have 15
other categories besides "Events Coordination" to include with this database.
Is there a simple way of doing this or would I be wiser to work on this with
Excel? Thanx in advance.

 

[David Cox]

Perhaps you should split the events coordination table into a master record
and 60'ish linked criteria records each of which has a checkbox and a
description. You could then base your form on queries from these tables, and
use that information in other places.The design could thereby much more
extensible and adaptable too.

 

[Jeff Boyce]

So, you are saying that, at the present, you have "approximately 60
elements" which you use to evaluate how much "Events Coordination"
knowledge, skills and/or experience someone has.

And you've hard coded those into a table (I'm assuming a table that is
approximately 60 fields "wide"), and onto a form? And you may even have
queries and reports that are intimately tied to that table.

And you have another table for another "skill", and another form, and
another table ...

What happens when you need to add a new element to "Events Coordination"?
Won't you have to modify your table and modify your form and modify your
related queries and reports and code and ...?

What happens when you need to add a new "skill" (i.e., "Underwater
Basketweaving")? Won't you have to create a new table and new form and new
queries and ...?

This is a LOT of maintenance, but is absolutely necessary ... if you treat
Access like a spreadsheet.

I may be reading too much into your post ... but if your data is structured
along the lines of my scenario above, you really need to spend some time
re-defining your data structure. Access can't (easily) do its work (nor can
you) if the data it is fed is a spreadsheet, not a relational database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

And to respond to your question, there is a way to set up Access to help you
do this. If you haven't used Access or relational databases before, and
have used Excel (or other spreadsheets), you might find it much quicker to
use Excel!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I have used Access quite extensively but I just wasn't sure if it would be
the best method when I am going to require so many calculations on forms.
Thanx for suggestions.

 

[Guest]

Jeff: Your scenario is exactly what I am trying to accomplish. I would
prefer doing it with Access as I don't particularly care for Excel. I have
tried setting it up with two different structures and even tried using
Microsoft's Classroom database structure, but I have not been able to get the
calculation from the "Event Coordination" form to feed into a master form.
If you have any suggestions on the structure, please share. Thanx.

 

[Jeff Boyce]

I have only what I'm "inventing", based on my interpretation of your
description. Take this with a grain of salt...

You have Employees.

You have "Skills" (e.g., "Event Coordination").

You have "Indicators" (these are the 'approximately 60
checkboxes/fields...').

A Skill will have one/more Indicators.

(I can't tell if a particular Indicator might apply to more than one Skill.
On the chance that it might, here's a potential table structure...

tblEmployee
EmployeeID (PK)
LastName
FirstName
DOB

tblSkill
SkillID (PK)
SkillTitle (e.g., "Event Coordinator")

tblIndicator
IndicatorID (PK)
IndicatorTitle (e.g., "Able to use Outlook")

trelSkillIndicator
SkillIndicatorID (PK)
SkillID (FK)
IndicatorID (FK)

trelEmployeeSkillIndicator
EmployeeIndicatorID (PK)
EmployeeID (FK)
SkillIndicatorID (FK)

A couple things to note about this table structure...

If you need a new Skill, just add it to the (skill) table.

If you need a new Indicator, just add it to the (indicator) table.

If you need a new Employee, ... (you get the idea).

If you need to connect an Indicator to a Skill, just add it to the ... (a
form/subform works great for this).

If you need to show an employee as having "passed" certain Indicators
related to certain Skills, just add it to the (EmployeeSkillIndicator) table
(again, form/subform...).

Queries work well to show all of an Employees Skills and Indicators.
Queries can also work well to calculate "percentages".

You will have to do a lot of behind the scenes coding to get the
"checkbox"-like form to work AND to update properly when the number of
indicators changes. You might want to rethink this visual presentation. An
alternative would be something along the lines of a pair of listboxes,
showing "available" indicators and "achieved" indicators. If you aren't
familiar with this approach, you can see paired listboxes in action by using
the New Query wizard from the main database window.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[aaron.kempf]

Jeff

HOW DARE YOU

I CALL FOR THE IMMEDIATE EXECUTION OF JEFF BOYCE

'just use excel'

what kindof advice is that?

go play in the freeway, Jeff.

Aaron
ADP Nationalist

 

[Guest]

Thank you again. Sounds like I have a lot of work ahead of me but I think it
will be well worth the effort.