How+to+Create+a+Database

(Added - 6/27 DJShipley)Visual & Audio Tutorial on [|Using Access] found at Learning Electric.com. S06/database course/descriptions of various database terms and their uses

Philosopher Wittgenstein is a Mountain Ralph Maltese link to Falconroost wiki https://falconroost.wikispaces.com/ Here are links to a demonstration of how to create parts of a database: Table >>>> http://www.screencast-o-matic.com/watch/cjhQ1DVOV Form >>>> http://www.screencast-o-matic.com/watch/cjho11Vt8 Query>>>> http://www.screencast-o-matic.com/watch/cjho1MVtR Report>>>> http://www.screencast-o-matic.com/watch/cjhoXFVtS Filtering in a Table >>>> http://www.screencast-o-matic.com/watch/cjhD1GVuQ This document contains information about the following items: (click on an item to jump to it or simply scroll down) Record Field Table Form Query Report Views Creating a New Database Default Value Primary Key [|Changing a Database Table] [|Entering Data in a Database] [|Creating a Form to Enter Data] [|Using a Form to Enter Data] [|Generating a Query] [|Creating a Report] [|Using Yes/No as a Type of Field When Designing a Table] [|Filter by Selection] [|Filter by Form] [|Sorting a Column] Big Oh Oh A record is the basic unit of a database. A table is actually the list of records. Each record is composed of fields. For example, an entry in a telephone book is a record. The person’s name is a field, the address is another field, and the phone is a third field. You can have a student record composed of fields. The name of the student would be one field, his counselor another field, his grade level a third field, etc. You construct a table in design view. Most grading programs (Gradebook, LetterGrade, Progress Book) are actually databases (or combinations of spreadsheets and databases). The table is the heart of the database. If information is NOT contained in the table, it does not exist anywhere else in the database (with minor esoteric exceptions). So if you want information to appear in a form, then it must exist in the table. A report is predicated on a form or a query, and both of the latter are predicated on a table, so any information in a report, again, with certain exceptions, must appear in a table.

A form is just what it sounds like. You base a form on a table, picking out which fields you want to appear on the form. For example, if I design a form for students to fill out, I would include in the form certain fields from the table—student name, grade level, counselor, etc. A form can be printed out or used to enter data into the database.

A query is the workhorse of a database. Like a form, you base a query on a table (or on a form or even another query!!) You would use a query to select certain records. For example, using our student database, you could design a query to pick out only those students who are in tenth grade or only those students who are in tenth grade and who have the Marquis de Sade as their counselor. The query makes these selections based on the //criteria// you establish.

A report is also just what it sounds like. You generate a report based on a query or form. For example, if I want a hard copy of all students who are in tenth grade and who have prison records, I would design a query to make these selections and then a report //based on the query// so I can print out the results.

These are the rudimentary elements of a database. There are many different ways to do things using a database, and, as you become comfortable with these basics, I am certain you will begin some interesting explorations and will develop some creative ways to use them in the classroom.

Views--There are basically two views in a database table (at least in Microsoft’s //Access// database program). The design view is the view you would use to create or change a table. The datasheet view is a view of all the records and their the fields.

Creating a new database-- In the same way that there are multiple routes to New York City, and there are several ways to do things in any computer program. However, while there are several ways to get to New York City, some means of transportation or routes are more efficient/shorter/scenic than others. After loading in //Access// click on “New.” On the right hand side of the window, click on “Blank Database.” This will pop up a window that will ask you two questions: 1) Where do you want to save this database and 2) what name you would like to use for this database. //Access// does not work like the other programs in Microsoft Office. You cannot, for example, do a “Save As.” So give some thought to where you want the file saved. My suggestion is either your home drive, a jump drive or the shared drive in the Teacher’s Folder (if you want to share access to your database). The next window will pop up. Select “Create Table in Design View.” This will bring up the [|Design View]. Now we are ready to create. Type in the fields that you want to exist in each record. To the right of each field is the //type// of field. From the pull down menu you can see that a field might be text, number, currency, yes/no, etc. The default is “text.” For beginners, leave this as “text.” We will get to one of the other options later. When you are finished entering the field names, think about any of the fields that will be the same for a number of records. For example, suppose you are making a database of vocabulary words from the reading curriculum. You have twenty-five words from //Things Fall Apart// and thirty words from //Pride and Prejudice//. In the field, “Book Title” you could simply type in the title for each record OR, you can save time by changing the “Default” option in the [|Design View]of the table. Scroll down to “Default Value.” Next to “Default Value” type in the title of the book (//Things Fall Apart//). From now on, whenever you enter a **new** record, the title //Things Fall Apart// will automatically appear in the “Book Title” field. is exactly what it sounds like. It is the value (text or numbers) that you type in. When you type in the fields for your record, notice that the Default Value for type of field is “Text.” [So let us jump ahead when you are busily entering vocabulary words associated with //Things Fall Apart//. You finished typing in the twenty-five words from this novel and are about to type in the thirty words from //Pride and Prejudice//. **Oh, Oh**. When you start to type in the words for //Pride and Prejudice,// the Default Value for the “Book Title” field is still //Things Fall Apart//. Before you start typing the words for //Pride and Prejudice//, go to [|Design View]and change the default value to “Pride and Prejudice.” If you are not comfortable with changing the [|Default Value], you can still make life easier by simply pressing “Ctrl” and the quotation mark key **//simultaneously.//** Doing this will copy whatever is in the field ABOVE the record you are currently working on. Try it and you will see what I mean.]

When you are satisfied with your fields, click the “X” to close this window. The program will ask you if you want to make changes to the table. The answer is “Yes.” If this is the first time you created a table for this database, the program will ask for a name. Choose a name including the word “table.” Later on you will find out why. The program will next complain that there is no defined. A Primary Key is an identification (alphanumeric) that is unique to each record. Let the computer choose, so just click “Yes.” **OH, OH**. Some people mistake the Primary Key as the order in which the records appear. The Primary Key has nothing to do with order (it does, but, for now, trust me, it doesn’t.). Do NOT change the primary key. You can really wreck things by messing with this, so do not….unless you like wrecking things.

[|Default Value], etc. To do that, click on “Table” and then on [|Design View.]Then you can go in an make your changes. Make certain to save your changes when you “X” out of [|Design View].

 2) Create a [|Form]to enter data. The advantage to using a Form to enter data is using a form saves time. If you enter data in a Table (using method #1 above), you will have to tab or enter day in each field. Imagine that you have ten fields for each record for our imaginary vocabulary database. You simply want to enter the vocabulary word, the book title and the grade level. That is three fields. But if you have ten fields, you will have to tab through the other seven fields to get to the next record, and you may not have data yet for those fields. (for example, other books in which the vocabulary word appears.)

To create a Form, click on “Forms” under objects in your database. Click on “New.” The next window will ask you (via a drop down menu) to choose [|what you wish to predicate this form on]. Choose the table you created. You will then be confronted with an ominous screen, light gray in color. This is actually what your form will look like. Notice that the fields in your table appear in another, smaller window. To choose fields, just drag the fields from this window to the form, placing them where you want them to appear on the form. In this way you can choose which fields you want to enter data in and which fields you want to skip for now. Later you can always add to the field or create another form to enter other data for each record. You will notice that when you drag a field over, two linked items are dragged to the form. The left most is the name of the field you created, for example, “Vocabulary Word.” The box next to it, //though it does not show in design view//, is the actual vocabulary word. You can change the width and height of both rectangles simply by clicking on one or the other and dragging the margins. You can also select the font and font size. When you are finished dragging and resizing the fields to your form, click out of the design view (You will be asked the first time to provide a name for this form. Do so. If you make subsequent changes to the form you will be asked to save the changes, and, again, choose “Yes.”)

Okay. You have a created a form and want to begin entering data. Click on “Forms” under “Objects” on your database window and click on the form you just created. Start entering data. Notice that if you set any [|Default Values], they are in effect. Please understand this bit of logic: To whit, **//that the form is simply a way of entering data into the table//**. If you click on the table you created, you will notice that the data you entered in the form //is in the table//. That is because, in creating the form, you predicated the form on the table you created, using the fields in the table. In essence, you are simply using the form to enter data into the fields in the table.

[|**Query**]A query is a very powerful way to select only those records that you want to select in order to see patterns in the data or to generate a [|Report]. Click on “Queries” under “Objects” in your database window. Click on “New.” The concept in working with Queries is basically the same as with Forms, although you cannot enter data into a table using a query, as you can in a form. A query is used to select data from a table and manipulate the information. After clicking on “New” the window listing your tables will appear. Highlight your table (if there is more than one table in this database), and then click on “Add” to add your table to the query. Click on the first column. Notice that a drop down menu appears listing all your fields. Clicking on a field will add it to the query. Suppose you want a query to select all those vocabulary words that are for 11th grade. In the first column choose the vocabulary word field, in the next column choose the grade level field, and in the third column choose the degree of difficulty field. So you now have three columns, each column representing the data from each field. Now the fun begins. Under the Grade Level Field Column, and in the Criteria row, type “11” or sometimes you need to type 11. Close the window (you will be prompted to give a name to the query. Do so, with something like, “Grade Level Query.” ) Now open the query you just created. You will notice that the query will select only those vocabulary words for 11th grade. But wait, (I am beginning to sound like a late night television advertisement, “But wait, if your order now…..”). Look at the power you have. Go back to the [|Design View] of your query. Keep the number 11 in the criteria row under the Grade Level Field Column, but in the criteria row under Degree of Difficulty Column type in “High.” Close the query in [|Design View], saving the changes. Open the query and Presto! (or hickety, pickety) the only records selected are those vocabulary words for eleventh grade AND of high degree of difficulty. As Clyde Barrow said, “Ain’t life grand?”[|**Report**]Creating a report is somewhat similar //in appearance// to creating a [|Form]. You will see the same gray gridded page. And you will drag over the fields you want displayed in the report. So here we go. Click on “Reports” under “Objects” in the database window. The popup window will ask you what you want to base your report on. Select the Query you created above, the one that selects vocabulary words for eleventh grade students with a high degree of difficulty (the words, not the students, hopefully). Drag over the fields to the report, close the window, give the report a name, and there it is. Open the report and you will see the vocabulary words for eleventh graders and only those words with a high degree of difficulty. You can use the report header and footer to create a nice looking paper which you can then print. Using the Yes/No option in designing a table is a very powerful tool for selection. By clicking on this field in a table or a query, you can select only those records you choose to select. Want to choose questions five, nine and thirty-three for a quiz? Simply click on the yes/no field for these records and, using a Report, you can generate a quiz at the click of a mouse.To filter means to select only certain records by choosing certain fields. Suppose you want a quick view at how many vocabulary words are in the list for eleventh grade. Click on the number eleven in the Grade Level Field in any record. Then click on the “Filter by Selection” icon (a projector bulb with lightning next to it). Only the eleventh grade words will appear. You have not delected the other records. You are simply filtering those you want to view. Click on the “Apply Filter” icon two icons over from the “Filter by Selection” icon to get back to a list of all the words.Filter by Form works like [|Filter by Selection] except you can filter on two or more fields. Click on the “Filter by Form” icon which is to the right of the “Filter by Selection” icon. A new window will pop up asking you to type the data in the fields that you want to filter by. For example, under the Grade Level Column, type in the number “12.” Under the Degree of Difficulty Column, type in “high.” Click on “Apply Filter.” You will now view only those vocabulary words for grade 12 and are a high degree of difficulty. Click on “Apply Filter” to get back to the entire list. Sorting a column is incredibly easy. Click on the column that you want to sort. If the field is text, it will sort alphabetically. If is numerical, it will sort numerically. Duh…. Then click on the “A-Z” icon to sort from low to high or “Z-A” to sort from high to low. That simple. Big Oh Oh If you are sharing a database with others, do not make changes without asking everyone else if is okay. Not asking first is very bad computer etiquette. You can add vocabulary words or book titles, etc. but to change the arrangement of the database, the order of columns or words is bad form. Think “we” not “I.”

I hope you find this document useful. Please feel free to contact me with any difficulties or clarifications. Mastering the use of a database is a sophisticated undertaking. Notice how many people do not use databases for just that reason. Take care, and support each other. Ralph