file review dbase part 2

[Guest]

I am trying to create a dbase for employee file reviews. On paper each
review, lists: the employee, manager, district, file #, date of incident,
customer name and 25 yes/no questions. How would I go about creating a proper
table structure for this dbase? Each employee will have an almost infinite
number of files reviewed. The same goes for the manager reviewing the files.
The purpose for this dbase is to keep track of what employees are missing in
their files. Where they can be listed in reports. Thanks for any assistance
you can provide.

 

[John Vinson]

I am trying to create a dbase for employee file reviews. On paper each
review, lists: the employee, manager, district, file #, date of incident,
customer name and 25 yes/no questions. How would I go about creating a proper
table structure for this dbase? Each employee will have an almost infinite
number of files reviewed. The same goes for the manager reviewing the files.
The purpose for this dbase is to keep track of what employees are missing in
their files. Where they can be listed in reports. Thanks for any assistance
you can provide.

Sounds like you need at least the following tables:

Employees
EmployeeID
LastName
FirstName
<other appropriate bio data>
ManagerID

Incidents
IncidentNumber
<information about the incident... does each incident involve one
employee? maybe multiple employees? If multiple you need an
Incident-Employee resolver table, otherwise just an EmployeeID>
<does each incident involve a customer??

Questions
<how are the questions related to incidents? You know better than
I!>

Files
<what's a "file" in this context? One of these sheets of paper with
questions?>

More info please - in *one* newsgroup, not five...

John W. Vinson[MVP]

 

[Guest]

Sorry for the posts. Thanks for the help.

Employees looks good but do I need a table for managers?
incidents: there is only one employee per incident and per file

File: Each piece of paper represents a file, as the paper is the review for
the file.

Questions: The questions are the same for all of the file reviews (25 yes/no).

 

[John Vinson]

Sorry for the posts. Thanks for the help.

Employees looks good but do I need a table for managers?

A manager is an employee, no? That's what the ManagerID is in the
Employee table: a link *back into the employee table itself*, linking
to the EmployeeID of the manager. Sorry I didn't explain that.

incidents: there is only one employee per incident and per file

Then the "Files" and "Incidents" table should have an EmployeeID field
as a link.

File: Each piece of paper represents a file, as the paper is the review for
the file.

Questions: The questions are the same for all of the file reviews (25 yes/no).

Just to allow for flexibility in the future, then, I'd suggest
modeling this as a many to many relationship:

Questions <a 25-row table with the text of the questions>
QuestionNo <manually entered integer primary key>
QuestionText

Answers
AnswerID <autonumber primary key>
FileID <link to the files table>
QuestionNo <link to Questions>
Answer <yes/no>

This will let you change, delete, or add new questions without
redesigning any tables.

John W. Vinson[MVP]

 

[Guest]

Ok. I have created the tables: Employees, Files, Managers, Questions,
Responses. All are linked as you stated. Now I am trying to create a form for
the manager to complete. What I did was create a qry to base the form off of.
It all looks good except the questions. In the form the questions are blank.
I want the questions (which are the same for each file) to be listed. The
manager should simply have to input his/her name, the employee, customer info
and answer yes/no to all the questions. Then later create a report based on
the responses. Did any of that make sense?

 

[John Vinson]

Ok. I have created the tables: Employees, Files, Managers, Questions,
Responses. All are linked as you stated. Now I am trying to create a form for
the manager to complete. What I did was create a qry to base the form off of.

Probably not the best bet. One Great Master Query may well not be
updateable, and it will certainly only let you update some tables and
not others. Instead, consider using a Form with Subforms (and in your
case probably sub-subforms).

It all looks good except the questions. In the form the questions are blank.
I want the questions (which are the same for each file) to be listed. The
manager should simply have to input his/her name, the employee, customer info
and answer yes/no to all the questions.

I'd suggest a continuous Subform with combo boxes (bound to the answer
table's QuestionID but displaying the question text).

Then later create a report based on
the responses. Did any of that make sense?

Sure, but just remember that the Report is based directly on the
tables, not on the form.

John W. Vinson[MVP]

 

[Guest]

Not really, I may be over my head.

Probably not the best bet. One Great Master Query may well not be
updateable, and it will certainly only let you update some tables and
not others. Instead, consider using a Form with Subforms (and in your
case probably sub-subforms).


I'd suggest a continuous Subform with combo boxes (bound to the answer
table's QuestionID but displaying the question text).


Sure, but just remember that the Report is based directly on the
tables, not on the form.

John W. Vinson[MVP]