DATABASE DESIGN

[Guest]

I'm in the design phase for a new database and would like some help with the
design. My issue: I have a set of forms (each has 50+ known tasks). For
each task I need to allow an entry of a score (4 possible answers). My plan
is to have 1 record in the table for each task on the form. My issue is how
to design it so that the selection of the tasks is not done with drop downs
(too time consuming since the tasks will always be the same for the form). I
want to present the tasks and only require the scores to be entered. I also
need to be able to present a blank form that can be printed.

 

[Jeff Boyce]

You appear to be starting in the middle, or even at the end.

Access is a relational database ... with emphasis on DATA. If you already
have forms designed, before you even have the table structure, your cart is
definitely before your horse.

We're not there ... how 'bout a bit more context on the subject area?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for the response.

The reason the forms are designed is because the current application being
used has forms designed. It's currently a paper based system with some Excel
capture of the data. The current users are looking to keep the forms being
used but I'd be willing to try to change that if I can.

I have worked on the table structure. I've panned on having the basic
employee data table (not a big deal) -- ID, name, hire date, etc. I also
planned on a table with the detail task information for each employee
evaluation. (The employee data table and the detail table will be linked by
employee ID.) Each employee evaluation will have the 50+ tasks associated
with it from the current form. Since we know what the task names are I don't
see any reason for the user to click a drop down to identify the task. I
want to present them with a list of tasks and have them enter the evaluation
results for that task. (I want to design the data capture of the detail in
the simplest form possible for the user.) Then each of these tasks will
become a record in the detail task information table. At least that's what
I'm envisioning. Is there something I'm missing?

With the current design I can create reports and manipulate the data in any
number of ways. I'm searching for the best way to set up the data capture.
Do I still have the cart before the horse?

 

[Pete D.]

You really need to design the tables for each part of your data, eventually
you maybe able to get to using something very similar to your current forms.
What your asking for will be completed easier if you break it down first.
Table Tasks
Table Employee
Table Evaluations
Table 1 to many probably employee task group
Relate tables together you may find more as you go. Once this is completed
the forms will almost build themselves and then you can concentrate on the
user interface.
Read Description of database normalization basics in Access 2000
http://support.microsoft.com/kb/209534

 

[Guest]

In case my prior post doesn't explain it well enough --

I'm faced with 20+ different types of employee evaluations (each 1 is for a
different machine). For each of these 20+ different evaluations there are
50+ tasks associated with it. The tasks are always the same for a particular
evaluation but they vary from evaluation to evaluation. So if an employee is
evaluated on machine A there will be 50+ tasks associated with machine A.
Now the user needs to input the results associated with each of those tasks.
Since the tasks for machine A will be the same for employee #1 as well as
employee #2, I want to keep the entry of the results as simple as possible.

 

[Michael Gramelspacher]

In case my prior post doesn't explain it well enough --

I'm faced with 20+ different types of employee evaluations (each 1 is for a
different machine). For each of these 20+ different evaluations there are
50+ tasks associated with it. The tasks are always the same for a particular
evaluation but they vary from evaluation to evaluation. So if an employee is
evaluated on machine A there will be 50+ tasks associated with machine A.
Now the user needs to input the results associated with each of those tasks.
Since the tasks for machine A will be the same for employee #1 as well as
employee #2, I want to keep the entry of the results as simple as possible.

Basically you have the same structure as a survey, which has been discussed
here a lot lately.

Evaluation Form = Survey
Task = Question

Task Answer = Survey Answer

The answer you say is always 1 of 4 possible choices.

Maybe just look at a structure similar to what I used here:
http://www.psci.net/gramelsp/temp/Survey_Practice_2.zip

 

[Pete D.]

Add to my previous a Machine table which can identify the tasks associated
with that machine.

 

[Pat Hartman \(MVP\)]

When a user starts a new evaluation, they will enter the employeeID and the
type of evaluation. With that information, you can take the tasks for the
specified evaluation from the table that defines them and append them to the
evaluation response table which will probably be displayed as a subform on
the main form. The records will have only an option group or checkbox or
whatever is needed for the answers.

You can even format the subform so that it blends in with the surrounding
mainform and the whole thing can just look like one long form.

make sure you remove the record selectors from the subform and also set its
allow additions and allow deletions properties to no. you can also remove
the navigation bar and just use a scroll bar to move through the task list.

I have several forms that work like this and they work fine.

 

[Jeff Boyce]

Thanks for the clarifications. I think Michael and Pete are offering more
workable directions, given your situation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thank you for the help! I think I can make this work.