This is Part II of a two-part tutorial on how to create a survey database in Access. Part I introduced the sample project, survey, and how to set up the tables. Part II picks up where Part I left off, and walks you through how to set up your data entry form.
Video 1: Starting a new form; Design View; dragging and dropping fields onto your form; “labels” vs “controls”; moving controls and labels; deleting, editing, and adding labels; the “Toolbox”
http://www.researchtutorials.com/videos/dm/surveydatabaseB1new.flv
Video 2: Form View; hiding the default record selector, dividing line, and scroll bars; saving and naming form
http://www.researchtutorials.com/videos/dm/surveydatabaseB2.flv
Video 3: Adding more fields to the form; copying and pasting labels; using form grid to maintain a consistent layout
http://www.researchtutorials.com/videos/dm/surveydatabaseB3.flv
Video 4: Changing font; resizing controls & labels; grouping and deselecting controls; moving a group of controls closer to their labels by lengthening and resizing
http://www.researchtutorials.com/videos/dm/surveydatabaseB4.flv
Video 5: Updating your form and field list after making changes (e.g., properties, new fields, etc.) to the underlying table
http://www.researchtutorials.com/videos/dm/surveydatabaseB5.flv
Video 6: Adding some more labels; adding a hard return (CTRL+ENTER) inside a label so it goes on two lines; shrink excess form space
http://www.researchtutorials.com/videos/dm/surveydatabaseB6.flv
Video 7: Changing text alignment; changing width of drop down list; ensuring your data entry boxes are mapped to the correct field
http://www.researchtutorials.com/videos/dm/surveydatabaseB7.flv
Video 8: Add a soft gray line; changing the tab order
http://www.researchtutorials.com/videos/dm/surveydatabaseB8.flv
Video 9: Add a “Delete Record” button; brief view of Visual Basic, Access’ programming language
http://www.researchtutorials.com/videos/dm/surveydatabaseB9.flv
Video 10: Create rectangles, background colors, and section headings; Access’ weird ampersand (&) issue; changing the order of items in a drop down (i.e., combo) box; show more than 8 items in a drop down list
http://www.researchtutorials.com/videos/dm/surveydatabaseB10.flv
access 2003, questionnaire, survey
October 4th, 2009 at 10:15 pm
Extremely helpful. Thanks.
Unfortunately, I am running into some of the limits Access imposes on the number of fields in a table and the number of objects in a form. Thus, I have had to create multiple tables. So, I have two questions.
First, with multiple tables, how can I get the primary key entered on each table (survey respondent ID) from one object on the form?
Second, is there a way to create multiple, linked forms (i.e. to get around the number of objects allowed in one form)?
October 6th, 2009 at 11:18 am
Dan:
> Unfortunately, I am running into some of the limits Access imposes on the number of fields in a table and the number of objects in a form.
What you’ve encountered is another limitation of the non-normal structure: in big databases you will quickly reach the limits Access imposes on tables (255 fields) and forms and reports (754 controls). At this point, most people would suggest you reevaluate your database design and consider normalizing some aspects of the data. If you’ve considered this and still want to proceed with a non-normal structure, here’s what you can do:
First, while developing your database, make sure you regularly compact and repair (C & R) it (Tools > Database Utilities > Compact and Repair Database). You should do this because Access retains in memory everything you delete until you C & R. Because C & R purges the database of this leftover bloat, it will shrink the size of your .mdb file and may even free you (temporarily) from the “you’ve reached the limit of 255 fields” notice.
As to your specific questions:
> First, with multiple tables, how can I get the primary key entered on each table (survey respondent ID) from one object on the form? Second, is there a way to create multiple, linked forms (i.e. to get around the number of objects allowed in one form)?
You have several options. Since you’ve exceeded the form limits, you need to make multiple forms (and link them), just like you’ve created multiple tables (and linked them). Here’s what I’d recommend:
First, make sure you’ve related your tables one-to-one, not one-to-many. (Multiple tables linked together one-to-one is really just one big table.)
Table Design:
1. Give each table to be related the same PK (e.g., SurveyID).
2. In the first table (e.g., tblSurvey1), make the PK’s data type AutoNumber; in the second table (e.g., tblSurvey2), make the data type Number (and get rid of that default 0).
Table Linking:
3. Go to Tools > Relationships
4. Add the two tables to the workspace
5. Draw a line from the bold field (SurveyID) in tblSurvey1 to the bold field (SurveyID) in tblSurvey2. If done correctly you should see a 1 and a 1 at each end of the line that connects the tables. You now have a 1 to 1 relationship.
Form Design:
6. Create a form based on tblSurvey1 (might call it frmSurvey1; this is going to be your main form). Include your PK field (SurveyID) and a few other fields.
7. Create a form based on tblSurvey2 (might call it fsubSurvey2; this is going to be your subform). For now, I recommend including the PK (SurveyID) field on this form, along with your other fields. You don’t need it but it will help you verify things are linking properly. You can later delete it from the form, keep it but set its Visible property to False, or keep it but set it’s Enabled property to False (this grays it out).
8. Open frmSurvey1 in Design View. On the Toolbox, make sure the wand is selected, and click the “Subform/Subreport” icon (it’s off to the right).
9. In the SubForm Wizard dialog:
… a. Choose “Use an existing form” and select fsubSurvey2
… b. Click Next
… c. Accept the suggested linking approach (it should end with “… using SurveyID”, which it detected based on how you related the tables).
… d. click Finish
10. Open frmSurvey1 in Form view and test it out. The moment you add something to the record in your main form (frmSurvey1), Access should automatically take the value of SurveyID in your main form (and tblSurvey1) and apply it to the SurveyID field in your subform (and tbleSurvey2).
October 13th, 2009 at 10:48 pm
Your tutorial is EXCELLENT I have not found any one as good as yours. THANK YOU! Really, really helpful. I am in the same situation than Dan with the number of fields and objects in the database. I am going to follow your advise to Dan about how to link multiples tables and forms to avoid the access limitation. My final goal is to export the data to SPSS.
I’ll be working with about 155 question and a total of about 300 variables or fields and I have a question:
-Would it be possible to save the data using the data entry form that you describe in different sections?
October 19th, 2009 at 10:12 am
> Would it be possible to save the data using the data entry form that you describe in different sections?
Not sure if I follow you … if the fields are all based on a single table, the data will also be stored in that table. But if you wanted to logically group certain fields together, you could create a select query that includes only those fields, and export that to SPSS.
But … consider restructuring your database. Encountering the field limit like you and Dan did is usually a sign that your database needs to be restructured.
I suspect your database could be normalized a bit to avoid the limitations you’ve encountered. For instance, are you storing multiple related entries for each survey in separate fields? If so, you probably have a bunch of fields like ABC1, ABC2, ABC3, etc. This is usually a place to normalize.
Consider the survey used in this tutorial. If I wanted to also collect data on each person’s appointments, I could create fields like Appt1, Appt2, Appt3, etc. If I also wanted to collect information about each appointment, like whether they showed up or not, I might also make fields like Appt1Outcome, Appt2Outcome, etc. This is a bad move and will lead to many problems down the road, only one of which is the Access field limit. To normalize this, I would store appointment information in a separate table, called tblAppointments.
tblAppointments
—————
AppointmentID (the Primary Key; DataType: AutoNumber)
SurveyID (link this to SurveyID, the primary key in tblSurveys)
Outcome
PaymentType
etc.
When you link tblSurveys and tblAppointments as indicated, it will create a one to many relationship, such that ONE survey can have one or MANY appointments associated with it. This is also nice because it stores your date in more logical units – a table with only Appointment data, a table with only Survey data, etc.
To implement a one to many relationship on the form level, create a form (in Datasheet view for example) based on tblAppointments. Add the form to your main form (which is based on tblSurveys), and link the two based on their common key, which is SurveyID. In form Design View, there is a subform/subreport button on the toolbox that will launch a wizard and walk you through every step.
I plan to do a tutorial on this in the future.
October 21st, 2009 at 9:20 pm
Thanks for your help Kurt.
We would like to have all the fields in one table, but because the Access limitation with number of fields. I was planning to follow your advise to Dan about linking two tables (one to one relationship).
Due to that our survey has many questions I would like to have the possibility to save the data entry using the access Form in different sections. For example, it is probably that one person could start to entry the survey corresponding to record 1 and on question 75 needs to stop. Could it be possible to save the survey at that point and later continue with question 76.
It will be wonderful any additional tutorial about access because your explanations are very clear and useful. Thanks you!
October 22nd, 2009 at 10:42 am
Access saves the data automatically as you enter it.
November 1st, 2009 at 9:47 pm
Thank you!
December 7th, 2009 at 4:04 am
I would really like to see a tutorial on this. Everything else you’ve done is brilliant!
October 15th, 2009 at 11:12 am
Anyone ever run into the problem of a form, in form view, graying out? I have a main form, several subforms found in tabbed sheets of my main form. When I am working on this in design view, all is fine. As soon as I switch to form view, all grays out… fields, labels, tabs… everything. I can switch back to design view and all is fine again.
October 19th, 2009 at 9:53 am
This problem is often caused by basing a form on a query that is non-updatable. I suspect if you open the query the form is based on, you won’t be able to add any records to it.
A non-updatable query is usually caused by improperly joining two or more tables. This sometimes occurs if one or more tables in the query does not have a primary key.
October 22nd, 2009 at 9:31 am
Hi Kurt – Thanks for your reply. I made sure that all the tables in my query had primary keys. My query references three tables, Introduction_Q which is the main table (primary key = SurveyID), Other_Q (primary key = OtherID (autonumber), and Housing_Q (primary key = HousingID (autonumber). I still have the same problem – everything including the various tabs disappear on my dataentry form in form view. However, I still think that my problem is based on the query. When I delete references to the tables Housing_Q and Other_Q above, I can then view my form in form view however, then I receive the following error message 3 times…
The LinkMasterFields property setting has produced this error: ‘The object doesn’t contain the Automation object ‘Introduction_Q.”
Not sure what this means since that is the only table that remains in my query.
Having looked a little online, it appears there are many explanations for disappearing forms in form view (e.g. subform control is too small for underlying subform, scroll bar settings are wrong, control that first receives focus is located too far down the tabbed page, or when there are no records to display and no records can be added). I checked and played with each of these and nothing worked. Based on my monkeying around with the query, however, I think the problem lies there.
October 22nd, 2009 at 10:30 am
> I made sure that all the tables in my query had primary keys.
What’s more important is how these three tables are joined. i.e., how are they related? For instance, is there a one-to- many between Introduction_Q and Other_Q, and another one-to- many between Introduction_Q and Housing_Q?. If so, both Other_Q and Housing_Q should have a field called SurveyID (but not as the primary key). Introduction_Q’s SurveyID field (the PK) should be linked to the SurveyID field in these two tables.
1. You can verify if you have a problem with your query (and not just your form) by opening the query. Does it open without any errors? If so, can you add records to it (i.e., is it updatable?)
2. You have a problem with how your form and subform(s) are linked as evidenced by that error.
The Link Master Fields property (along with the Link Child Fields property) specifies how Access links records in a subform to records in main form. Double click on the *border* of your subform. A dialog box called “Subform/Subreport: [name of subform] will appear. Click on the All tab and you’ll see the Link Master and Child properties. If two tables are related on the same field (e.g., SurveyID), this is usually the field listed in both fields (the Master field refers to the key used in the parent table/main form – usually the PK – and the Child field refers to the key used in the child table/subform – usually a field by the same name, but not the PK).
This website needs a forum!
October 22nd, 2009 at 12:17 pm
Forms are joined as you state. One to many.
The query opens without error. When I added records, I just used the * so as to indicate all records in each of the three tables. Is this a problem? I’ve tried to add the fields individually and this works too.
Each of my subforms has the same Link Master and Child properties…
Link Child Fields… SurveyID
Link Master Fields… Introduction_Q.SurveyID
Hmmm… a forum would be nice. I know I have taken advantage of your generosity. I’ll try one of the online forums. Thanks again for all of your help.
October 19th, 2009 at 10:57 am
Thanks for your reply Kurt. I suspected this was related to primary keys. As you may remember, I have many tables corresponding to question sets in tablular form. Per your recommendation, I added a primary key to each (e.g. Crop_Type) and also added SurveyID with which I linked all child tables to the main survey table. I have other tables which do not correspond to tabular question sets. On these tables, I also have the field SurveyID. Should the SurveyID field in these tables be linked in the same way to the main survey table? I suspect so. Should SurveyID be made a primary key in these tables? I suspect not but am not so sure.
October 19th, 2009 at 11:15 am
> Per your recommendation, I added a primary key to each (e.g. Crop_Type) and also added SurveyID with which I linked all child tables to the main survey table.
Good! So long as the lines between the parent and each child table have a “1″ (next to bold SurveyID in the parent table) and an infinity sign (next to the non-bold SurveyID in the child table), the tables are properly linked.
> I have other tables which do not correspond to tabular question sets.
On these tables, I also have the field SurveyID. Should the SurveyID field in these tables be linked in the same way to the main survey table? I suspect so.
I suspect so, too. If the relationship between the parent and child is still one to many, the set up should be the same as before: SurveyID is the PK in the parent table, but not the PK in the child table. The child table should use a different field, probably an AutoNumber one, as its PK.
> Should SurveyID be made a primary key in these tables? I suspect not but am not so sure.
I suspect not, too.
If you made it the PK and then tried to link it to the parent table, it would become a 1 to 1 relationship.
October 23rd, 2009 at 5:42 am
Do you have a Part III to this series, which would cover Reports?
November 27th, 2009 at 7:38 pm
I am very confused about the normalized version of this survey. I have 100 surveys to enter into Access from a college, all of which are four pages, but plan to add multiple batches of 100 surveys in the near future (for other population samples– ie, other colleges).
I want to keep each batch of 100 separate, and I will want to use regression at one point to analyze the data.
I tried to follow your “normalized data tables” brief introduction, but it doesn’t make any sense to me. Your four proposed tables were: 1 for questions, 1 for possible answers, 1 for respondents, and 1 for completed surveys (“responses”). I like that idea of using the Responses table, but get stuck.
The Questions table you show in datasheet view has a new record for each question– not a new field for each question. Yet your Answers table only shows “question ID” and “choice” for possible fields– that’s where I get stuck. What do I enter in for choice in the datasheet view of the Answer table for it to make sense later on when I link it with the Questions and Responses table. (For now, I don’t plan on keeping a Respondent table).
Please let me know. Thank you so much.
-David
December 24th, 2009 at 8:57 am
I agree with everyone here, this is a great tutorial. I am going to have some of my Marines, take a look, so they can learn about Access.
I am trying to build a survey database as well, but not as large as some of the other respondents I have seen. I have two tables one for Instructional Rating Form and one for Examination Rating Form. The vast majority of the questions utilize the Licard (spelling?)scale. What I need to be able to is take and make an after instruction/examination report, that totals how many people reponded to agree, disagree and so on per question. That is where I get stumped.
I do not have the option to use any further software, I have to stay in Access to create my reports. I checked out your sample database with the union query, but can’t see how to get them grouped to the specific question.
Your tutorial has already helped a great deal. Thanks in advance.
Semper Fi,
Jon
January 24th, 2010 at 10:04 pm
Thank you so much! This was amazingly helpful. An additional tutorial about importing data would also be appreciated…
May 30th, 2010 at 7:46 am
I have duplicated the non-normalized database, but the problem I am having is with the queries. I will not be exporting my data to SAS, so I would like to see the actual text value, not the number value. How can I do this considering my queries are storing just numbers?
July 9th, 2010 at 6:59 pm
Thank you very much for sharing! It is a very useful tutorial. I have a question: after I create the data base, how do I send the survey to every respondant in order to feed my data base?
August 20th, 2010 at 2:22 pm
Kurt – You gave me a lot of valuable help in setting up a database and forms for entering household surveys. It all went very well so thanks again for your help.
I have what might be an easy question. We are in year two of our study. We kept the same instrument so essentially I’d like to begin entering year 2 data. Ideally, I change the year for a particular respondent and the form then goes blank and I am able to enter all year 2 data. I am not sure how to do this. I can’t create a new record as we are sticking with the same respondent IDs (i.e. primary keys). Any quick thoughts or suggestions? What is the best way to do this?
August 26th, 2010 at 11:05 am
You’ll need to split your existing table (tblSurveys?) into two tables, tblSubjects and tblSurveys, and create a one-to-many relationship between them, such that *one* Subject can have *many* Surveys. Read up on one-to-many relationships to understand the concept.
tblSubjects will contain fields like SubjectID (the primary key), and any data unique to the subject that you’re keeping (Age, Race, etc.).
tblSurveys will contain fields like SurveyID (the primary key), SubjectID (the “foreign key” linked to SubjectID in tblSurveys), and any fields unique to the survey (year, q1, q2, q3, etc.) So, a subject who completes two surveys will have one SubjectID and two SurveyIDs.
At the form level, one-to-many relationships are implemented using a main form (frmSubjects, based on tblSubjects) and a subform (fsubSurveys, based on tblSurveys). The subform is linked to the main form via SurveyID, which is the only field the two tables have in common.
Since your table is already filled with data, splitting things into two tables that are properly linked is tricky. Access has a Wizard to help you do this (Tools > Analyze > Table) but it’s not always intuitive. It’s a place to start, though. Once you get the table structure in place, your next step is to create the form and subform interface. For your situation, I’m guessing: 1) create a new form called frmSubjects based on tblSubjects, that includes all the Subject-specific fields (maybe that’s just SubjectID); 2) drag and drop your current form, frmSurveys, onto frmSubjects, thus making it a subform. If all goes well, Access will detect the common link and link them appropriately, allowing you to enter multiple surveys for each subject.