Database Design question

[lmnop7854]

I am a somewhat newbie - I have worked with Access before, but I always have trouble with design. I have to set up a database that will be used to enter data regarding contract information. There are 100+ questions to be answered per contract, with responses being in several different data types, and then maybe 200+ contractors. So the database needs to store each contract for each contractor. It seems simple enough, but the responses are what is confusing me. The responses to most of the questions are Yes/No, but there needs to be a field for each of these Yes/No answers for free text comments (in case the contractor wants something "special"). Other responses could be dates (which is at least concrete), and some of the responses may have more than one answer (on some of the questions that get down into the contract detail, there may be reimbursement rates that differ by category). What this means is that for almost every question, there is at least 2 answers (or the possibility of more than one answer).

I'm not worried about setting up the tables, queries, forms and reports for the database, because I have done millions of those for existing databases that we have. I just haven't designed one in a long time, and this one is freaking me out. Any help you can provide would be wonderful!

Lisa

 

[KenHigg]

Lisa, Interesting db. Primary question - Will each contract always have exactly the same questions or will you need to modify the questions from time to time or maybe have a different set of questions depending on the contractor?

kh

 

[Mile-O]

Do a search on the keyword survey by the user Pat Hartman.

 

[lmnop7854]

Ken:

My understanding is that the questions will always be the same, for every contract.

Lisa

 

[KenHigg]

Lisa,

I would take Stew's suggestion to search the forum - a lot of ground has been covered on this type stuff...

But repost if you can't find anything there

kh

 

[DALIEN51]

Controlling Data Entered By Code

This is really where unbound forms come into there own. If you create a save record button as apposed to having a bound form allowing you to create data straight away into a table you can code this button to check each field prior to writting to the table. Coding the button also allows you to throw up errors, make different fields appear dissapear dependant on answers ie if there are the options YES, NO, YES (but with options), then if the YES (but with options) answer is selected you can make a field become visible to key in what those options are. Personally for this reason I never ever ever bind a form to a table.

Regards.

 

[KenHigg]

This is really where unbound forms come into there own. If you create a save record button as apposed to having a bound form allowing you to create data straight away into a table you can code this button to check each field prior to writting to the table. Coding the button also allows you to throw up errors, make different fields appear dissapear dependant on answers ie if there are the options YES, NO, YES (but with options), then if the YES (but with options) answer is selected you can make a field become visible to key in what those options are. Personally for this reason I never ever ever bind a form to a table.

Regards.

Never tried this - thought of, but never tried it. I may give it a whirl on my next db (if the db is simple enough

kh

 

[Mile-O]

If you create a save record button as apposed to having a bound form allowing you to create data straight away into a table

A bound form doesn't immediately put data in any table. Entered data still has t go through the form's BeforeUpdate event to ensure that the data is requested to be saved.

 

[lmnop7854]

Almost there...

To all who responded: thanks so much for your help. I have gone through the survey and questionnaire threads, and I found a great many ideas that will help me.

However, I am still stuck on the Responses part. For most of the questions, there will be one answer (Yes or no, a date, or a $ amount). But then there will be other questions for which there will be one or more answers. I know I should be avoiding a table with fields called Response1, Response2, Response3, etc, but I am not sure where to go with this. I can even handle the Comments field, because there will be only one Comment per question. Any examples you have that you could send would be helpful (be advised I am in the dark ages, using Access 97).

Lisa

 

[KenHigg]

I think I would approach this as contract attributes table instead of a questionaire / respones db. In which case instead of the flds beings refered to responses in a questionaire, they would be straight up data flds that contain data about each contract.

That being said, I would do the main table like:

tblContracts
contractID - pk
companyID - fk
contratSubmitted - yes/no
submittedDate - date
contractReturned - yes/no
returnedDate - date
etc...

You could then build logic into the input form that would turn stuff on and off, etc...

???
kh

 

[KKilfoil]

If it were me, I would build a new table (say [tblQuestionResponses]) and establish a one-to-many relationship with your table presently containing the survey Question reponses. This new table will contain the responses (if any), beyond the yes/no choice, so you are not carrying a lot of empty fields in your exisitng table.

You will probably need a sub-sub-form to display your responses in a meaningful way.

HTH

 

[Pat Hartman]

This is really where unbound forms come into there own. If you create a save record button as apposed to having a bound form allowing you to create data straight away into a table you can code this button to check each field prior to writting to the table. Coding the button also allows you to throw up errors, make different fields appear dissapear dependant on answers ie if there are the options YES, NO, YES (but with options), then if the YES (but with options) answer is selected you can make a field become visible to key in what those options are. Personally for this reason I never ever ever bind a form to a table.

-FYI DALIAN51 - ALL of this can be accomplished with bound forms with much less coding. You only need to understand the various control and form events and when they should be used.

The key to using a bound form is understanding that NO MATTER WHAT, the form's BeforeUpdate event is the last event executed prior to the data actually being committed to the table. Everything comes together here. If you want to include edits that prevent a record from being saved, this is the place to do them. To stop a record from being saved takes a very simple command -
Cancel = True

 

[lmnop7854]

I think I would approach this as contract attributes table instead of a questionaire / respones db. In which case instead of the flds beings refered to responses in a questionaire, they would be straight up data flds that contain data about each contract.

That being said, I would do the main table like:

tblContracts
contractID - pk
companyID - fk
contratSubmitted - yes/no
submittedDate - date
contractReturned - yes/no
returnedDate - date
etc...

Ken: This was my original plan - to use the questions as the fields, and enter the responses as the actual data. Except that each question needs to have an available comments field. So this is the crossroads I am at - use your suggestion above, and have at least 100 comments fields, all with free text; or create a survey-like database, with tblQuestions and tblResponses, except that I still don't know how to deal with multiple responses to specific questions.

Any other suggestions? Or maybe there is something I am not understanding that you could beat into my head with a rock?

Lisa

 

[KenHigg]

Are you taking this from an existing hand written system? I think the problem with making any suggestions from this side is trying to comprehend the exact nature of you questions / attributes... Think you could post some examples?

kh

 

[lmnop7854]

Here are some examples of the questions and their possible answers that were given to me. I still have a few questions about a couple of them, but at this point, I am just trying to get the tables designed in a workable way. As you can see, some of the questions have more than just a yes/no answer, and for some of those, it could be any combination of the choices listed. For the answers that include $, there could be $ for every one of those categories, or for none of them.

Lisa