Prepopulation & Normalization

[robertmeyer1]

Hi,

I have 3 tables: Client, Question, and Answer. Each has a PK AutoID. The relationships are: tblAnswer (PK AnswerID, FK ClientID, FK QuestionID) then an Answer field. Now here’s the issue. I am trying to create a form which will allow me to link a specific answer to a specific questionID. I will end up having multiple forms based off this 1 table. (There are about 1000 questions to answer per client (all currency); each questionID will have a specific answer). Now I have created a form but I cannot get the answer field to match to the questionID field. I added about 30 answer fields to the form, (hoping for a 1 question – 1 answer deal) but I simply get 30 answer boxes that relate to the same field. So when I enter a number in 1 of the boxes, all the other boxes show the same number. This is not what I need. I need for the answers to be linked to the question Id. So 30 answers boxes, each relates to a single question. I am having absolutely no luck figuring this out. PLEASE HELP!!!

[MMcCarthy]

Instinctively I would say that a Client has many questions and questions have only one answer.

Therefore

tblClient {ClientID (PK), etc.}
tblQuestion {QuestionID (PK), ClientID (FK), etc.}
tblAnswer {AnswerID (PK), QuestionID (FK), etc.}

Answer should not be tied to question and client as question is already tied to client and this just confuses the issue.

The following query should result in All Clients, All Questions for each client and All Answers per question.

 
SELECT tblClient.ClientID, tblClient.ClientName, tblQuestion.Question, tblAnswer.Answer
FROM (tblClient LEFT JOIN tblQuestion 
ON tblClient.ClientID=tblQuestion.ClientID)
LEFT JOIN tblAnswer
ON tblQuestion.QuestionID=tblAnswer.QuestionID;
 

[robertmeyer1]

Ok. I've followed this and have a query which shows the same sql. But how do I use that to help with data entry though forms? I need to set up some forms to allow me to have an answer for a question for all clients. Thanks.

[MMcCarthy]

Ok. I've followed this and have a query which shows the same sql. But how do I use that to help with data entry though forms? I need to set up some forms to allow me to have an answer for a question for all clients. Thanks.

Ok this was just to get the structure right.

Now create a form based on Client Table.

Create a query based on Question and Answer Table.

e.g.

 
SELECT ClientID, Answer, Question 
FROM Question LEFT JOIN Answer
On Question.QuestionID=Answer.QuestionID;
 

Now create a second form based on this query.

Put the second form on the first as a subform joining on clientID

Now when you open the form you will see the client and a list of questions and answers in the subform.

[robertmeyer1]

Thanks for the help, I really appreciate it. Here’s another question: I have everything set up the way you suggested and it works great. The only thing I would like to do is have it so more than 1 question will show up on the form at a time. On the sbf (based off qry Question & Answer) I have 1 question box and 1 answer box. The question is displayed in the question box. To see the next question you have to click on the > (next record) to show the 2nd question…. Is there a way to have about 20 questions show at once with 20 answer boxes? Thanks again.

[MMcCarthy]

Thanks for the help, I really appreciate it. Here’s another question: I have everything set up the way you suggested and it works great. The only thing I would like to do is have it so more than 1 question will show up on the form at a time. On the sbf (based off qry Question & Answer) I have 1 question box and 1 answer box. The question is displayed in the question box. To see the next question you have to click on the > (next record) to show the 2nd question…. Is there a way to have about 20 questions show at once with 20 answer boxes? Thanks again.

In the subform change the default view in the properties of the subform under format tab to Datasheet or Continuous Forms. Whichever suits you better.

[robertmeyer1]

Hi,

Ok, I have it all set up as described/recommended above. On the main form, based off tblclient and including sbfAnswer. I now have the ability to enter in the numbers for a specific client. That is, I have a datasheet view which I can type a number into. But when I check the tbls, the number is not being entered properly. Instead of filling in an answer box from the list from tblQuestions, it is adding new records. Ex: the list in questions goes to 100. In tblAnswers, the new entered data is in record 101. This record is also added to tblQuestions. So instead of answering questions 1-100 in the fields, it is allowing for the creation of more fields in both tblAnswers and tblQuestions. What can I do? Thanks.

[MMcCarthy]

Hi,

Ok, I have it all set up as described/recommended above. On the main form, based off tblclient and including sbfAnswer. I now have the ability to enter in the numbers for a specific client. That is, I have a datasheet view which I can type a number into. But when I check the tbls, the number is not being entered properly. Instead of filling in an answer box from the list from tblQuestions, it is adding new records. Ex: the list in questions goes to 100. In tblAnswers, the new entered data is in record 101. This record is also added to tblQuestions. So instead of answering questions 1-100 in the fields, it is allowing for the creation of more fields in both tblAnswers and tblQuestions. What can I do? Thanks.

Check the main form and the subform under the data tab for the Data Entry property. Make sure it is set to No on both forms.

[MMcCarthy]

Check the main form and the subform under the data tab for the Data Entry property. Make sure it is set to No on both forms.

The questions already assigned to each client should show on the subform. 1 - 100 records.

[robertmeyer1]

Ok, I think this may be part of the problem. I am using this DB to add clients, then to answer the questions. The questions will remain the same for all clients. And the answers should remian based off these questions. Would adding a new client and then trying to perform data entry on the mainform (with sbf) affect this? I can't think of why else I am having this problem, so if this is the reason, any suggestions? Thanks.

[robertmeyer1]

Both sbf and main form are set to No on the properties

[MMcCarthy]

Ok, I think this may be part of the problem. I am using this DB to add clients, then to answer the questions. The questions will remain the same for all clients. And the answers should remian based off these questions. Would adding a new client and then trying to perform data entry on the mainform (with sbf) affect this? I can't think of why else I am having this problem, so if this is the reason, any suggestions? Thanks.

You need a tbl which will join the client, the question and the answers per client. This would replace the table answer and have one hundred records per client.

New Table

ClientID (PK - Joint primary key)
QuestionID (PK - Joint primary key)
Answer (text)

This would be the record source of the subform and would allow you to choose a questionID and give an answer for this client.

[robertmeyer1]

Ok. I greatly appreciate the help with this. I am still having some problems. So let me explain what I have in the DB now. I have 3 tbl’s: tblClient, tblQuestion, tblAnswer. tblQuestion has a QuestionID PK and ClientID FK. TblAnswer has ClientID PK & QuestionID PK. These are all in relationships w/ enforced referential. Next I have a sbf based off tblAnswer. I have the fields: Answer (currency) QuestoinID (not enabled). Then I have a mainform based off tblclient. This form shows the client information at the top of the form, then has the sbf inserted in. The subform currently only shows answer box (on datasheet view). Now here is the issue: In the Mainform, when I enter a # it says “You cannot add or change a record because a related record is required in table “TblQuestion”. Also, the sbf does not allow data entry because it says I need to enter the clientAutoId. This message does not appear in the mainform, so I think that part works ok. But the mainform is asking for a questionId. I have it linked to the tbl question but it doesn’t seem to be brining the questions over for each client. What can I do?

[MMcCarthy]

Ok. I greatly appreciate the help with this. I am still having some problems. So let me explain what I have in the DB now. I have 3 tbl’s: tblClient, tblQuestion, tblAnswer. tblQuestion has a QuestionID PK and ClientID FK. TblAnswer has ClientID PK & QuestionID PK. These are all in relationships w/ enforced referential. Next I have a sbf based off tblAnswer.

I have the fields: Answer (currency) QuestoinID (not enabled).

You need the fields ClientID (Hidden or in datasheet just narrow width to nothing), QuestionID (Locked rather than Not Enabled I think), Answer (currancy)

Then I have a mainform based off tblclient. This form shows the client information at the top of the form, then has the sbf inserted in. The subform currently only shows answer box (on datasheet view). Now here is the issue: In the Mainform, when I enter a # it says “You cannot add or change a record because a related record is required in table “TblQuestion”. Also, the sbf does not allow data entry because it says I need to enter the clientAutoId. This message does not appear in the mainform, so I think that part works ok. But the mainform is asking for a questionId. I have it linked to the tbl question but it doesn’t seem to be brining the questions over for each client. What can I do?

The link Child/Master between the subform and the mainform is based on ClientID.

If you open the main form in design view and click on the frame around the subform. Open the properties and under data tab check the Link Child Fields and Link Master Fields properties. Both should be set to [ClientID].

[robertmeyer1]

Ok. I have it set up with the links, but for some reason, the tblAnswer table does not display any clientID's so it is not allowing data entry ("error" clientID cannot be null) on the mainform. The sbf says this same message. On the plus side the sbf now shows all the questions. The db is only 292KB is it possibe I could email it to you and you could look over what the issue is? I'm not I'm explaining it properly. Thanks, Once again i appreciate it.

[MMcCarthy]

Ok. I have it set up with the links, but for some reason, the tblAnswer table does not display any clientID's so it is not allowing data entry ("error" clientID cannot be null) on the mainform. The sbf says this same message. On the plus side the sbf now shows all the questions. The db is only 292KB is it possibe I could email it to you and you could look over what the issue is? I'm not I'm explaining it properly. Thanks, Once again i appreciate it.

Robert,

Send me a PM and I'll give you contact details.

Mary

[robertmeyer1]

Well it didn't take me long to come up with another question. On the database main form, is it possible to pre-populate the questions out for each client? So when you open the form, it shows the client name (top), question (already filled in) and answer (default value of $0.00). Then you just fill-in the answer and continue on to the next client. Thanks.

[robertmeyer1]

Here's Question #2

Also, I am going to be creating a new query and I needed some help with this. I am going to take the information from the Answers Table (clientID, QuestoinID, and Answer) and use them in a query. What I need to do is create a query which will combine records from all the clients for each question. So if ABC answers Question 1 with $200, and XYZ answers question 1 with $300, the query will show $500. My issue is, I am not sure how to do this in a query.

I understand how to do a query based off the actual table field, but not based off the records under a field. This is the second DB I’ve worked on, the 1st didn’t have as many questions so I was able to have each question in a field and have the field set to currency. Thanks for the help.

[MMcCarthy]

Well it didn't take me long to come up with another question. On the database main form, is it possible to pre-populate the questions out for each client? So when you open the form, it shows the client name (top), question (already filled in) and answer (default value of $0.00). Then you just fill-in the answer and continue on to the next client. Thanks.

Delete anything currently in tblAnswer and then set up a query to append the questions to the answer table. Something like ...

 
INSERT INTO tblAnswer ( ClientAutoID, QuestionNo )
SELECT tblClient.ClientAutoID, tblQuestion.QuestionNumber
FROM tblClient, tblQuestion;
 
 

[MMcCarthy]

Here's Question #2

Also, I am going to be creating a new query and I needed some help with this. I am going to take the information from the Answers Table (clientID, QuestoinID, and Answer) and use them in a query. What I need to do is create a query which will combine records from all the clients for each question. So if ABC answers Question 1 with $200, and XYZ answers question 1 with $300, the query will show $500. My issue is, I am not sure how to do this in a query.

I understand how to do a query based off the actual table field, but not based off the records under a field. This is the second DB I’ve worked on, the 1st didn’t have as many questions so I was able to have each question in a field and have the field set to currency. Thanks for the help.

Try this ...

SELECT tblAnswer.QuestionNo, Sum(tblAnswer.Answer) AS SumOfAnswer
FROM tblAnswer
GROUP BY tblAnswer.QuestionNo;
 

[robertmeyer1]

Cool. Now because I worry, Can I have unlimited records showing in a table? I inputted 4 clients into the DB then made the append query. I now have 1734 records in this table. Is there a maximum? There will be hundreds of clients having information listed here and I want to make sure they will all be able to have numbers shown. Thanks for the great help.

[MMcCarthy]

Cool. Now because I worry, Can I have unlimited records showing in a table? I inputted 4 clients into the DB then made the append query. I now have 1734 records in this table. Is there a maximum? There will be hundreds of clients having information listed here and I want to make sure they will all be able to have numbers shown. Thanks for the great help.

I thought there was only 100 questions per client. In which case you should only have 400 records. As there is only 3 fields in this table, 2 of which are indexed, you should be able to reach large record sizes comfortably.

If there are 400+ questions per client and 500 clients this will be 200,000 records which should be manageable. Assuming no more than 500 questions and 1000 clients which will reach 500,000 records you should be alright.

There is no maximum size to records in a table but the more records will increase the size of the database which does (depending on Version) have a 2 GB limit.

The solution to add the records to the table works initially assuming all clients and questions are entered first. However, this gets a lot more complicated for clients added after this which will require a different procedure and if there is any possibility of a new question being added at a later stage this gets extremely complicated.

Don't know if this helped or made it worse..:D

Mary

[robertmeyer1]

I thought there was only 100 questions per client. In which case you should only have 400 records. As there is only 3 fields in this table, 2 of which are indexed, you should be able to reach large record sizes comfortably.

If there are 400+ questions per client and 500 clients this will be 200,000 records which should be manageable. Assuming no more than 500 questions and 1000 clients which will reach 500,000 records you should be alright.

There is no maximum size to records in a table but the more records will increase the size of the database which does (depending on Version) have a 2 GB limit.

The solution to add the records to the table works initially assuming all clients and questions are entered first. However, this gets a lot more complicated for clients added after this which will require a different procedure and if there is any possibility of a new question being added at a later stage this gets extremely complicated.

Don't know if this helped or made it worse..:D

Mary

Ok. All the questions will be entered initially. So that shouldn’t be an issue. The clients I wanted to be able to enter the client information and then continue on with answering the client questions through the additional forms. There may be anywhere from 1-2500+ clients. I’m not sure what the upward limit would be, but I don’t think it will be past 2500. In order not to reach this 2GB limit, should I make it so all the client information (name, id…) is entered at one time. Then the person goes back and enters the question / answer? If this is the case, what happens if a client needs to be added at the end? (ex: you find a client you missed entering information on?) As for the question limit. There are several hundred questions per client. These questions can be grouped though. Should I follow the format I’ve been using and create new tables for these questions? (ex: TblAnswers1, TblAnswers2….). This would allow about a 100 question max, with most having less than this. Or is this a problem maker? Thanks once again for the help.

[MMcCarthy]

I would try it initially with just the tables we've previously outlined. Although this could lead to a million or more records in the Answers table it shouldn't be a problem.

Make sure each question is only entered once in the questions table with a unique identifier.

If you run into problems we can change the structure later.

You will have to write a procedure to handle new clients being added later but we can look at that when you have the structure designed and are happy with it.

Mary

[robertmeyer1]

I would try it initially with just the tables we've previously outlined. Although this could lead to a million or more records in the Answers table it shouldn't be a problem.

Make sure each question is only entered once in the questions table with a unique identifier.

If you run into problems we can change the structure later.

You will have to write a procedure to handle new clients being added later but we can look at that when you have the structure designed and are happy with it.

Mary

Sounds Good. One last thing. Is it possible to set the information being displayed in the main form to a specific question? So instead of having all questions/answers shown in a straight list. Could I break-up the list? Such as questions 1-10 are shown together. Then on a different form questions 11-20 are shown together. How can I go about setting the specific questions to display on the form? Thanks.

[MMcCarthy]

Sounds Good. One last thing. Is it possible to set the information being displayed in the main form to a specific question? So instead of having all questions/answers shown in a straight list. Could I break-up the list? Such as questions 1-10 are shown together. Then on a different form questions 11-20 are shown together. How can I go about setting the specific questions to display on the form? Thanks.

You will need to establish a control for the list box. For example set up a combo box based on a value list "[1-10]";"[11-20]"; ... etc.

Then in the after update event of the combobox you could put something like.

Dim strFilter As String
 
   strFilter = "QuestionNo IN (" & Me.ComboboxName & ")"
   Me.SubformObjectName.Form.Filter = strFilter
 
 

Mary

[robertmeyer1]

You will need to establish a control for the list box. For example set up a combo box based on a value list "[1-10]";"[11-20]"; ... etc.

Then in the after update event of the combobox you could put something like.

Dim strFilter As String
 
   strFilter = "QuestionNo IN (" & Me.ComboboxName & ")"
   Me.SubformObjectName.Form.Filter = strFilter
 
 

Mary

Could you explain this some more? I tried creating a combobox on the mainform. Then I set the control source to ClientID, changed Row Source Type to Value List, and Row Source to qryQuestionAnswer. Is this the correct Row Source? Or should it be set to tblQuestions? How do I insert the values of the questions? (ex: 1-10). Thanks.

[MMcCarthy]

Could you explain this some more? I tried creating a combobox on the mainform. Then I set the control source to ClientID, changed Row Source Type to Value List, and Row Source to qryQuestionAnswer. Is this the correct Row Source? Or should it be set to tblQuestions? How do I insert the values of the questions? (ex: 1-10). Thanks.

This combobox has no Control Source as in it's value is not being saved in the table. The Value List means that it's not based on a query just a list of values.

The Row Source is just the list of values (only) that I outlined before.

Row Source "[1-10]";"[11-20]";"[21-30]";.........."[491-500]"

[robertmeyer1]

This combobox has no Control Source as in it's value is not being saved in the table. The Value List means that it's not based on a query just a list of values.

The Row Source is just the list of values (only) that I outlined before.

Row Source "[1-10]";"[11-20]";"[21-30]";.........."[491-500]"

Ok. I have the combo box set up. I followed your instructions and I also inserted the code. I've entered data into the combo box to check the code and it is running fine. Now, when I switch through my records, the information I enter in the combo box is displayed on all the forms (regardless of client...). Also, the combo box does not display any information, and the inserted sbf still shows all the questions, regardless of the # criteria I inserted. Is there a next step to this procedure or have I made some mistakes along the way? Thanks.

[MMcCarthy]

Ok. I have the combo box set up. I followed your instructions and I also inserted the code. I've entered data into the combo box to check the code and it is running fine. Now, when I switch through my records, the information I enter in the combo box is displayed on all the forms (regardless of client...). Also, the combo box does not display any information, and the inserted sbf still shows all the questions, regardless of the # criteria I inserted. Is there a next step to this procedure or have I made some mistakes along the way? Thanks.

Check the combo box properties. Under data tab bound column should be 1 and under format tab column count should be 1 and column widths should be 2cm.

Mary

[robertmeyer1]

No luck. Let me rundown what I did and see if you can find something wrong.

On the mainform, used for data entry. I have inserted a sbf. This allows a list of questions and ability to answer. (Up to this point everything works great). Now I am trying to limit the questions on the form, to allow for easy data entry (ex: 1 form = 500 questions to 10 forms = 50 questions). You suggested the use of a combobox to accomplish this. I inserted a combobox onto the mainform, below the inserted sbf. Then I set the properties as laid out. (Value list, “[101-110]”, column count = 1, bound column 1, width is = .8” (2cm)). I also inserted the code you listed (with necessary changes):

Dim strFilter As String
strFilter = "QuestionNumber IN (" & Me.Combo36 & ")"
Me.Child24.Form.Filter = strFilter

Child24 is the name of the sbf inserted into the mainform. I have no problems running the code (i.e. enter information into combobox and it works). Now here’s what everything looks like. The sbf still shows all the same information it did originally (is not limiting the question list). The combobox is simply a big empty box (below the sbf). I can enter 1 line of information into it. This information is displayed on all records of the mainform, so it is not distinguishing between clientID’s. Am I supposed to be able to see both the sbf and combobox? One day I will finally understand this stuff, but until then, THANKS.

[MMcCarthy]

No luck. Let me rundown what I did and see if you can find something wrong.

On the mainform, used for data entry. I have inserted a sbf. This allows a list of questions and ability to answer. (Up to this point everything works great). Now I am trying to limit the questions on the form, to allow for easy data entry (ex: 1 form = 500 questions to 10 forms = 50 questions). You suggested the use of a combobox to accomplish this. I inserted a combobox onto the mainform, below the inserted sbf. Then I set the properties as laid out. (Value list, “[101-110]”, column count = 1, bound column 1, width is = .8” (2cm)). I also inserted the code you listed (with necessary changes):

Dim strFilter As String
strFilter = "QuestionNumber IN (" & Me.Combo36 & ")"
Me.Child24.Form.Filter = strFilter

Child24 is the name of the sbf inserted into the mainform. I have no problems running the code (i.e. enter information into combobox and it works). Now here’s what everything looks like. The sbf still shows all the same information it did originally (is not limiting the question list). The combobox is simply a big empty box (below the sbf). I can enter 1 line of information into it. This information is displayed on all records of the mainform, so it is not distinguishing between clientID’s. Am I supposed to be able to see both the sbf and combobox? One day I will finally understand this stuff, but until then, THANKS.

Robert can you email it to me and I'll have a look.

Mary

[robertmeyer1]

Mary,

I just sent you a copy. Thanks.

[robertmeyer1]

Mary,

That drop down list is sweet. I am planning on using that design for another DB project I am going to be starting in a few months. Thanks for showing me that was possible, it’s really cool.

For the DB I am working on now, it would really help if they were pre-done on which questions are on which form. I decided to start a new thread “Forms – Splitting Questions & Design” to see if anyone has other suggestions on it.

Thanks so much for you help with my DB. If I come up with any more questions (which is bound to happen) I’ll post them on the site. Thanks for going above & beyond.

Robert

[robertmeyer1]

Ok. I came up with a question regarding the append query. I am working on the “trial” data entry to make sure this part of the DB works before moving on. When I enter a client. Then run the append query to populate the answers, everything works great. If I then enter the answers then decide to add another client, I run into problems. After entering a new client and running the append query again, the original clients end up with 2x the entry for every question. 1 of the questions has the filled in # from before running, the second is blank and allows for fill-in. What can I do to eliminate this second number? Thanks for the help Mary.

[NeoPa]

Mary is not around just at the moment (hence the unusual lack of response).
Normal service is expected to be resumed shortly...

[MMcCarthy]

Robert,

As I said in one of my responses somewhere. The append only works once for existing clients. We need some other procedure for new clients. Something that restricts the query to the new client ID only. The following should restrict the amendment to the client currently in focus on the form. You could add a button to the form to trigger the query when new clients are added.

Mary

INSERT INTO tblAnswers ( ClientAutoID, QuestionNumber )
SELECT [tblClient].[ClientAutoID], [tblQuestions].[QuestionNumber]
FROM tblClient, tblQuestions
WHERE tblClient.[ClientAutoID]=[Forms]![frmTotalRevenueExclusions]![ClientAutoID];
 

[robertmeyer1]

Mary,

I know I’ve said this before, but once again, Thanks. This code worked great. I am now able to update a specific client. One more follow-up on it. I created a new qry and a command button on the form to run it. Is it possible to set this query so it will not append the records if a client already has records in tblAnswers? This way, if someone forgets they already ran the query, they can’t accidentally click it again. “Thanks

[robertmeyer1]

Also, I'm testing this code, and it makes you close the form first before it will allow the append. So I have to enter a new client, then close the form, then go back to the correct client and click the button to make it work. If I click it after entering information originally, it doesn't do anything. Any thoughts?

[MMcCarthy]

In the click event of the new button add the following code around the query run command.

Mary

If nz(DLookup("[ClientAutoID]","tblAnswers","[ClientAutoID]=" & Me.ClientAutoID),0) = 0 Then
   DoCmd.RunQuery "QueryName"
End If
 

[robertmeyer1]

Nice. Thanks for the help. That code worked great and now it no longer updates multiple times. I think I'm good on this issue, If I come up with any more questions I'll let you know.

[robertmeyer1]

Sigh. Another Issue.

I am trying to start working on some queries. I have a few queries which take have the format:

ClientID ; QuestionNo; Answer (all based off tblAnswers).

I’ve used QuestionNo to limit the answers through criteria (i.e. Between 201 And 249).


What I am trying to do now is take this concept and add some of the answers. For example: Set up a query (need help on) and select all quesionNo 204-210. Then add.

Any thoughts on how to do this? Thanks.

[robertmeyer1]

Sigh. Another Issue.

I am trying to start working on some queries. I have a few queries which take have the format:

ClientID ; QuestionNo; Answer (all based off tblAnswers).

I’ve used QuestionNo to limit the answers through criteria (i.e. Between 201 And 249).


What I am trying to do now is take this concept and add some of the answers. For example: Set up a query (need help on) and select all quesionNo 204-210. Then add.

Any thoughts on how to do this? Thanks.

This thread is closed, please see others. Thanks Mary.