Tables, forms and relationships

[webcats2]

I have 20 tables that I need to link. I want to have data entry into the tables where the parent table creates an autonumber and that is also the foreign key in the parent and the primary in the child. The form is a series of questions where the answer is a combo box with yes/no/N/A. I hope I am making sense. Can this be done?

What I am trying to do is create separate forms for each table but would like them all to be one record. I can't get it to update the record. I have tried to put them all in one table but there is not enough room to create a single form because there are something like 206 fields.

Thanks.

 

[randysmid]

Hi,
I have read your request several times, but don't seem to understand the problem. So, can you provide an example of what you want to do?

HTH,
Randy Smith, MCP
California Teachers Association

 

[willir]

webcats2

Sure it can be done.

But a couple of things.

What type of relationship exists between the parent and child -- one-to-one, one-tomany or many-to-many??

1) I want to have data entry into the tables where the parent table creates an autonumber
Not a problem -- this field is defined when you create your tables.

2) the foreign key in the parent and the primary in the child
This is unusual. Normally, in a one-to-many relationship, the primary key in the parent table is used as the foreign key in the child record. The foreign key is placed on the "many" side.

Here is why...
Using a simple invoice process as an example. (For this example, I am simplifying things for the purpose of demonstration.)

You would have the invoice "header" and "detail" tables, and a "customer" table.

tblCustomer
CustomerID or CustomerCode
CustomerName
CustomerAddress

tblInvoice
InvoiceNo
InvoiceDate

tblInDetail
InvDetailID
ProductCode
ProdPrice
InvQuantity

So how do you related customer to the invoice?
If you you put the invoice code on the customer table (put the foreign key on the parent table), you would need a field in the customer table to track each invoice. Instead, you put the customer ID or code on the invoice.

Ditto for the invoice detail. The invoice number, the primary key, will appear in the invoice detail records as the foreign key.

So I hope this issue has been clarified for you.

3) data entry into the tables
You are probably aware of this, but please realize that for the sake of preserving data integrity, data entry usually occurs with the use of forms. By entering the data into the forms, valid data is entered into the tables; controls and the user interface simplfy data entry, and prevent / minimize the risk of have incorrect data entered.

4a) The form is a series of questions where the answer is a combo box with yes/no/N/A.

Yes this can be done. But before you proceed, let's look at your design. (I had this discussion with a friend over the weekend)

A typical approach would be to have a table with fields 1 to 5 for five questions. This would work with provided that the questions would never change.

A better solution would be to have a question table and an answer table. This solution is more complex but allows for more flexibility. Since the questions are now in a table, rather than "fixing" a question to a field, questions can be edited, added and marked obsolete (not deleted - since this would possibly leave answers without a question).


4b)
I have 20 tables that I need to link ... and ...
What I am trying to do is create separate forms for each table but would like them all to be one record

This leads me to wonder what information you have in the 20 tables. Specificaly, which tables relate to the questions and how do they relate? I am guessing that you are tyring to create an "answer" table but have not quite got it worked out.


5) Some basics...
- When you run the combo box wizard, you have the ability to enter a list of acceptable values -- in your case, Yes, No and N/A.

- Linking a form to a subform usually requires the primary key to exist on the subform as the foreign key. When linking the forms, you edit the property of the subform to set the parent / child relationship. (It helps to ahve already defined the relationships with the relationship designer tool)

So I will look for any additional info regarding your tables and relationships.

Richard

 

[webcats2]

Thanks for the responses. Let me give a little more detail. I am creating a questionnaire for loans. Borrower information with 206 questions broken down into 18 sections. There can only be one answer for each question and only one set of questions for each loan.

I created a Borrower table using the loan number as the primary key. I then created 18 tables for each section of the questionnaire with an ID code for each section making that the primary key. I then added the ID codes to the Borrower table as a foreign key.

I the Borrower table and all the others in a one to one relationship. I got an error message that there were to many indexes in the borrower table but there are only 18 tables. I tried removing the indexes from 6 of the table to see if I could get them linked. I linked them but I am unable to get the table to update.

I made a separate form for each table with the borrower being the first to be populated. I want the 1st form to populate the subsequent forms ID codes as they are opened. This way the loan number will not have to be entered into each form. This is not happening.

I am not sure which of the solutions presented would be the best solution.

I also need all the tables updated as a single record.


Borrower table:
Borrower name
Date
Loan number
Q1_ID
Q2_ID
Q3_ID
Q4_ID
Q5_ID
Q6_ID
Q7_ID
Q8_ID
Q9_ID
Q10_ID
Q11_ID
Q12_ID
(etc.)

Question table1:
Q1_ID
Q1
Q2
Q3
Q4
Q5

Question table2:
Q2_ID
Q6
Q7
Q8
Q9

Question table3:
Q3_ID
Q10
Q11
Q12

(etc. up to 206 questions, 19 tables)

Thanks.

 

[willir]

webcats2

This very similar to a "survey" database I designed. Instead of storing the answers on the borrower's table, it uses an answer table. I see this having two advantages...
- more flexible - can add or remove questions as required without making changes to the database design
- less tables
- greatly reduces the number of indexes (Access creates an index for any foreign key)

If you want it, here it is, amended for your requirements...


[blue]tblBorrower[/blue]

BorrowID - primary key, autonumber, could be other if required
LastName
FirstName
etc



[blue]tblLoan[/blue] - master file for specific loan

LoanNumber - primary key
BorrowID - foregin key to tblBorrow. Note: a customer can have more than one loan, many-to-one
MasterID - foreign key to tblMasterQuestion, one-to-one
BDate
BTerms
LoanOfficer - probably want to create a table for loan officers and link as a foreign key
etc



[blue]tblQuestion[/blue] - suppository of questions

QuestionID - primary key, autonumber
QuestionTxt - question

can also add...
[red]- hyperlink to external document[/red]
- owner of question (accountability type of thing)
- review date
- sort code
[red]- mandatory yes/no field if question has to be answered
- imporatance if you want to flag important and misc questions[/red]



[blue]tblProfileQuestion[/blue] - profile for a "grouping" of questions

ProfileQID - primary key, autonumber
ProfileSbGrpID - foreign key to tblSubGroup
QuestionID - foreign key to tblQuestion
LineNo - interger, used for sorting questions

Note: ProfileQID is the primary key, but a unique index for [red]ProfileSbGrpID[/red] + [red]QuestionID[/red] is used to ensure only one profile exists for question x subgroup.

can also add...
- revision date



[blue]tblSubGroup[/blue] - sub units for questions

ProfileSbGrpID - primary key, autonumber
SbGrpCode - sorting field / description of group

can also add...
- revision date
- owner of subgroup
- scope - when to use this subgroup of questions
- comments

Would cpature things such as credit check, employer check, etc




[blue]tblProfileSbGrp[/blue] - profile used to group subgroups to the master file

MasterID - primary key [red]MasterID[/red] + [red]ProfileSbGrpID[/red]
ProfileSbGrpID
LineNo - interger, sorting field, used to organize subgroups in sequence

can also add...
- revision date
- comments



[blue]tblMAsterQestion[/blue] - Master group for questionaire. You may only have one Master group for your situation, or you may decide to have more

MasterID - primary key, autonumber
MasterCode - sorting code, or text description

can also add...
- owner
- revision date
- scope - when to use this selection / set of questions, etc
- comments

Examples...
Car loan
Student loan
Mortage




[blue]tblAnswers[/blue] - capture answers from questions

AnswerID - primary key, autonumber
LoanId - foreign key to tblLoan
ProfileQID - foreign key to tblProfileQuestion


plus desired answer fields for questions...
- AnswerTxt - details for answer, either a text field, 255 character or memo
- AnswerYes - yes / no for successful answer
- AnserrNA - yes / no if the question is applicable

can also add...
- memo field for comments
- anwser date



I realize this may seem complicated, but it is more complicated than it actually is...

[blue]1) Determining questions[/blue]
1a) MasterQuestion table is used to create a super group.
For example...
- car loan
- mortgage
- student loan

1b) SubGroup table is used to group the questions into subgroups.
- employer check
- credit check
- co-signer check

1c) Questions to be asked

I have set up a many-to-many relationship for
MasterQeustion x SubGroup - uses table ProfileSbGrp
SubGroup x Question - uses table ProfileQuestion


In summary...

Master -> SubGroups -> Questions for each subgroup

These many-to-many relationships may make the design seem complicated. Just think of the big picture as you work your way through.



[blue]2) Determining questions to ask borrower[/blue]

2a) Loan table used to determine "MasterQuestion" or type of survey
Again
- car loan
- mortgage
etc


2b) Using the relationship...
Master -> SubGroups -> Questions for each subgroup
A blank question or survey sheet is generated
- each answer is linked directly to the loan table (one-to-many)
- each answer is linked indirectly to the question table (many-to-many via the tblProfileQuestion table)
- each answer is linked to subgroup table (one to one, this was for sorting purposes only; in my case your "loan officer" could be different for each survey question)

In summary...

Loan -> MasterQuestion
Loan -> Answer
Answer -> ProfileQestion -> Question
Answer -> Question subgroup

Again, to get around the complexity, just remember the big picture...
- You need to know what type of suervey to ask (Loan -> Master)
- You need to tie your answers to the loan (Loan -> Answer)
- The answer is linked to the question


Bonus:
Since the question record can be linked to an external document, it give you access to an external form, or instructions or procedures for the question.



[blue]3) Answer questions[/blue]
The questions needed to be asked were created in step 2

Now the answers are created...
- text field cpatures text
- true/false or yes/no field caputers success or failure
- additional true/false flags the question if not applicable

This component is fairly straight forward in terms of relationships.
But, you need logic to flag your loans - accepted, researcing etc. Which means you have to
- Count the acceptable answers,
- Exclude tne N/A questions
- Red flag mandatory or important questions that failed


There is one gotcha...
A question should never or edited in the event it was used in an earlier loan questionaire. Rather, it would be advisable to create a new question. In the profile table delete the tblProfileSbGrp, the table used to associate a question to link the profile to a new question. This is why the tblProfileQuestion has two apparent primary keys (only one, ProfileQID, but the [red]ProfileSbGrpID + QuestionID [/red] is also unique -- it allows the integrity of old data to be preservered but to work with new data.)


Please note
- I have indicated loan officer and owner references in the tables. In my field, ownership of questions is really important so I have allowed this info to be tracked. For my database, these are linked using a foreign key to a Loan Officer table
- Ditto for revision dates on questions, profiles, etc

Did I loose you??

Richard

 

[webcats2]

Thank you. I am going to work on it right now.

 

[nberryman]

This is from Bill Gates himself (well, Microsoft.com)

ACC: Sample Table Design to Support Questionnaire Applications

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0
Microsoft Access for Windows 95, version 7.0
Microsoft Access 97

--------------------------------------------------------------------------------


SUMMARY
Novice: Requires knowledge of the user interface on single- user computers.

This article describes a table design that you can use for an application that tallies results from questionnaires and surveys.



MORE INFORMATION
The following examples outline nonrelational table design commonly used for questionnaires and surveys and suggests an improved, relational table design.

Nonrelational Table Design
When designing tables for questionnaire applications, many users begin with a design that resembles the table below. Each record contains multiple fields, called Question1 through Question<n>, that contain responses to the questions.

Table: Table1 (old)
--------------------------------------
FieldName: Respondent ID [Primary Key]
FieldName: Question1
FieldName: Question2
FieldName: Question3
.
.
.
FieldName: Question<n>

Problems occur when you want to perform crosstab queries to summarize and/or graph the questionnaire results.
Relational Table Design
A relational table design better supports summary queries, reports, and graphs. In the table below, the Question ID field identifies the question and the Response field contains the answer.

Table: Table2 (new)
----------------------------------
Field: Respondent ID [Primary Key]
Field: Question ID [Primary Key]
Field: Response


How to Switch to Relational Database Design
To convert data that has been entered in Table1 format, follow these steps:

Create Table2 according to the above structure, using the following data type criteria:


All Respondent ID fields must be the same data type.


The Question ID field must be a Text data type.


The Response field must be the same data type as the Question<x> data types.





Create a new query based on Table1.


From the Query menu, choose Append Query (Append in versions 7.0 and earlier) Select Table2 as the table to which you want to append the data. Design the query as follows:

Query: Query1
---------------------------
Field: Respondent ID
Append To: Respondent ID
Field: Question1
Append To: Response
Field: &quot;Question1&quot;
Append To: Question ID



Run Query1 to append to Table2 each participant's responses to Question1.


Repeat steps 2-4 above, replacing Question1 with Question2, and &quot;Question1&quot; with &quot;Question2&quot;. You must re-create or modify this query for each question in Table1.


After running all the append queries, the result is a table (Table2) that can easily summarize your results in a crosstab query:

Query: CrossTabExample
---------------------------

Field: Question ID
Total: Group By
Crosstab: Row Heading
Field: Response
Total: Group By
Crosstab: Column Heading
Field: Response
Total: Count
Crosstab: Value






REFERENCES
For more information about append queries, search the Help Index for &quot;append queries,&quot; or ask the Microsoft Access 97 Office Assistant.

Additional query words: cross tab append questionnaire flat file survey

Keywords : kbusage
Issue type : kbinfo
Technology : kbAccessSearch kbAccess110 kbAccess200 kbAccess100 kbAccess97 kbAccess97Search kbAccess95Search kbZNotKeyword3 kbAccess700





Neil Berryman
IT Trainer
neil_berryman@btopenworld.com