Forms type database

[Leif]

I've done many database designs and implementations. However, for me, this
current project is a bit unique.

Access may not have been the best choice for this type of project. However,
I don't know of other software to handle it. In any case, I'm well along in
the project and it would be difficult to change. However, perhaps you have
suggestions for this project or how I should handle a simlar project in the
future.

It requires tracking responses to many questions over many forms in a
"package". The inputs may be text, check boxes, combo boxes type input.

Due to the variations in forming of each of the forms I've stored the
questions in separate forms and report modules. The questions are not in a
table, only the answers.

At some point a package is considered done. At that time it is marked as
archive. The user may not make changes, but an admin user may.

The trick is that over time questions may changed, added, or deleted.
However, they still need to be able to pull up their old package for review
and/or printing.

I'm planning to handle this by keeping an internal revision number of the
forms/reports. When the user marks as archive the current internal revisions
are saved with the record (answers) for each form.

This has proved to a lot of programming work. Does anyone have a suggestion
on how I could have done this in a better/easier manner?

Thanks.

 

[Fred]

Databases store structured "tabular" type data in tables. Forms, reports
and queries provide user interfaces to the data. I mention this for two
reasons:

- A foundation for evaluating whether or not this is a database application

- Help in some thought processes, because you mentioned the idea of
storining data in a form.

In the general (worst) case scenerio you could have packages continuusly
changing in all respects. For example, the quantities and wording of
questions continuously changing. In that case, your mian data is not well
suited for databasing, and you probably need an electronic filing cabinet
full of (e.g. Word) documents. You could created a table structure where
the questions themsleves (as well as their answers) are the data to be
databased, but this may not proved to be that useful.

If you have some degree of continuity, then you need to look at the
situation and decide which things are valid long term. For example, is there
an ongoing "pick list" of questions? Is there a standard number of questions
in a package? And then structure you tables around them.


Hopefully this helps a little even if it's a little vague. Much key info
(such as the answers to the above questions) is not known to us yet.

Sincerely,


Fred

 

[Leif]

Thanks Fred. See answers embedded below.

Databases store structured "tabular" type data in tables. Forms, reports
and queries provide user interfaces to the data. I mention this for two
reasons:

- A foundation for evaluating whether or not this is a database application

- Help in some thought processes, because you mentioned the idea of
storining data in a form.

In the general (worst) case scenerio you could have packages continuusly
changing in all respects. For example, the quantities and wording of
questions continuously changing. In that case, your mian data is not well
suited for databasing, and you probably need an electronic filing cabinet
full of (e.g. Word) documents. You could created a table structure where
the questions themsleves (as well as their answers) are the data to be
databased, but this may not proved to be that useful.

I'm told the questions will not change frequently, although clearly then
will change/add/delete over time. This occurred to me in the beginning and I
decided to give it a try as a database. However, as I mentioned, there may
other software better suited for this application. I remember a LONG, LONG,
LONG time ago there was an application called Forms that addressed this type
of application. I have no idea if it still exists.

If you have some degree of continuity, then you need to look at the
situation and decide which things are valid long term. For example, is there
an ongoing "pick list" of questions? Is there a standard number of questions
in a package? And then structure you tables around them.

No standard number of questions per package, or even forms per package. The
questions are not presented as a picklist, rather they are presented in the
form as text, combo, or checkbox caption. I did not go with the questions in
a table since the formatting changes enough that I need to customize the look
of the forms. In addition, adding the questions to the database, and making
control's text as linked labels, would have been another magitude of work.

I scheme I'm working with now is to tag each forms and associated report
with an internal revision. When the user archives the record the current
form/report revision will be saved. Its ackward, but it seems the best idea
at the moment.

 

[Fred]

I think that there are three different meanings of the word "form" floating
around here:

1. A blank questionnaire sheet which someone is going to complete

2. A recorded instance of a set of answers to a questionnaire

3. An Access object which provides the user a "window" to data in tables.

It could very well be that an electronic filing cabinet with a bunch of Word
documents in it it your best solution. But here's one idea that may or may
not be good depending on your particulars (of course, shorten the field names
whihc I made lone for descriptive purposes:

Table: Packages

Field: PackageNumber (autonumber, PK)
Field: PackageNameOrDescription

Table: Questionnaires

Field: QuestionnaireNumber (autonumber, PK)
Field: PackageNumber (integer, FK) (linked to Packages.PackageNumber)
Field: QuestionnaireNameOrDescription

Table: Questions

Field: QuestionNumber (autonumber, PK)
Field: QuestionnaireNumber (integer, FK, linked to
Questionnaires.QuestionNumber)
Field: QuestionText


Table: Respondents:

Field: RespondentIDNumber (autonumber, PK)
Fields: Name and other info on the respondents

Table: CompletedQuestions

Field: QuestionNumber (FK, linked to Questions.QuestionNumber)
Field: AnswerText
Field: RespondentIDNumber (integer, FK, linked to
Respondents.RespondentIDNumber)

The "Core" form would be a questionnaire form, with a datasheet style
subform which has, as its recordsourc a query which joins the "Questions" and
"CompletedQuestions" tables. All fields in the subform would be locked to
be "View only" except the AnswerText. Code the form to autoload the
RespondentIDNUmber into that field in the CompletedQuestions table.

I havent tried this (and there are lots of undescribed details) but I thin
that this would provide a DB application where EVERYTHING (Packages,
Questionnaires, the Questions on them and all completed questionaires) would
be stored in tables. No more creating, designing or storing multiple
copies of forms!

This may or may not be good for your situaiton.

Sincerley,

Fred

 

[Leif]

Thanks Fred for your detailed reply.

I've done something similar to what you suggest, however, I have left the
questions on the forms/reports rather than placing them in the database.
Your solution is certainly more general purpose, however, it is difficult in
my case due to general (varying) formatting considerations.

What I've got to work is to create form and report revisions. The current
form & report revision number is recorded in a table. So to begin with all
my forms and reports have names like FormA_0, FormB_0, ReportA_0, ReportB_0,
etc. When a form or report is updated I will create a new access form/report
with a name like FormA_1 or ReportA_1.

The user agrees that the form they started with they will stick with, even
if there is a form update before they complete the package. Packages may be
worked on for several weeks.

It seems to work. My coding thus looks like docmd.openform formname & "_" &
revision, where form name and revision is taken form the package established
when it was created. Creating the package copies the latest forms and
revisions from another table that will be updated as forms are
added/changed/deleted.

Thanks again for your suggestions.