Brian's Science Education Site

Brian's Class Materials- SPRING 2008 - SEYS 778

SEYS 778 Home

Spring 2008

SEYS 778 – Seminar Research in Science Education II

Wednesday 7:10 pm to 9:40 pm

Powdermaker Hall Room 020

Data Entry Using Open Office Base and Calc

Tutorial

(Click here for the video demos that go with this tutorial)

Data entry is an extremely important step in the research process. If it is not done properly, missing and incorrect data can result. When large numbers of surveys or test scores need to be entered by hand, the data entry process becomes even more important.

Please note: If you are collecting certain types of qualitative data, e.g. interviews, content analysis, observations, you will be analyzing your data differently. There are software applications available for qualitative data such as N.U.D.I.S.T. (commercial software) and Weft QDA (free and open source) and I will post a tutorial for these later.

There are many software applications that can be used for data entry and analysis. A lot depends on what is available. If you have a favorite statistics program you would like to use, that's fine. These are just suggestions and links that might be helpful. Here are a few possible options:

Possible Data Entry Options

1) Create a database and form in SPSS and enter your data directly into SPSS. This is the ideal method but it requires you to have a licensed copy of SPSS. Queens College does have a site license for SPSS and it is available in the Campus computer labs. Students can also purchase SPSS at a reduced price.
2) Create a database and form in Microsoft Access and enter your data. You can then import the data in Excel and SPSS for further data analysis. Unfortunately Microsoft Access is always not included in Microsoft Office. Another problem is that you may not have the latest version of Excel.
3) Create a database and form in OpenOffice.org Base and enter your data. You can then import the data into Calc or Excel, and finally SPSS for further data analysis. OpenOffice.org Base and Calc are free, open source software applications. They are also compatible with Microsoft Office. You can obtain the very latest versions plus any extensions you might want. The database and spreadsheet files can be exported in a variety of formats that can be used by SPSS or other statistics software. This is the option I am recommending for most people.
4) One other possibility is to collect your data online using web based applications such as surveymonkey or phpesp. If you do this, you basically "outsource" the data entry to the participants in your study. You can then export the data and then pull it into a spreadsheet or SPSS later. Of course, your participants would have to all have to access to computers to complete the survey or exam.
5) If you don't have much data, you can enter your data directly into OpenStat.

This tutorial is going to use the tools mentioned in 3), i.e, Open Office. A subsequent tutorial will deal with data analysis using SPSS and Excel.

Tip: Throughout this tutorial you may need to "right click" with your mouse. Macs do not have right and left mouse buttons but you can still "right click" with a Mac. You should hold down the "control" key and click, when you need to right click when using a Mac.

Step 1 - Download and install Open Office. Visit http://www.openoffice.org/, click on "Get OpenOffice.org" and follow the directions.

Step 2 - Launch OpenOffice.org Base

Step 3 - Create a database using the Database wizard

Select the radio button "Create a new database"
Click the "next" button
The radio button "Yes, register the database for me" should be selected, and check "open the database for editing"
Click the "Finish" button to save the database
Give your database a File name. I will call it survey-science1. You can give it your own file name but do not leave spaces in the file name. Make sure to note where you save the survey-science1 database. Click "Save" and "Finish"
Now you need to create a table. Click on the "Tables" icon in the upper left. Click on "Create table in design view"
Enter a field for each item on your survey or test. For example, I will start my table with fields for firstname, lastname, gender, ethnicity, Q1, Q2, a numerical score (e.g. a math test score) and a field for comments.
Click in the box under "Field name" and type in firstname. In the column under "Field Type" you will see the default field type, text [VARCHAR]. This just means that this field is designated for text of variable character length. If you look at the bottom of the page you will see "Entry required" and I am going to choose "yes" since I want all of the participants to enter this field.
Press the "Tab" key until you are at the second record. Now create a field for "lastname". Do the same for "gender"
Let's assume that Q1 is a question that uses a Likert scale. You can create fields for Q1 of the same field type, text [VARCHAR]
Create a field called "math-score" and choose "number" for the field type.
Finally create a field named "comments" with a field type of text[VARCHAR]
Now you need to save your table. Click on "File" in the upper left, and then on "Save". You will be prompted to enter a name. You can just accept the default name "Table1" and click "OK".
A pop up message will ask "Should a primary key be created now?" Click the "Yes" button.
If you look at the top left of your table, you should now see a new field named "ID" with a gold key to the left of "ID".
Minimize or close the Table Design window. Now you are going to create a form to enter data into your database table.

Step 4 - Create a form to enter data into your database table

Open Office Base should still be open (the icon is light red in color on the taskbar below)
Click on the "Forms" icon on the left
Click on "Use wizard to create form"
A new window will open with OpenOffice Writer in the background (this is equivalent to Microsoft Word). A popup Form Wizard will now guide you through the creation of the form.
The first step is the select the fields you want to appear on your data entry form. In our case we want all of them. You can add them to your form by clicking on one at a time and then on the right pointing arrow. Or you can add them all in one step by clicking on the ">>" .
Click on the "Next" button. We don't need a subform, so now click on the "Next" button again.
Do not change anything on the "Arrange the controls on the form" page. Click the "Next" button.
The radio button should be selected next to "The form is to display all data" and click the "Next" button.
Leave the "Field border" as "3D look" and the style as "Beige" and click "Next"
You should now be at the "Set the name of the form" page. Leave the name as "Table1". You can now either "work with the form" (this means you can enter data but not make any changes in the form", or "modify the form". Click the radio button next to "modify the form" and click the "Finish" button.
You will now see a gray table with the field names arranged horizontally across the top of the form.
The first three fields are fine and we will leave them as they are. We can make changes to the form by right clicking on the fieldname.
Right click on "Gender" and then choose "Replace with" and then on "List box"
Right click on "Gender" then on "Column" and now we can type the choices we want listed for gender. Click "List entries" and then type in "female" and press the "Shift" and then "Enter" keys. Now type in "male" and press enter. Click the small "x" in the Property List Box to close.
Now we will modify Q1 and add the following "List entries": strongly agree, agree, disagree, strongly disagree in the same way.
The next field, math-score, is already a numerica field. You can right click on "math-score" and then select "column" to see the various options available to you.
Please note, you can right click and then add a column to the form. If you do this, you would also have to go back and add a field to your database table. Then you can right click on the new column in your form, then on "data" and select the appropriate field.
Right click on the "Comments" column and then change the "Width" to 3.0 This just leaves more space on the form for comments. If you notice the "maximum text length" is "0" by default and this means that there is no maximum number of characters.
Now we have finished modifying our data entry form.
If you look at the left side of the window near the top, there is a small icon with a green pencil, a yellow triangle, and a white ruler. This button toggles between form "design mode" on or off. When "design mode is on, you can make changes to the structure of your data entry form but you cannot enter any data.
Click on the "design mode" icon and now design mode is off. The gray square around the design mode icon disappears and boxe appear under the field names on your data entry form.
Go ahead and enter some sample data in your form. You can use the tab key to move from one field to the next.
After you have entered some data, save your data. Click on "File", and then on "Save".
Make sure you have saved everything and then close the windows with your database and form.
You should be back at OpenOffice Base now. If you click on the "Tables" icon on the left, you should see your database table below. You can open it up by click on "Table1"
You should see the sample data you entered.

You may now be asking, why did we go to all of the trouble of creating a form with the drop down lists? If you have lots of data to enter, forms make it much easier and also help to ensure that you get complete and clean data. Typing data directly into a table results in many errors and omissions.

Step 5 - View the data in a spreadsheet, OpenOffice Calc

Make sure that OpenOffice Base is open. Click on Table1 but don't open it.
Click on "File" and then "New" and then "spreadsheet"
OpenOffice Calc is now open with a blank spreadsheet.
Click on the upper left of the spreadsheet, i.e. the "A" column.
Click on "Edit" and then "Paste" and you should see your data table appear in the Calc spreasheet.
Click on "File" then "Save as" and save your file. Make sure to remember where you save your spreadsheet. Notice that the spreadsheet is saved by default as as "Open Document Spreadsheet" with a filename extension of .ods This format is compatible with all open source spreadsheet applications. If you want to open the spreadsheet in Excel, you can choose "File" then "Save as" and then change "Save as type" to the Excel format that you wish. Notice that you can save your data in many different formats and this is very useful when you want to import your data into a statistical software application such as SPSS. Two very useful formats for this are .csv or .dbf .

Step 6 - After going through this tutorial, you can also view a video demonstration of how I set up a different database table and form. I would recommend that you play around with this a bit and then create your database and form.

Step 7 - More than one source of data and multiple tables

If you are going to have several sources of data from the same group of participants you may want to create tables for each data source and then link them with a common field, e.g. the ID. You do NOT have to do this as you can do most things with one simple table.

Menu:

Latest news:

More info:

Links: