Data entry data base question

[Dennis the Safety Nerd]

I am trying to design a bata base for a statistical data collection system.
I have data collection sheets that need to be entered. Each sheets has
Person, Date,Department. The data consists of 8 items, that are marked yes,
no, or not seen. If the data is no, there must be a comment, with fields for
comment quality, (1-5) type(1,2,3) and correct marking (yes no)
I can not figure out if I need a seperate table for the data, or if all the
data goes on one tabe with look ups for names and departments, and do the
comments go in a subform?

 

[John Vinson]

I am trying to design a bata base for a statistical data collection system.
I have data collection sheets that need to be entered. Each sheets has
Person, Date,Department. The data consists of 8 items, that are marked yes,
no, or not seen. If the data is no, there must be a comment, with fields for
comment quality, (1-5) type(1,2,3) and correct marking (yes no)
I can not figure out if I need a seperate table for the data, or if all the
data goes on one tabe with look ups for names and departments, and do the
comments go in a subform?

Don't be too hasty to think about Forms: the table structure and
relationships come first, and then you fit the Form to the data
structure.

In this case, it seems that you have a many (people) to many (items, 8
to be exaxt) relationship. I'd suggest the following tables:

Departments
DepartmentNo Primary Key
DepartmentName

People
PersonID Primary Key <autonumber or a unique Employee number>
LastName
FirstName
<other bio data>

Topics
TopicNo Primary Key
Topic <one of your eight statistical subjects>

Answers
PersonID <link to People>
TopicNo <link to Topics>
Answer <integer, perhaps 1 = Yes, 2 = No, 3 = Not Seen>
Comment <text or memo depending on expected length of comment>
Type <integer>
Correct <Yes/No>

Use a Form based on People, with a Subform based on Answers; there'll
be combo boxes on the subform for TopicNo, and either combo boxes or
option groups for Answer and Type. You may want small lookup tables
(NOT Lookup Fields though!) for Answer and Type.

John W. Vinson[MVP]

 

[tlyczko]

Hi John, you partly answered one of my questions with your reply.

If one of the topics changed names or became inactive or a new topic
was added, how would this be accounted for in the database tables
design??

I am constructing a similar database, though I have Locations and
People which have topics and questions about each topic.

Thank you, Tom

 

[John Vinson]

Hi John, you partly answered one of my questions with your reply.

If one of the topics changed names or became inactive or a new topic
was added, how would this be accounted for in the database tables
design??

I am constructing a similar database, though I have Locations and
People which have topics and questions about each topic.

If you store the name of the location (Topic) in a table with a
numeric Primary Key, and store a numeric foreign key in the data
table, then any change to the text of the Location will automatically
be applied. A Query linking People to the PeopleLocations table, and
thence to the Locations table, will pick up the current value of the
location, whether it's changed or not.

Adding a new record to the Locations table simply makes that location
avaialable to be added to the PeopleLocations table. Deleting a record
would either give an error message (probably the best bet!) if there
is an existing record, or - if you have defined the relationship
between Locations and PeopleLocations with Cascade Deletes - it will
delete all record that that location had ever been associated with
anyone.

John W. Vinson[MVP]

 

[tlyczko]

Hello John,

Thank you for replying.

I need to keep both the 'old' and 'new' version of the question texts,
so it now seems to me that having a 'version number' for each question
plus an 'active/inactive' field for each question would allow me to do
sufficient versioning to permit keeping old question texts for
reference and to permit referencing old / deactivated questions and to
incorporate new questions as necessary.

Do you think this makes sense?? Or is there an easier/simpler way to
'version' past and current, active/inactive questions??

Thank you,
Tom

 

[tlyczko]

Hello again,

I also just realized that because each question has a unique ID and
that ID is stored with the question responses, then using
Max(QuestionVersion) would likely keep the questions current yet permit
the older question version to apply to previous surveys...that is, the
highest version number is the most current question version, if an
older question number is used, then its ID is stored in the responses
table...

Thank you,
Tom