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 |
#11
|
|||
|
|||
Questionnaire
Now we're getting somewhere. I will suggest a table structure. The table
name is listed first, with the fields below it and indented. One thing that is not clear is whether this program is just for the children with the allergies, or if it is for the parents or guardians also. For now I will assume the former. tblApplication ApplicantID (primary key, or PK) AppFirstName AppLastName AppStreet etc. tblChildren ChildID (PK) ApplicantID (foreign key, or FK) ChFirstName ChLastName etc. I will leave the out for the moment the Questions part of the process. Build these two tables. You define the PK in table design view. We'll get to the FK in a moment. For the PK you can use autonumber or any other number (or text) that is guaranteed to be unique. Access won't let you have a repeating PK value. Click Tools Relationships. Add both tables, then drag ApplicantID from one table and drop it on ApplicantID in the other table. In the dialog box that appears, click Enforce Referential Integrity. Build a form (frmApplicant) based on tblApplicant, and another form (fsubChild) based on tblChild. In form design view, drag the icon for fsubChild onto frmApplicant. Now you can enter an applicant's information, and separate records for each child. Format for both forms will probably be single view (ont eh form's property sheet - View Properties. Experiment with a few sample records. Now for the questions. If there aren't too many of them (less than 30, say, for each child) then they could be part of tblChild. Same for the applicant questions, which could be part of tblApplicant. Alternatively, questions could be in their own table that is related one-to-one with tblChild (see Help for more on one-to-one relationships). Same for applicant questions, which would probably be in their own table (if you are putting them into a separate table from tblApplicant, that is), as I assume they would be rather different than the Child questions. Spaces for staff comments could be in tblApplicant, or in a table related one-to-one. Remember that on the form (where you will be entering and editing information) and on the report (for printing information) you can arrange the fields any way you want. You are not limited to grids. Two fields that are side-by-side on the table could be on opposite corners of the form. See if this helps to get you started. "Flipper" wrote in message ... Ok. WE have a program that helps low/moderate income families with asthma/allergies. the fill out an app and we see if they qualify. The app asks Renter info and homeowner info (name, address, phone) (for each, if applicable). Then, there is a children section for each child. It asks name, dob,sex,race. It asks "your relationship to child", and anme and phone number of legal guardian/parent of child. Then there are a series of questions for that child, with the answer of yes, no, or don't know. (This repeats for each child). then there are 13 questions in another section of the app for the applicant (usualy the parent). Then there are small questions in part 4 that asks income and then for staff use only there are more questions; date app rcvd, date applicant contacted, inspection/assesment?, basic intervention? , custom intvnt? "BruceM" wrote: Sorry, no. Why don't you start by describing the situation? There is an application. Who is applying, and for what? Why are children listed on the application? What sorts of questions? If a civic group is submitting applications for children to attend summer camp, you might say something like: "A civic group submits applications for children to attend summer camp. There could be just one child, or as many as twenty. Each child is listed separately. Each child's listing includes Name, DOB and other personal information. A series of questions needs to be answered about each child." In describing the situation, you do not need a lot of detail. "Name, DOB and other personal information" is plenty to describe the Children table. By the way, I need to modify something I said before. The relationship to the Questions table would probably be one-to-one, but to what other table I am not sure. "Flipper" wrote in message ... Is there any way I can send you my Word document of the form i am basing my DB on and a copy of my db? "BruceM" wrote: Relationships exist between tables, not forms. Nested subforms are fine, but are not going to create relationships. The relationships come first, at the table level. That's why I asked about your tables, but maybe it's best to back up a step first. What real-world situation is your database intended to address. It sounds as if maybe you want to have an application form, and a subform on which children are listed, and another subform for information about each child. In that case you would have an Application table related one-to-many to a Children table, which is in turn related one-to-many with a Questions table. In terms of forms and subforms, the Questions subform would be nested in the Children form, which in turn is nested in the Application form. Of course, my understanding may be completely inaccurate. In any case, there are a lot of unresolved details in my quick explanation. "Flipper" wrote in message news Well, I hope my relationships are set up right...But it could be wrong... What I mean by subforms is that I have them nested. Basically, I have a main form called Applications. It has tab controls on that main form. Each tab control has a subform on it. One the Subform called Children (which has infor about each kid such as name, dob, gender, ethnic). I then put the Questions form on the children subform. (nested it). But all I get is blank fields when I do. The form on its own, not as a subform works fine. It asks a question and i have a box for the answers. I don't know how to get it in my main form and have it relate to each child. "BruceM" wrote: Your meaning is unclear, particularly "when I create a subform and make it in the subform". I know you said that your relationships are set up properly, but something isn't working, so more information is needed. A description of the table structure (not every field, just the primary keys, other fields involved in relationships, and a general description of the table's purpose) would help. "Flipper" wrote in message news I have tables set up for my questionnaire and responses. I made the questionnaire into a form and it lists all the questions. When I create a subform and make it in the subform, it doesnt list the questions. It just lists blank fields. What am I doing wrong? |
#12
|
|||
|
|||
Questionnaire
Well, I already started and would prefer not to start over, if I don't have
to. Do you have an email that I can send my db to and the word document of the paper form I am creating most of my db around? "BruceM" wrote: Now we're getting somewhere. I will suggest a table structure. The table name is listed first, with the fields below it and indented. One thing that is not clear is whether this program is just for the children with the allergies, or if it is for the parents or guardians also. For now I will assume the former. tblApplication ApplicantID (primary key, or PK) AppFirstName AppLastName AppStreet etc. tblChildren ChildID (PK) ApplicantID (foreign key, or FK) ChFirstName ChLastName etc. I will leave the out for the moment the Questions part of the process. Build these two tables. You define the PK in table design view. We'll get to the FK in a moment. For the PK you can use autonumber or any other number (or text) that is guaranteed to be unique. Access won't let you have a repeating PK value. Click Tools Relationships. Add both tables, then drag ApplicantID from one table and drop it on ApplicantID in the other table. In the dialog box that appears, click Enforce Referential Integrity. Build a form (frmApplicant) based on tblApplicant, and another form (fsubChild) based on tblChild. In form design view, drag the icon for fsubChild onto frmApplicant. Now you can enter an applicant's information, and separate records for each child. Format for both forms will probably be single view (ont eh form's property sheet - View Properties. Experiment with a few sample records. Now for the questions. If there aren't too many of them (less than 30, say, for each child) then they could be part of tblChild. Same for the applicant questions, which could be part of tblApplicant. Alternatively, questions could be in their own table that is related one-to-one with tblChild (see Help for more on one-to-one relationships). Same for applicant questions, which would probably be in their own table (if you are putting them into a separate table from tblApplicant, that is), as I assume they would be rather different than the Child questions. Spaces for staff comments could be in tblApplicant, or in a table related one-to-one. Remember that on the form (where you will be entering and editing information) and on the report (for printing information) you can arrange the fields any way you want. You are not limited to grids. Two fields that are side-by-side on the table could be on opposite corners of the form. See if this helps to get you started. "Flipper" wrote in message ... Ok. WE have a program that helps low/moderate income families with asthma/allergies. the fill out an app and we see if they qualify. The app asks Renter info and homeowner info (name, address, phone) (for each, if applicable). Then, there is a children section for each child. It asks name, dob,sex,race. It asks "your relationship to child", and anme and phone number of legal guardian/parent of child. Then there are a series of questions for that child, with the answer of yes, no, or don't know. (This repeats for each child). then there are 13 questions in another section of the app for the applicant (usualy the parent). Then there are small questions in part 4 that asks income and then for staff use only there are more questions; date app rcvd, date applicant contacted, inspection/assesment?, basic intervention? , custom intvnt? "BruceM" wrote: Sorry, no. Why don't you start by describing the situation? There is an application. Who is applying, and for what? Why are children listed on the application? What sorts of questions? If a civic group is submitting applications for children to attend summer camp, you might say something like: "A civic group submits applications for children to attend summer camp. There could be just one child, or as many as twenty. Each child is listed separately. Each child's listing includes Name, DOB and other personal information. A series of questions needs to be answered about each child." In describing the situation, you do not need a lot of detail. "Name, DOB and other personal information" is plenty to describe the Children table. By the way, I need to modify something I said before. The relationship to the Questions table would probably be one-to-one, but to what other table I am not sure. "Flipper" wrote in message ... Is there any way I can send you my Word document of the form i am basing my DB on and a copy of my db? "BruceM" wrote: Relationships exist between tables, not forms. Nested subforms are fine, but are not going to create relationships. The relationships come first, at the table level. That's why I asked about your tables, but maybe it's best to back up a step first. What real-world situation is your database intended to address. It sounds as if maybe you want to have an application form, and a subform on which children are listed, and another subform for information about each child. In that case you would have an Application table related one-to-many to a Children table, which is in turn related one-to-many with a Questions table. In terms of forms and subforms, the Questions subform would be nested in the Children form, which in turn is nested in the Application form. Of course, my understanding may be completely inaccurate. In any case, there are a lot of unresolved details in my quick explanation. "Flipper" wrote in message news Well, I hope my relationships are set up right...But it could be wrong... What I mean by subforms is that I have them nested. Basically, I have a main form called Applications. It has tab controls on that main form. Each tab control has a subform on it. One the Subform called Children (which has infor about each kid such as name, dob, gender, ethnic). I then put the Questions form on the children subform. (nested it). But all I get is blank fields when I do. The form on its own, not as a subform works fine. It asks a question and i have a box for the answers. I don't know how to get it in my main form and have it relate to each child. "BruceM" wrote: Your meaning is unclear, particularly "when I create a subform and make it in the subform". I know you said that your relationships are set up properly, but something isn't working, so more information is needed. A description of the table structure (not every field, just the primary keys, other fields involved in relationships, and a general description of the table's purpose) would help. "Flipper" wrote in message news I have tables set up for my questionnaire and responses. I made the questionnaire into a form and it lists all the questions. When I create a subform and make it in the subform, it doesnt list the questions. It just lists blank fields. What am I doing wrong? |
#13
|
|||
|
|||
Questionnaire
You will probably spend more time trying to force an ineffective design to
meet your needs than you will starting over. You need a minimum of two tables, set up along the lines I have suggested. Post your table design in similar format to what I have done. Describe the relationships. Maybe I misunderstand the situation, and what you have is workable. Regarding sending the files to me: I have put some time into responding, and have told you what specific information would help me (or somebody else) provide a targeted answer. You can probably decipher the obfuscated e-mail address in the message header, but unless you are willing to at least attempt to provide information in this forum I will tell you frankly that I will not make any promises about reviewing the files. "Flipper" wrote in message ... Well, I already started and would prefer not to start over, if I don't have to. Do you have an email that I can send my db to and the word document of the paper form I am creating most of my db around? "BruceM" wrote: Now we're getting somewhere. I will suggest a table structure. The table name is listed first, with the fields below it and indented. One thing that is not clear is whether this program is just for the children with the allergies, or if it is for the parents or guardians also. For now I will assume the former. tblApplication ApplicantID (primary key, or PK) AppFirstName AppLastName AppStreet etc. tblChildren ChildID (PK) ApplicantID (foreign key, or FK) ChFirstName ChLastName etc. I will leave the out for the moment the Questions part of the process. Build these two tables. You define the PK in table design view. We'll get to the FK in a moment. For the PK you can use autonumber or any other number (or text) that is guaranteed to be unique. Access won't let you have a repeating PK value. Click Tools Relationships. Add both tables, then drag ApplicantID from one table and drop it on ApplicantID in the other table. In the dialog box that appears, click Enforce Referential Integrity. Build a form (frmApplicant) based on tblApplicant, and another form (fsubChild) based on tblChild. In form design view, drag the icon for fsubChild onto frmApplicant. Now you can enter an applicant's information, and separate records for each child. Format for both forms will probably be single view (ont eh form's property sheet - View Properties. Experiment with a few sample records. Now for the questions. If there aren't too many of them (less than 30, say, for each child) then they could be part of tblChild. Same for the applicant questions, which could be part of tblApplicant. Alternatively, questions could be in their own table that is related one-to-one with tblChild (see Help for more on one-to-one relationships). Same for applicant questions, which would probably be in their own table (if you are putting them into a separate table from tblApplicant, that is), as I assume they would be rather different than the Child questions. Spaces for staff comments could be in tblApplicant, or in a table related one-to-one. Remember that on the form (where you will be entering and editing information) and on the report (for printing information) you can arrange the fields any way you want. You are not limited to grids. Two fields that are side-by-side on the table could be on opposite corners of the form. See if this helps to get you started. "Flipper" wrote in message ... Ok. WE have a program that helps low/moderate income families with asthma/allergies. the fill out an app and we see if they qualify. The app asks Renter info and homeowner info (name, address, phone) (for each, if applicable). Then, there is a children section for each child. It asks name, dob,sex,race. It asks "your relationship to child", and anme and phone number of legal guardian/parent of child. Then there are a series of questions for that child, with the answer of yes, no, or don't know. (This repeats for each child). then there are 13 questions in another section of the app for the applicant (usualy the parent). Then there are small questions in part 4 that asks income and then for staff use only there are more questions; date app rcvd, date applicant contacted, inspection/assesment?, basic intervention? , custom intvnt? "BruceM" wrote: Sorry, no. Why don't you start by describing the situation? There is an application. Who is applying, and for what? Why are children listed on the application? What sorts of questions? If a civic group is submitting applications for children to attend summer camp, you might say something like: "A civic group submits applications for children to attend summer camp. There could be just one child, or as many as twenty. Each child is listed separately. Each child's listing includes Name, DOB and other personal information. A series of questions needs to be answered about each child." In describing the situation, you do not need a lot of detail. "Name, DOB and other personal information" is plenty to describe the Children table. By the way, I need to modify something I said before. The relationship to the Questions table would probably be one-to-one, but to what other table I am not sure. "Flipper" wrote in message ... Is there any way I can send you my Word document of the form i am basing my DB on and a copy of my db? "BruceM" wrote: Relationships exist between tables, not forms. Nested subforms are fine, but are not going to create relationships. The relationships come first, at the table level. That's why I asked about your tables, but maybe it's best to back up a step first. What real-world situation is your database intended to address. It sounds as if maybe you want to have an application form, and a subform on which children are listed, and another subform for information about each child. In that case you would have an Application table related one-to-many to a Children table, which is in turn related one-to-many with a Questions table. In terms of forms and subforms, the Questions subform would be nested in the Children form, which in turn is nested in the Application form. Of course, my understanding may be completely inaccurate. In any case, there are a lot of unresolved details in my quick explanation. "Flipper" wrote in message news Well, I hope my relationships are set up right...But it could be wrong... What I mean by subforms is that I have them nested. Basically, I have a main form called Applications. It has tab controls on that main form. Each tab control has a subform on it. One the Subform called Children (which has infor about each kid such as name, dob, gender, ethnic). I then put the Questions form on the children subform. (nested it). But all I get is blank fields when I do. The form on its own, not as a subform works fine. It asks a question and i have a box for the answers. I don't know how to get it in my main form and have it relate to each child. "BruceM" wrote: Your meaning is unclear, particularly "when I create a subform and make it in the subform". I know you said that your relationships are set up properly, but something isn't working, so more information is needed. A description of the table structure (not every field, just the primary keys, other fields involved in relationships, and a general description of the table's purpose) would help. "Flipper" wrote in message news I have tables set up for my questionnaire and responses. I made the questionnaire into a form and it lists all the questions. When I create a subform and make it in the subform, it doesnt list the questions. It just lists blank fields. What am I doing wrong? |
#14
|
|||
|
|||
Questionnaire
My tblApplications is as such:
ApplicationID PeopleID Relationship to Children Application Date Application Score contactdate BaselineDate My tblChildren is: ChildID ApplicationID ProjectNumber FirstName Last DOB GEnder Ethnicity tblPeople PeopleID REsidentStatus ApplicantRole FirstName LastName tblAddresses AddressID PeopleID StreetADd City State Zip tblePhones PhoneID PeopleID PhoneTypeID PhoneNUmber tluPHoneTypes PhoneTypeID PHoneType I have tons of other tables too for all the questions. There are two parts sections of questions on the application. One is related to each child on the application. The other section of questions is related to the application and asks more household questions or about ALL the children, so to speak. Did you want to see how those tables are set up also? "BruceM" wrote: You will probably spend more time trying to force an ineffective design to meet your needs than you will starting over. You need a minimum of two tables, set up along the lines I have suggested. Post your table design in similar format to what I have done. Describe the relationships. Maybe I misunderstand the situation, and what you have is workable. Regarding sending the files to me: I have put some time into responding, and have told you what specific information would help me (or somebody else) provide a targeted answer. You can probably decipher the obfuscated e-mail address in the message header, but unless you are willing to at least attempt to provide information in this forum I will tell you frankly that I will not make any promises about reviewing the files. "Flipper" wrote in message ... Well, I already started and would prefer not to start over, if I don't have to. Do you have an email that I can send my db to and the word document of the paper form I am creating most of my db around? "BruceM" wrote: Now we're getting somewhere. I will suggest a table structure. The table name is listed first, with the fields below it and indented. One thing that is not clear is whether this program is just for the children with the allergies, or if it is for the parents or guardians also. For now I will assume the former. tblApplication ApplicantID (primary key, or PK) AppFirstName AppLastName AppStreet etc. tblChildren ChildID (PK) ApplicantID (foreign key, or FK) ChFirstName ChLastName etc. I will leave the out for the moment the Questions part of the process. Build these two tables. You define the PK in table design view. We'll get to the FK in a moment. For the PK you can use autonumber or any other number (or text) that is guaranteed to be unique. Access won't let you have a repeating PK value. Click Tools Relationships. Add both tables, then drag ApplicantID from one table and drop it on ApplicantID in the other table. In the dialog box that appears, click Enforce Referential Integrity. Build a form (frmApplicant) based on tblApplicant, and another form (fsubChild) based on tblChild. In form design view, drag the icon for fsubChild onto frmApplicant. Now you can enter an applicant's information, and separate records for each child. Format for both forms will probably be single view (ont eh form's property sheet - View Properties. Experiment with a few sample records. Now for the questions. If there aren't too many of them (less than 30, say, for each child) then they could be part of tblChild. Same for the applicant questions, which could be part of tblApplicant. Alternatively, questions could be in their own table that is related one-to-one with tblChild (see Help for more on one-to-one relationships). Same for applicant questions, which would probably be in their own table (if you are putting them into a separate table from tblApplicant, that is), as I assume they would be rather different than the Child questions. Spaces for staff comments could be in tblApplicant, or in a table related one-to-one. Remember that on the form (where you will be entering and editing information) and on the report (for printing information) you can arrange the fields any way you want. You are not limited to grids. Two fields that are side-by-side on the table could be on opposite corners of the form. See if this helps to get you started. "Flipper" wrote in message ... Ok. WE have a program that helps low/moderate income families with asthma/allergies. the fill out an app and we see if they qualify. The app asks Renter info and homeowner info (name, address, phone) (for each, if applicable). Then, there is a children section for each child. It asks name, dob,sex,race. It asks "your relationship to child", and anme and phone number of legal guardian/parent of child. Then there are a series of questions for that child, with the answer of yes, no, or don't know. (This repeats for each child). then there are 13 questions in another section of the app for the applicant (usualy the parent). Then there are small questions in part 4 that asks income and then for staff use only there are more questions; date app rcvd, date applicant contacted, inspection/assesment?, basic intervention? , custom intvnt? "BruceM" wrote: Sorry, no. Why don't you start by describing the situation? There is an application. Who is applying, and for what? Why are children listed on the application? What sorts of questions? If a civic group is submitting applications for children to attend summer camp, you might say something like: "A civic group submits applications for children to attend summer camp. There could be just one child, or as many as twenty. Each child is listed separately. Each child's listing includes Name, DOB and other personal information. A series of questions needs to be answered about each child." In describing the situation, you do not need a lot of detail. "Name, DOB and other personal information" is plenty to describe the Children table. By the way, I need to modify something I said before. The relationship to the Questions table would probably be one-to-one, but to what other table I am not sure. "Flipper" wrote in message ... Is there any way I can send you my Word document of the form i am basing my DB on and a copy of my db? "BruceM" wrote: Relationships exist between tables, not forms. Nested subforms are fine, but are not going to create relationships. The relationships come first, at the table level. That's why I asked about your tables, but maybe it's best to back up a step first. What real-world situation is your database intended to address. It sounds as if maybe you want to have an application form, and a subform on which children are listed, and another subform for information about each child. In that case you would have an Application table related one-to-many to a Children table, which is in turn related one-to-many with a Questions table. In terms of forms and subforms, the Questions subform would be nested in the Children form, which in turn is nested in the Application form. Of course, my understanding may be completely inaccurate. In any case, there are a lot of unresolved details in my quick explanation. "Flipper" wrote in message news Well, I hope my relationships are set up right...But it could be wrong... What I mean by subforms is that I have them nested. Basically, I have a main form called Applications. It has tab controls on that main form. Each tab control has a subform on it. One the Subform called Children (which has infor about each kid such as name, dob, gender, ethnic). I then put the Questions form on the children subform. (nested it). But all I get is blank fields when I do. The form on its own, not as a subform works fine. It asks a question and i have a box for the answers. I don't know how to get it in my main form and have it relate to each child. "BruceM" wrote: Your meaning is unclear, particularly "when I create a subform and make it in the subform". I know you said that your relationships are set up properly, but something isn't working, so more information is needed. A description of the table structure (not every field, just the primary keys, other fields involved in relationships, and a general description of the table's purpose) would help. "Flipper" wrote in message news I have tables set up for my questionnaire and responses. I made the questionnaire into a form and it lists all the questions. When I create a subform and make it in the subform, it doesnt list the questions. It just lists blank fields. What am I doing wrong? |
#15
|
|||
|
|||
Questionnaire
No wonder you don't want to change it. You went to a lot of trouble putting
together all of those tables. Here'a a pretty concise way of looking at tables: A table should contain information about a single real-world entity (application, child, etc.). You should be able to describe its purpose in a single sentence without using the word "and". Having said that, you can have first name and last name, or name and address, and things like that in the same table. Items such as FirstName, LastName, Street, City, Zip, Phone and so forth are all parts of a person's contact information, so they could all be in the same record in the People table (or whatever you would like to call it). A main reason for related tables is to avoid duplicating information. Consider Social Security numbers. That number identifies you for tax and benefits purposes, no matter if your name and address changes. If your name and address were part of the record for every tax or benefit transaction, then old records would contain information that may no longer be current. There is no real reason to have a separate address table. In most cases you will want that information in each person's record. If two people are at the same address and one of them moves, what would you do if you had linked both to the same address record? However, you may choose to have a Parent table with name and address information. Children would be in a related table, but the address would appear only once (in the Parent table). The whole issue of parents and children can get a bit murky, since children grow up and may become parents themselves. In that case, and in other cases as well, you may choose to put all people into one table, and to use check boxes or some such to separate them into categories. I will ignore that, since it sounds as if you aren't dealing with that situation. What I said about addresses applies to phone numbers and other personal information. Could the same person be an Applicant more than once? If so, you need to store Applicant information in its own table. In that case you would have an Application table, an Applicant table, and a Child table. tblApplication ApplicationID (PK) AppDate Reason, comments, and other information about that specific application tblApplicant ApplicantID (PK) ApplicationID (FK) AppFirstName other personal information tblChild ChildID (PK) ApplicantID (FK) ChFirstName and other information specific to the child Create relationships between the PK fields and their namesake FK fields. Click Enforce Referential Integrity for all. Build an Application form, and subforms for Applicant and Children as I described. Don't worry about data for now. Just create the tables and forms, insert the subforms as described, and try adding a few sample records. Get used to how this can work. For handling the questions, see my remarks in the previous post. For now, don't worrry about that. Make some related records, and get that part of it working. If there is a reason you cannot change your design, about all I can suggest is to see Help for information about one-to-one relationships. Apply that relationship between the address table and the People table, etc., and get ready for an administrative nightmare. "Flipper" wrote in message ... My tblApplications is as such: ApplicationID PeopleID Relationship to Children Application Date Application Score contactdate BaselineDate My tblChildren is: ChildID ApplicationID ProjectNumber FirstName Last DOB GEnder Ethnicity tblPeople PeopleID REsidentStatus ApplicantRole FirstName LastName tblAddresses AddressID PeopleID StreetADd City State Zip tblePhones PhoneID PeopleID PhoneTypeID PhoneNUmber tluPHoneTypes PhoneTypeID PHoneType I have tons of other tables too for all the questions. There are two parts sections of questions on the application. One is related to each child on the application. The other section of questions is related to the application and asks more household questions or about ALL the children, so to speak. Did you want to see how those tables are set up also? "BruceM" wrote: You will probably spend more time trying to force an ineffective design to meet your needs than you will starting over. You need a minimum of two tables, set up along the lines I have suggested. Post your table design in similar format to what I have done. Describe the relationships. Maybe I misunderstand the situation, and what you have is workable. Regarding sending the files to me: I have put some time into responding, and have told you what specific information would help me (or somebody else) provide a targeted answer. You can probably decipher the obfuscated e-mail address in the message header, but unless you are willing to at least attempt to provide information in this forum I will tell you frankly that I will not make any promises about reviewing the files. "Flipper" wrote in message ... Well, I already started and would prefer not to start over, if I don't have to. Do you have an email that I can send my db to and the word document of the paper form I am creating most of my db around? "BruceM" wrote: Now we're getting somewhere. I will suggest a table structure. The table name is listed first, with the fields below it and indented. One thing that is not clear is whether this program is just for the children with the allergies, or if it is for the parents or guardians also. For now I will assume the former. tblApplication ApplicantID (primary key, or PK) AppFirstName AppLastName AppStreet etc. tblChildren ChildID (PK) ApplicantID (foreign key, or FK) ChFirstName ChLastName etc. I will leave the out for the moment the Questions part of the process. Build these two tables. You define the PK in table design view. We'll get to the FK in a moment. For the PK you can use autonumber or any other number (or text) that is guaranteed to be unique. Access won't let you have a repeating PK value. Click Tools Relationships. Add both tables, then drag ApplicantID from one table and drop it on ApplicantID in the other table. In the dialog box that appears, click Enforce Referential Integrity. Build a form (frmApplicant) based on tblApplicant, and another form (fsubChild) based on tblChild. In form design view, drag the icon for fsubChild onto frmApplicant. Now you can enter an applicant's information, and separate records for each child. Format for both forms will probably be single view (ont eh form's property sheet - View Properties. Experiment with a few sample records. Now for the questions. If there aren't too many of them (less than 30, say, for each child) then they could be part of tblChild. Same for the applicant questions, which could be part of tblApplicant. Alternatively, questions could be in their own table that is related one-to-one with tblChild (see Help for more on one-to-one relationships). Same for applicant questions, which would probably be in their own table (if you are putting them into a separate table from tblApplicant, that is), as I assume they would be rather different than the Child questions. Spaces for staff comments could be in tblApplicant, or in a table related one-to-one. Remember that on the form (where you will be entering and editing information) and on the report (for printing information) you can arrange the fields any way you want. You are not limited to grids. Two fields that are side-by-side on the table could be on opposite corners of the form. See if this helps to get you started. "Flipper" wrote in message ... Ok. WE have a program that helps low/moderate income families with asthma/allergies. the fill out an app and we see if they qualify. The app asks Renter info and homeowner info (name, address, phone) (for each, if applicable). Then, there is a children section for each child. It asks name, dob,sex,race. It asks "your relationship to child", and anme and phone number of legal guardian/parent of child. Then there are a series of questions for that child, with the answer of yes, no, or don't know. (This repeats for each child). then there are 13 questions in another section of the app for the applicant (usualy the parent). Then there are small questions in part 4 that asks income and then for staff use only there are more questions; date app rcvd, date applicant contacted, inspection/assesment?, basic intervention? , custom intvnt? "BruceM" wrote: Sorry, no. Why don't you start by describing the situation? There is an application. Who is applying, and for what? Why are children listed on the application? What sorts of questions? If a civic group is submitting applications for children to attend summer camp, you might say something like: "A civic group submits applications for children to attend summer camp. There could be just one child, or as many as twenty. Each child is listed separately. Each child's listing includes Name, DOB and other personal information. A series of questions needs to be answered about each child." In describing the situation, you do not need a lot of detail. "Name, DOB and other personal information" is plenty to describe the Children table. By the way, I need to modify something I said before. The relationship to the Questions table would probably be one-to-one, but to what other table I am not sure. "Flipper" wrote in message ... Is there any way I can send you my Word document of the form i am basing my DB on and a copy of my db? "BruceM" wrote: Relationships exist between tables, not forms. Nested subforms are fine, but are not going to create relationships. The relationships come first, at the table level. That's why I asked about your tables, but maybe it's best to back up a step first. What real-world situation is your database intended to address. It sounds as if maybe you want to have an application form, and a subform on which children are listed, and another subform for information about each child. In that case you would have an Application table related one-to-many to a Children table, which is in turn related one-to-many with a Questions table. In terms of forms and subforms, the Questions subform would be nested in the Children form, which in turn is nested in the Application form. Of course, my understanding may be completely inaccurate. In any case, there are a lot of unresolved details in my quick explanation. "Flipper" wrote in message news Well, I hope my relationships are set up right...But it could be wrong... What I mean by subforms is that I have them nested. Basically, I have a main form called Applications. It has tab controls on that main form. Each tab control has a subform on it. One the Subform called Children (which has infor about each kid such as name, dob, gender, ethnic). I then put the Questions form on the children subform. (nested it). But all I get is blank fields when I do. The form on its own, not as a subform works fine. It asks a question and i have a box for the answers. I don't know how to get it in my main form and have it relate to each child. "BruceM" wrote: Your meaning is unclear, particularly "when I create a subform and make it in the subform". I know you said that your relationships are set up properly, but something isn't working, so more information is needed. A description of the table structure (not every field, just the primary keys, other fields involved in relationships, and a general description of the table's purpose) would help. "Flipper" wrote in message news I have tables set up for my questionnaire and responses. I made the questionnaire into a form and it lists all the questions. When I create a subform and make it in the subform, it doesnt list the questions. It just lists blank fields. What am I doing wrong? |
#16
|
|||
|
|||
Questionnaire
Yes, I have already made all of my tables and established one to many
relationships. I have already created my main form with subforms. I am just trying to figure out the one section on the paper application, which is a series of questions. I have the answer key to the questions, and just need to get the responses on there for each question. Each question will have a yes/no/don't know answer, but then a couple have a "if yes, then what source?" (or somethi9ng like that). Then there are like 6 choices and then "other" as the last choice. then I have another table for "other". "BruceM" wrote: No wonder you don't want to change it. You went to a lot of trouble putting together all of those tables. Here'a a pretty concise way of looking at tables: A table should contain information about a single real-world entity (application, child, etc.). You should be able to describe its purpose in a single sentence without using the word "and". Having said that, you can have first name and last name, or name and address, and things like that in the same table. Items such as FirstName, LastName, Street, City, Zip, Phone and so forth are all parts of a person's contact information, so they could all be in the same record in the People table (or whatever you would like to call it). A main reason for related tables is to avoid duplicating information. Consider Social Security numbers. That number identifies you for tax and benefits purposes, no matter if your name and address changes. If your name and address were part of the record for every tax or benefit transaction, then old records would contain information that may no longer be current. There is no real reason to have a separate address table. In most cases you will want that information in each person's record. If two people are at the same address and one of them moves, what would you do if you had linked both to the same address record? However, you may choose to have a Parent table with name and address information. Children would be in a related table, but the address would appear only once (in the Parent table). The whole issue of parents and children can get a bit murky, since children grow up and may become parents themselves. In that case, and in other cases as well, you may choose to put all people into one table, and to use check boxes or some such to separate them into categories. I will ignore that, since it sounds as if you aren't dealing with that situation. What I said about addresses applies to phone numbers and other personal information. Could the same person be an Applicant more than once? If so, you need to store Applicant information in its own table. In that case you would have an Application table, an Applicant table, and a Child table. tblApplication ApplicationID (PK) AppDate Reason, comments, and other information about that specific application tblApplicant ApplicantID (PK) ApplicationID (FK) AppFirstName other personal information tblChild ChildID (PK) ApplicantID (FK) ChFirstName and other information specific to the child Create relationships between the PK fields and their namesake FK fields. Click Enforce Referential Integrity for all. Build an Application form, and subforms for Applicant and Children as I described. Don't worry about data for now. Just create the tables and forms, insert the subforms as described, and try adding a few sample records. Get used to how this can work. For handling the questions, see my remarks in the previous post. For now, don't worrry about that. Make some related records, and get that part of it working. If there is a reason you cannot change your design, about all I can suggest is to see Help for information about one-to-one relationships. Apply that relationship between the address table and the People table, etc., and get ready for an administrative nightmare. "Flipper" wrote in message ... My tblApplications is as such: ApplicationID PeopleID Relationship to Children Application Date Application Score contactdate BaselineDate My tblChildren is: ChildID ApplicationID ProjectNumber FirstName Last DOB GEnder Ethnicity tblPeople PeopleID REsidentStatus ApplicantRole FirstName LastName tblAddresses AddressID PeopleID StreetADd City State Zip tblePhones PhoneID PeopleID PhoneTypeID PhoneNUmber tluPHoneTypes PhoneTypeID PHoneType I have tons of other tables too for all the questions. There are two parts sections of questions on the application. One is related to each child on the application. The other section of questions is related to the application and asks more household questions or about ALL the children, so to speak. Did you want to see how those tables are set up also? "BruceM" wrote: You will probably spend more time trying to force an ineffective design to meet your needs than you will starting over. You need a minimum of two tables, set up along the lines I have suggested. Post your table design in similar format to what I have done. Describe the relationships. Maybe I misunderstand the situation, and what you have is workable. Regarding sending the files to me: I have put some time into responding, and have told you what specific information would help me (or somebody else) provide a targeted answer. You can probably decipher the obfuscated e-mail address in the message header, but unless you are willing to at least attempt to provide information in this forum I will tell you frankly that I will not make any promises about reviewing the files. "Flipper" wrote in message ... Well, I already started and would prefer not to start over, if I don't have to. Do you have an email that I can send my db to and the word document of the paper form I am creating most of my db around? "BruceM" wrote: Now we're getting somewhere. I will suggest a table structure. The table name is listed first, with the fields below it and indented. One thing that is not clear is whether this program is just for the children with the allergies, or if it is for the parents or guardians also. For now I will assume the former. tblApplication ApplicantID (primary key, or PK) AppFirstName AppLastName AppStreet etc. tblChildren ChildID (PK) ApplicantID (foreign key, or FK) ChFirstName ChLastName etc. I will leave the out for the moment the Questions part of the process. Build these two tables. You define the PK in table design view. We'll get to the FK in a moment. For the PK you can use autonumber or any other number (or text) that is guaranteed to be unique. Access won't let you have a repeating PK value. Click Tools Relationships. Add both tables, then drag ApplicantID from one table and drop it on ApplicantID in the other table. In the dialog box that appears, click Enforce Referential Integrity. Build a form (frmApplicant) based on tblApplicant, and another form (fsubChild) based on tblChild. In form design view, drag the icon for fsubChild onto frmApplicant. Now you can enter an applicant's information, and separate records for each child. Format for both forms will probably be single view (ont eh form's property sheet - View Properties. Experiment with a few sample records. Now for the questions. If there aren't too many of them (less than 30, say, for each child) then they could be part of tblChild. Same for the applicant questions, which could be part of tblApplicant. Alternatively, questions could be in their own table that is related one-to-one with tblChild (see Help for more on one-to-one relationships). Same for applicant questions, which would probably be in their own table (if you are putting them into a separate table from tblApplicant, that is), as I assume they would be rather different than the Child questions. Spaces for staff comments could be in tblApplicant, or in a table related one-to-one. Remember that on the form (where you will be entering and editing information) and on the report (for printing information) you can arrange the fields any way you want. You are not limited to grids. Two fields that are side-by-side on the table could be on opposite corners of the form. See if this helps to get you started. "Flipper" wrote in message ... Ok. WE have a program that helps low/moderate income families with asthma/allergies. the fill out an app and we see if they qualify. The app asks Renter info and homeowner info (name, address, phone) (for each, if applicable). Then, there is a children section for each child. It asks name, dob,sex,race. It asks "your relationship to child", and anme and phone number of legal guardian/parent of child. Then there are a series of questions for that child, with the answer of yes, no, or don't know. (This repeats for each child). then there are 13 questions in another section of the app for the applicant (usualy the parent). Then there are small questions in part 4 that asks income and then for staff use only there are more questions; date app rcvd, date applicant contacted, inspection/assesment?, basic intervention? , custom intvnt? "BruceM" wrote: Sorry, no. Why don't you start by describing the situation? There is an application. Who is applying, and for what? Why are children listed on the application? What sorts of questions? If a civic group is submitting applications for children to attend summer camp, you might say something like: "A civic group submits applications for children to attend summer camp. There could be just one child, or as many as twenty. Each child is listed separately. Each child's listing includes Name, DOB and other personal information. A series of questions needs to be answered about each child." In describing the situation, you do not need a lot of detail. "Name, DOB |
#17
|
|||
|
|||
Questionnaire
I will admit I had not considered that each person could have multiple
addresses. For the question section, see Help for information on option groups. An option group selection (Yes, No, Don't Know) is stored in a single field, same as a Yes/No field but with more choices. For the "If yes, then what", that can be a table field, and a text box on the form can be bound to the field. Good luck. "Flipper" wrote in message ... Yes, I have already made all of my tables and established one to many relationships. I have already created my main form with subforms. I am just trying to figure out the one section on the paper application, which is a series of questions. I have the answer key to the questions, and just need to get the responses on there for each question. Each question will have a yes/no/don't know answer, but then a couple have a "if yes, then what source?" (or somethi9ng like that). Then there are like 6 choices and then "other" as the last choice. then I have another table for "other". "BruceM" wrote: No wonder you don't want to change it. You went to a lot of trouble putting together all of those tables. Here'a a pretty concise way of looking at tables: A table should contain information about a single real-world entity (application, child, etc.). You should be able to describe its purpose in a single sentence without using the word "and". Having said that, you can have first name and last name, or name and address, and things like that in the same table. Items such as FirstName, LastName, Street, City, Zip, Phone and so forth are all parts of a person's contact information, so they could all be in the same record in the People table (or whatever you would like to call it). A main reason for related tables is to avoid duplicating information. Consider Social Security numbers. That number identifies you for tax and benefits purposes, no matter if your name and address changes. If your name and address were part of the record for every tax or benefit transaction, then old records would contain information that may no longer be current. There is no real reason to have a separate address table. In most cases you will want that information in each person's record. If two people are at the same address and one of them moves, what would you do if you had linked both to the same address record? However, you may choose to have a Parent table with name and address information. Children would be in a related table, but the address would appear only once (in the Parent table). The whole issue of parents and children can get a bit murky, since children grow up and may become parents themselves. In that case, and in other cases as well, you may choose to put all people into one table, and to use check boxes or some such to separate them into categories. I will ignore that, since it sounds as if you aren't dealing with that situation. What I said about addresses applies to phone numbers and other personal information. Could the same person be an Applicant more than once? If so, you need to store Applicant information in its own table. In that case you would have an Application table, an Applicant table, and a Child table. tblApplication ApplicationID (PK) AppDate Reason, comments, and other information about that specific application tblApplicant ApplicantID (PK) ApplicationID (FK) AppFirstName other personal information tblChild ChildID (PK) ApplicantID (FK) ChFirstName and other information specific to the child Create relationships between the PK fields and their namesake FK fields. Click Enforce Referential Integrity for all. Build an Application form, and subforms for Applicant and Children as I described. Don't worry about data for now. Just create the tables and forms, insert the subforms as described, and try adding a few sample records. Get used to how this can work. For handling the questions, see my remarks in the previous post. For now, don't worrry about that. Make some related records, and get that part of it working. If there is a reason you cannot change your design, about all I can suggest is to see Help for information about one-to-one relationships. Apply that relationship between the address table and the People table, etc., and get ready for an administrative nightmare. "Flipper" wrote in message ... My tblApplications is as such: ApplicationID PeopleID Relationship to Children Application Date Application Score contactdate BaselineDate My tblChildren is: ChildID ApplicationID ProjectNumber FirstName Last DOB GEnder Ethnicity tblPeople PeopleID REsidentStatus ApplicantRole FirstName LastName tblAddresses AddressID PeopleID StreetADd City State Zip tblePhones PhoneID PeopleID PhoneTypeID PhoneNUmber tluPHoneTypes PhoneTypeID PHoneType I have tons of other tables too for all the questions. There are two parts sections of questions on the application. One is related to each child on the application. The other section of questions is related to the application and asks more household questions or about ALL the children, so to speak. Did you want to see how those tables are set up also? "BruceM" wrote: You will probably spend more time trying to force an ineffective design to meet your needs than you will starting over. You need a minimum of two tables, set up along the lines I have suggested. Post your table design in similar format to what I have done. Describe the relationships. Maybe I misunderstand the situation, and what you have is workable. Regarding sending the files to me: I have put some time into responding, and have told you what specific information would help me (or somebody else) provide a targeted answer. You can probably decipher the obfuscated e-mail address in the message header, but unless you are willing to at least attempt to provide information in this forum I will tell you frankly that I will not make any promises about reviewing the files. "Flipper" wrote in message ... Well, I already started and would prefer not to start over, if I don't have to. Do you have an email that I can send my db to and the word document of the paper form I am creating most of my db around? "BruceM" wrote: Now we're getting somewhere. I will suggest a table structure. The table name is listed first, with the fields below it and indented. One thing that is not clear is whether this program is just for the children with the allergies, or if it is for the parents or guardians also. For now I will assume the former. tblApplication ApplicantID (primary key, or PK) AppFirstName AppLastName AppStreet etc. tblChildren ChildID (PK) ApplicantID (foreign key, or FK) ChFirstName ChLastName etc. I will leave the out for the moment the Questions part of the process. Build these two tables. You define the PK in table design view. We'll get to the FK in a moment. For the PK you can use autonumber or any other number (or text) that is guaranteed to be unique. Access won't let you have a repeating PK value. Click Tools Relationships. Add both tables, then drag ApplicantID from one table and drop it on ApplicantID in the other table. In the dialog box that appears, click Enforce Referential Integrity. Build a form (frmApplicant) based on tblApplicant, and another form (fsubChild) based on tblChild. In form design view, drag the icon for fsubChild onto frmApplicant. Now you can enter an applicant's information, and separate records for each child. Format for both forms will probably be single view (ont eh form's property sheet - View Properties. Experiment with a few sample records. Now for the questions. If there aren't too many of them (less than 30, say, for each child) then they could be part of tblChild. Same for the applicant questions, which could be part of tblApplicant. Alternatively, questions could be in their own table that is related one-to-one with tblChild (see Help for more on one-to-one relationships). Same for applicant questions, which would probably be in their own table (if you are putting them into a separate table from tblApplicant, that is), as I assume they would be rather different than the Child questions. Spaces for staff comments could be in tblApplicant, or in a table related one-to-one. Remember that on the form (where you will be entering and editing information) and on the report (for printing information) you can arrange the fields any way you want. You are not limited to grids. Two fields that are side-by-side on the table could be on opposite corners of the form. See if this helps to get you started. "Flipper" wrote in message ... Ok. WE have a program that helps low/moderate income families with asthma/allergies. the fill out an app and we see if they qualify. The app asks Renter info and homeowner info (name, address, phone) (for each, if applicable). Then, there is a children section for each child. It asks name, dob,sex,race. It asks "your relationship to child", and anme and phone number of legal guardian/parent of child. Then there are a series of questions for that child, with the answer of yes, no, or don't know. (This repeats for each child). then there are 13 questions in another section of the app for the applicant (usualy the parent). Then there are small questions in part 4 that asks income and then for staff use only there are more questions; date app rcvd, date applicant contacted, inspection/assesment?, basic intervention? , custom intvnt? "BruceM" wrote: Sorry, no. Why don't you start by describing the situation? There is an application. Who is applying, and for what? Why are children listed on the application? What sorts of questions? If a civic group is submitting applications for children to attend summer camp, you might say something like: "A civic group submits applications for children to attend summer camp. There could be just one child, or as many as twenty. Each child is listed separately. Each child's listing includes Name, DOB and other personal information. A series of questions needs to be answered about each child." In describing the situation, you do not need a lot of detail. "Name, DOB |
#18
|
|||
|
|||
Questionnaire
Bruce,
When you refer me to "HELP", do you mean on this website or in ACCESS? Thanks. "BruceM" wrote: I will admit I had not considered that each person could have multiple addresses. For the question section, see Help for information on option groups. An option group selection (Yes, No, Don't Know) is stored in a single field, same as a Yes/No field but with more choices. For the "If yes, then what", that can be a table field, and a text box on the form can be bound to the field. Good luck. "Flipper" wrote in message ... Yes, I have already made all of my tables and established one to many relationships. I have already created my main form with subforms. I am just trying to figure out the one section on the paper application, which is a series of questions. I have the answer key to the questions, and just need to get the responses on there for each question. Each question will have a yes/no/don't know answer, but then a couple have a "if yes, then what source?" (or somethi9ng like that). Then there are like 6 choices and then "other" as the last choice. then I have another table for "other". "BruceM" wrote: No wonder you don't want to change it. You went to a lot of trouble putting together all of those tables. Here'a a pretty concise way of looking at tables: A table should contain information about a single real-world entity (application, child, etc.). You should be able to describe its purpose in a single sentence without using the word "and". Having said that, you can have first name and last name, or name and address, and things like that in the same table. Items such as FirstName, LastName, Street, City, Zip, Phone and so forth are all parts of a person's contact information, so they could all be in the same record in the People table (or whatever you would like to call it). A main reason for related tables is to avoid duplicating information. Consider Social Security numbers. That number identifies you for tax and benefits purposes, no matter if your name and address changes. If your name and address were part of the record for every tax or benefit transaction, then old records would contain information that may no longer be current. There is no real reason to have a separate address table. In most cases you will want that information in each person's record. If two people are at the same address and one of them moves, what would you do if you had linked both to the same address record? However, you may choose to have a Parent table with name and address information. Children would be in a related table, but the address would appear only once (in the Parent table). The whole issue of parents and children can get a bit murky, since children grow up and may become parents themselves. In that case, and in other cases as well, you may choose to put all people into one table, and to use check boxes or some such to separate them into categories. I will ignore that, since it sounds as if you aren't dealing with that situation. What I said about addresses applies to phone numbers and other personal information. Could the same person be an Applicant more than once? If so, you need to store Applicant information in its own table. In that case you would have an Application table, an Applicant table, and a Child table. tblApplication ApplicationID (PK) AppDate Reason, comments, and other information about that specific application tblApplicant ApplicantID (PK) ApplicationID (FK) AppFirstName other personal information tblChild ChildID (PK) ApplicantID (FK) ChFirstName and other information specific to the child Create relationships between the PK fields and their namesake FK fields. Click Enforce Referential Integrity for all. Build an Application form, and subforms for Applicant and Children as I described. Don't worry about data for now. Just create the tables and forms, insert the subforms as described, and try adding a few sample records. Get used to how this can work. For handling the questions, see my remarks in the previous post. For now, don't worrry about that. Make some related records, and get that part of it working. If there is a reason you cannot change your design, about all I can suggest is to see Help for information about one-to-one relationships. Apply that relationship between the address table and the People table, etc., and get ready for an administrative nightmare. "Flipper" wrote in message ... My tblApplications is as such: ApplicationID PeopleID Relationship to Children Application Date Application Score contactdate BaselineDate My tblChildren is: ChildID ApplicationID ProjectNumber FirstName Last DOB GEnder Ethnicity tblPeople PeopleID REsidentStatus ApplicantRole FirstName LastName tblAddresses AddressID PeopleID StreetADd City State Zip tblePhones PhoneID PeopleID PhoneTypeID PhoneNUmber tluPHoneTypes PhoneTypeID PHoneType I have tons of other tables too for all the questions. There are two parts sections of questions on the application. One is related to each child on the application. The other section of questions is related to the application and asks more household questions or about ALL the children, so to speak. Did you want to see how those tables are set up also? "BruceM" wrote: You will probably spend more time trying to force an ineffective design to meet your needs than you will starting over. You need a minimum of two tables, set up along the lines I have suggested. Post your table design in similar format to what I have done. Describe the relationships. Maybe I misunderstand the situation, and what you have is workable. Regarding sending the files to me: I have put some time into responding, and have told you what specific information would help me (or somebody else) provide a targeted answer. You can probably decipher the obfuscated e-mail address in the message header, but unless you are willing to at least attempt to provide information in this forum I will tell you frankly that I will not make any promises about reviewing the files. "Flipper" wrote in message ... Well, I already started and would prefer not to start over, if I don't have to. Do you have an email that I can send my db to and the word document of the paper form I am creating most of my db around? "BruceM" wrote: Now we're getting somewhere. I will suggest a table structure. The table name is listed first, with the fields below it and indented. One thing that is not clear is whether this program is just for the children with the allergies, or if it is for the parents or guardians also. For now I will assume the former. tblApplication ApplicantID (primary key, or PK) AppFirstName AppLastName AppStreet etc. tblChildren ChildID (PK) ApplicantID (foreign key, or FK) ChFirstName ChLastName etc. I will leave the out for the moment the Questions part of the process. Build these two tables. You define the PK in table design view. We'll get to the FK in a moment. For the PK you can use autonumber or any other number (or text) that is guaranteed to be unique. Access won't let you have a repeating PK value. Click Tools Relationships. Add both tables, then drag ApplicantID from one table and drop it on ApplicantID in the other table. In the dialog box that appears, click Enforce Referential Integrity. Build a form (frmApplicant) based on tblApplicant, and another form (fsubChild) based on tblChild. In form design view, drag the icon for fsubChild onto frmApplicant. Now you can enter an applicant's information, and separate records for each child. Format for both forms will probably be single view (ont eh form's property sheet - View Properties. Experiment with a few sample records. Now for the questions. If there aren't too many of them (less than 30, say, for each child) then they could be part of tblChild. Same for the applicant questions, which could be part of tblApplicant. Alternatively, questions could be in their own table that is related one-to-one with tblChild (see Help for more on one-to-one relationships). Same for applicant questions, which would probably be in their own table (if you are putting them into a separate table from tblApplicant, that is), as I assume they would be rather different than the Child questions. Spaces for staff comments could be in tblApplicant, or in a table related one-to-one. Remember that on the form (where you will be entering and editing information) and on the report (for printing information) you can arrange the fields any way you want. You are not limited to grids. Two |
#19
|
|||
|
|||
Questionnaire
I am back and did end up redesigning my DB. I feel it is much better and
closer to what you had suggested. So, now I am trying to tackle my applicant questions/responses. There are 13 questions. Some of the questions have multiple choice answers (yes/no/don't know) and two of them have text answers, and one of them that has multiple choice for the answers of the following: Reind/Relative, Hospital, Doctor/Nurse, etc, and the final multiple choice is "other". If the applicant chooses "other", then I need to have a spot that they can fill in what the other is, since there is a blank line on the paper form asking them to describe "other". I think I need a form to pop up, if they choose other. I have a table set up for when "other" is chosen. Also, with these 13 questions, there are two questions that as I explained before, have a multiple choice answer of yes,no or don't know. But it then goes on to say, IF yes, please answer the following multiple choices. It goes on to give them choices, which they can choose more than one. One of the choices is "other", so once again, I have that situation to tackle. My tables for those are as follows: tblApplication tblApplicantQuestions tblApplicantResponses tblPart3Specify tblPart3Other and a junction table Not sure if I went about this the right way. And I don't really know what I am doing. Any suggestions how to set up these questions? Thank you. "Flipper" wrote: Bruce, When you refer me to "HELP", do you mean on this website or in ACCESS? Thanks. "BruceM" wrote: I will admit I had not considered that each person could have multiple addresses. For the question section, see Help for information on option groups. An option group selection (Yes, No, Don't Know) is stored in a single field, same as a Yes/No field but with more choices. For the "If yes, then what", that can be a table field, and a text box on the form can be bound to the field. Good luck. "Flipper" wrote in message ... Yes, I have already made all of my tables and established one to many relationships. I have already created my main form with subforms. I am just trying to figure out the one section on the paper application, which is a series of questions. I have the answer key to the questions, and just need to get the responses on there for each question. Each question will have a yes/no/don't know answer, but then a couple have a "if yes, then what source?" (or somethi9ng like that). Then there are like 6 choices and then "other" as the last choice. then I have another table for "other". "BruceM" wrote: No wonder you don't want to change it. You went to a lot of trouble putting together all of those tables. Here'a a pretty concise way of looking at tables: A table should contain information about a single real-world entity (application, child, etc.). You should be able to describe its purpose in a single sentence without using the word "and". Having said that, you can have first name and last name, or name and address, and things like that in the same table. Items such as FirstName, LastName, Street, City, Zip, Phone and so forth are all parts of a person's contact information, so they could all be in the same record in the People table (or whatever you would like to call it). A main reason for related tables is to avoid duplicating information. Consider Social Security numbers. That number identifies you for tax and benefits purposes, no matter if your name and address changes. If your name and address were part of the record for every tax or benefit transaction, then old records would contain information that may no longer be current. There is no real reason to have a separate address table. In most cases you will want that information in each person's record. If two people are at the same address and one of them moves, what would you do if you had linked both to the same address record? However, you may choose to have a Parent table with name and address information. Children would be in a related table, but the address would appear only once (in the Parent table). The whole issue of parents and children can get a bit murky, since children grow up and may become parents themselves. In that case, and in other cases as well, you may choose to put all people into one table, and to use check boxes or some such to separate them into categories. I will ignore that, since it sounds as if you aren't dealing with that situation. What I said about addresses applies to phone numbers and other personal information. Could the same person be an Applicant more than once? If so, you need to store Applicant information in its own table. In that case you would have an Application table, an Applicant table, and a Child table. tblApplication ApplicationID (PK) AppDate Reason, comments, and other information about that specific application tblApplicant ApplicantID (PK) ApplicationID (FK) AppFirstName other personal information tblChild ChildID (PK) ApplicantID (FK) ChFirstName and other information specific to the child Create relationships between the PK fields and their namesake FK fields. Click Enforce Referential Integrity for all. Build an Application form, and subforms for Applicant and Children as I described. Don't worry about data for now. Just create the tables and forms, insert the subforms as described, and try adding a few sample records. Get used to how this can work. For handling the questions, see my remarks in the previous post. For now, don't worrry about that. Make some related records, and get that part of it working. If there is a reason you cannot change your design, about all I can suggest is to see Help for information about one-to-one relationships. Apply that relationship between the address table and the People table, etc., and get ready for an administrative nightmare. "Flipper" wrote in message ... My tblApplications is as such: ApplicationID PeopleID Relationship to Children Application Date Application Score contactdate BaselineDate My tblChildren is: ChildID ApplicationID ProjectNumber FirstName Last DOB GEnder Ethnicity tblPeople PeopleID REsidentStatus ApplicantRole FirstName LastName tblAddresses AddressID PeopleID StreetADd City State Zip tblePhones PhoneID PeopleID PhoneTypeID PhoneNUmber tluPHoneTypes PhoneTypeID PHoneType I have tons of other tables too for all the questions. There are two parts sections of questions on the application. One is related to each child on the application. The other section of questions is related to the application and asks more household questions or about ALL the children, so to speak. Did you want to see how those tables are set up also? "BruceM" wrote: You will probably spend more time trying to force an ineffective design to meet your needs than you will starting over. You need a minimum of two tables, set up along the lines I have suggested. Post your table design in similar format to what I have done. Describe the relationships. Maybe I misunderstand the situation, and what you have is workable. Regarding sending the files to me: I have put some time into responding, and have told you what specific information would help me (or somebody else) provide a targeted answer. You can probably decipher the obfuscated e-mail address in the message header, but unless you are willing to at least attempt to provide information in this forum I will tell you frankly that I will not make any promises about reviewing the files. "Flipper" wrote in message ... Well, I already started and would prefer not to start over, if I don't have to. Do you have an email that I can send my db to and the word document of the paper form I am creating most of my db around? "BruceM" wrote: Now we're getting somewhere. I will suggest a table structure. The table name is listed first, with the fields below it and indented. One thing that is not clear is whether this program is just for the children with the allergies, or if it is for the parents or guardians also. For now I will assume the former. tblApplication ApplicantID (primary key, or PK) AppFirstName AppLastName AppStreet etc. tblChildren ChildID (PK) ApplicantID (foreign key, or FK) ChFirstName ChLastName etc. I will leave the out for the moment the Questions part of the process. Build these two tables. You define the PK in table design view. We'll get to the FK in a moment. For the PK you can use autonumber or any other number (or text) that is guaranteed to be unique. Access won't let you have a repeating PK value. Click Tools Relationships. Add both tables, then drag ApplicantID from one table and drop it on ApplicantID in the other table. In the dialog box that appears, click Enforce Referential Integrity. Build a form (frmApplicant) based on tblApplicant, and another form (fsubChild) based on tblChild. In form design view, drag the icon for fsubChild onto frmApplicant. Now you can enter an applicant's information, and separate records for each child. Format for both forms will probably be single view (ont eh form's property sheet - View Properties. Experiment with a few sample records. Now for the questions. If there aren't too many of them (less than 30, say, for each child) then they could be part of tblChild. Same for the applicant questions, which could be part of tblApplicant. Alternatively, questions could be in their own table that is related one-to-one with tblChild (see Help for more on one-to-one relationships). Same for applicant questions, which would probably be in their own table (if you are putting them into a separate table from tblApplicant, that is), as I assume they would be rather different than the Child questions. Spaces for staff comments could be in tblApplicant, or in a table related one-to-one. Remember that on the form (where you will be entering |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Analysis of Questionnaire Data | J. Reeves | New Users | 3 | January 4th, 2006 10:48 PM |
I want to construct a questionnaire using Microsoft Publisher. | jilgu | Publisher | 3 | December 15th, 2005 05:16 PM |
Questionnaire - dilemma. | Anthony Hodges | Using Forms | 6 | March 9th, 2005 05:28 PM |
Questionnaire - dilemma. | Anthony Hodges | Setting Up & Running Reports | 6 | March 9th, 2005 05:28 PM |
Questionnaire form | Jean | New Users | 2 | May 24th, 2004 07:08 AM |