Rating Database Access 2003

[debinnyc]

I am trying to create a database to record performance measures for
individual people in an office. I will need to record multiple samples per
month, based on production. My first thought was to create a single table
with all the fields, but I see in other discussion topics there is a way to
create multiple tables and link them together.
Here is an example of what I want:

Empl Id, name, dept, supv name, audit number
Quality measure 1 (check box) excellent , (check box) good, etc... point
value
..
..

average point value
notes

I am assigning a unique id to each record through an auto number field such
as audit number.
So, if I have thirty employees and I perform 4 audits per month per
employee, I can record each audit, and analyze each employee's performance
over time based on scoring.
Does that make sense? I hope so.

Sorry, I have used access for a while from a reporting standpoint but have
little experience in design. Any help would be greatly appreciated.

 

[Jeff Boyce]

From your description, it sounds like you are asking if your Access table(s)
should look like a spreadsheet. You can do that, but you won't get much
advantage of Access' relationally-oriented features and functions if you
feed it 'sheet data.

If you are not familiar with the topics of normalization and relational
database design, there's a steep learning curve in your future before you'll
be able to make (full-er) use of Access.

If you ONLY need to use Access to report on data, could you simply link to
your Excel spreadsheet itself and generate the reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[debinnyc]

I am not trying to link it to a spreadsheet, I want to start a new system to
rate and compile rating data on employees. I want to be able to use the
individual audit records to produce hard copy feedback forms for individual
employees.

i.e. I will pull a random sampling of work, and rate it based on eight
criteria. Each criteria will have four catagories: excellent, proficient,
developing and unsatisfactory.

On the form, these catagories will be assigned a point value, and those
combined point values totaled and averaged to create a rating.

The form can then be printed out for the employee to review in
coaching/development sessions. Then the data can be queried.

Am I too ignorant at this point to design such a db?

 

[Larry Daugherty]

The general rule is one issue/question per thread but your concern
about your capacity to design your own application properly is worthy
of response so I'll address it first.

Create your application flawlessly on the first try? Absolutely
not.

Create your application with a lot of trial and error, more posts
to the newsgroups and lots of offline reading? Absolutely true.

I can see why Jeff thought you were doing something in Excel even
though you expressed Access and never once said "Excel"

You need to learn about Normalization and then learn to apply it to
your designs religiously. Go visit www.mvps.org/access and search for
"commandments". Those commandments really spell out a few issues.
That is a terrific site for Access developers.

This newsgroup and ...gettingstarted are the two best ones for novice
Access developers.

As your analysis of what your application is going to do progresses
you should be able to identify most of the entities in play in it.
All entities of a given type belong in the same table. That table is
usually given a name like the singular of the entity type because that
is what each record is really about. For example if I'm dealing with
People, I'll define a "tblPerson". Your preferences and mileage may
differ.

Once you start the actual design process you'll discover a lot of new
entities. Often these new entities are simple attributes of other
entities: Colors, for example. Why type out vermillion each time
it's needed? Create a table tblColor and list all of your colors in
it. When you need one, select it from a combobox that shows all of
the colors.

Some entities that I think you need:

tblEmployee
EmployeeID - Autonumber PK
EmployeeLast text Break out the names as
EmployeeFirst text shown. You can always
EmployeeMidle text concatenate to display.
EmpID text Employee number
DepartmentID Long Integer - Foreign Key
EmpSuervisor Long Integer - Foreign Key to supervisor
record in this table. Implies that you enter bosses first.

tblDepartment
DepartmentID - Autonumber PK
DepartmentName text
DepartmentDescription text
etc.

tblAudit
AuditID - Autonumber PK
AuditName text, number, ?? Don't use an autonumber
AuditDate Date/Time
AuditNote text

tblAuditEmployee Many side to Employee on the One side
tblAuditEmployeeID - Autonumber PK
EmployeeID Long Integer Foreign Key
AuditID Long Integer Foreign Key
WorkID Long Integer Foreign Key into tblWork - what is
being audited??
Quality integer value returned from option group

Note that there can't be an average on a single record with a
single value. You can generate that kind of information in your
reports....

If you intend to track several different "things" per audit then
this structure has to expand.

No attempt was made to demonstrate tblWork. It's just that you
have to identify what the audit is all about. If the same audit will
be applied to the work of several employees on the same day you can
resolve which data belongs in which table. If the data is exactly the
same for all elements at a single level then it probably belongs in
the level above. If it *can* change between records at a given level
then it must be changeable at that level.

HTH

 

[Jamie Collins]

As your analysis of what your application is going to do progresses
you should be able to identify most of the entities in play in it.

You need to learn about Normalization and then learn to apply it to
your designs religiously.

Great advice which you haven't followed yourself e.g.

tblEmployee
EmployeeID - Autonumber PK
...
EmpSuervisor Long Integer - Foreign Key to supervisor
record in this table. Implies that you enter bosses first.

At least with your comment "Implies that you enter bosses first"
you've had the decency to admit to introducing insert anomalies and
delete anomalies into the design. You committed the basic flaw of
confusing the entity ("officer") with the relationship ("officership")
by using the same table to model both.

All entities of a given type belong in the same table. That table is
usually given a name like the singular of the entity type because that
is what each record is really about.

Though you are of course free to use the naming convention of your
preference, and while I don't have any statistics (do you?) I'd
speculate that it is more usual to see a collective term used as the
name for an entity table, based on the idea that it is a set that
usually contains more than one e.g. 'LegalPersons'.

Go visit www.mvps.org/access and search for
"commandments". Those commandments really spell out a few issues.

Thanks, I haven't read those in a while

I get the tongue in cheek nature but would point out they are hardly
universal truths: there are some Access MVPs (I won't name names) who
admit publically to using spaces in database objects' names, who think
it's OK for autonumbers to have meaning and be exposed to users, etc.

If the one about "write comments [to] explain each variable" was
followed religiously (sorry <g>!), it would IMO be really annoying for
both code writer and reader. Personally I think virtually the
opposite: that a variable's name, type and context should convey its
purpose. I'd rather see a long and meaningful variable name than a
well-commented single-letter name (note that I think the need for
either is likely a symptom of a large sub procedure that should be
split into multiple smaller sub procedures). What comments to explain
variables are due in the VBA (aircode) below? (none, IMO):

Dim thisDoc As IDocument
Set thisDoc = Binder.Documents("Tony's Object Naming Conventions")

Dim thisConv As CNamingConventionDocument
Set thisConv = thisDoc

BTW if you don't get from the above that the class
CNamingConventionDocument Implements the interface IDocument then thou
shalt not maintain my code ;-)

Jamie.

--