If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Forms type database
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. |
#2
|
|||
|
|||
Forms type database
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 |
#3
|
|||
|
|||
Forms type database
Thanks Fred. See answers embedded below.
"Fred" wrote: 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. 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 |
#4
|
|||
|
|||
Forms type database
I think that there are three different meanings of the word "form" floating
around he 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 |
#5
|
|||
|
|||
Forms type database
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. |
Thread Tools | |
Display Modes | |
|
|