Opening a New Database - Gunadarma



Opening a New Database

Before you start, go round up some of your music CDs, cassettes, eight-track tapes, vinyl recordings and reel-to-reel tapes you have collected over the years.  You young people will be slightly disadvantaged in this regard.  This tutorial will use your music collection as the focus of your first database in Access 97 or 2000. You can even insert a CD into your CD player while you work on this tutorial.  Creating and using databases is not rocket science.

There are numerous options for opening a new document.   For this tutorial, we will use the "Start" button on the Windows toolbar and scroll up to "New Office Document". As in other Office products, Access allows all kinds of options for opening a new document. You'll determine your preferences soon enough.

If you just installed Access, the first time you open the application, the Office Assistant may appear on your screen.  Click on "Start using Microsoft Access" to start the program.  In Access 2000, you would get this window.  However, the window below will appear if you started Access with the Start => New Office Document procedure.  Either way, choose "Blank Access database" and move on.  We don't want to use the wizards at this time.  By the way, this may look slightly different with Access 97.  You get to use your intuition if things aren't exactly the same.  

If the "New Office Document" window opened because you used Start => New Office Document, select "Blank Data Base" from the choices you have and click OK.

[pic]

The File New Database window opens, and you will be asked to name your database. Select or create an appropriate folder and give the database a name by entering text over the highlighted db1.  Once again, this window is a little different in Access 97.

[pic]

Once saved, you will see the Database window which displays all the different objects you can create in a database. The object with which you will become most familiar is the Table. Most people recognize databases as tables of data arranged in some organized format.  

Click "Create table in Design view" in Access 2000, or select the Table tab and hit "New" in Access 97, and you'll see yet another set of choices.

There are really only two appropriate choices for you: "Datasheet View" and "Design View". "Datasheet View" looks like a traditional table…almost like a spreadsheet.  If you've used Microsoft Works database application, you'll recognize the "Datasheet View" as a table where you can enter data into cells.  However,  "Design View" is where you really want to create your data base fields, because you can determine data controls easier. So, select "Create table in Design View" and hit New. You'll get to "Datasheet View" soon enough. If you see this window below, just click on Design View and hit OK. 

[pic]

If you need a break, take one now.  When you return, click on the link below to move on to "Creating Tables in Design View" and more database fun.

Creating Tables in Design View

Creating Tables in Design View

You have created a new database in the first part of this tutorial, and you've chosen to create a table in Design View.  Below is a portion of what you should have on your screen.  It's a view we call "Design View" because it's where we can control most of our design parameters.  The other view is "Datasheet View" and you can get there by going to the "View" menu and selecting "Datasheet View" from the choices.  We'll stay in "Design View" for now.  I'll tell you when you can switch views. ;-^) 

[pic]

So that you will see how a relational database works, we're going to create two simple tables and then create a relationship between them. Since the first table we create will be about the artists in your music collection, that table will be called the Artist Table.  A Table is an entity (thing) that has attributes (fields) that fit together.  The second table will be the Recordings Table.  Its attributes will be about the characteristics of the specific recordings in your collection.

Now you're ready to enter some Field Names.  A field is a specific category that should have a name that makes sense.  In a music collection database, "Artist" or "Artists" would be good field names. 

*Just a  Note: It's important to realize that just about everything in Access deals with objects that can be clicked on or selected. That means that you make things happen by selecting things and pushing buttons in toolbars. The novice must remember the first law of "Technodynamics": For every action, there is an equal but opposite undo action. Use "Ctrl+Z" whenever you need it. Also, you may discover that there's usually a button somewhere on the toolbar that takes you back. Your intuitive gifts will come alive when using most Office products and Access is no exception. Enjoy the fact that you have these gifts, and thank Bill Gates and his programmers for being so perceptive as to recognize that people think this way.

• The cursor is patiently (patient cursor is a good compressed conflict) waiting for you to enter something in the "Field Name" column. Enter "Artist(s)" and press Enter or Tab.

•  Your cursor moves to the "Data Type" column and selects "Text" as your default type of data. That's the most common type of data you'll use.  Below is what you have so far.

[pic]

•  Just hit "Tab" or "Enter" for now and watch the cursor move on to the "Description" column.

• You may ignore the "Description" column. It is simply what it says it is. Type a description if you choose and move on by pressing "Enter or "Tab" once again.

• Your cursor moves on to the "Field Name" column in the next row and awaits your command. Enter "Genre"  and move on.

•  Hit "Tab" or "Enter" making "Text" your default "Data Type" and stop right there.  

• We're going to make a simple pop-up menu called a "Lookup", and we'll use a wizard to help us.

• Just click in the Data Type column next to the word "Text" and scroll down until you see "Lookup Wizard" in the drop-down menu.  The image at the right is what it looks like.

• Click on "Lookup Wizard" and you get to make some easy decisions about the music you listen to.

[pic]

• In the Lookup Wizard window, click the lower radio button which will enable you to type in the values (in this case the kind of music) you want and hit the "Next >"  button.  The window below shows the kind of music I have in my music collection.

[pic]

• Hit "Next>" to go on.  The next window is pretty intuitive.  Choose a name for the Lookup Table and hit "Finish".

•  How about saving this document before the whole system crashes? Control + S will do the trick. You'll be asked to give your table a name. You can use the default name "Table 1" if you want. This is just practice. I'd call it "Artists Table". 

• You'll think you know what you're doing until you hit Enter, but Access will ask you if you want to define a Primary Key. Just say "No". We'll do that next.

Defining a Primary Key

At this point, you've created two fields.  We'll create one more called the Primary Key.  A Primary Key is a field that identifies each record in your table as unique.  In order to establish relationships between tables, your table needs a Primary Key.  Access can set the Primary Key for you, or you can do it yourself.  In this case, we'll do it ourselves.

• In the next "Field Name" column, enter ArtistID and press Tab to get to the next column.  Click on the little triangle in the "Data Type" column,  scroll down and select "AutoNumber".  While your cursor is still in that record, go to the "Edit" menu and click on "Edit => Primary Key".  Now you've established a unique number that is automatically generated for you that makes each artist's record you enter unique.

• "Big deal," you say.  You'll see why after we create the next table and link the two together.  Below is what you have so far.

[pic]

Adding Records

Before we take a break, let's add some records to our new table.  In "Design View", go to "View => Datasheet View" in the menu bar.  Now, simply enter the name of a few artists in your music collection and choose their Genre of music from your drop-down menu.  You'll notice that the AutoNumber feature works great, establishing a new number for each new artist you enter.   Below is what mine looks like so far.  The ArtistID field with its AutoNumber and Primary Key makes each record unique.

[pic]

Take a break if you like, but don't forget to save.  When you come back, we'll create the next table and link them together.

Go on to Making the Recordings Table.

Making the Recordings Table

You have come a long way.  Now we're going to make a table that keeps track of the recordings in your collection.  In the "Database Window", Choose "Create table in Design view" again.  Hit "New" and follow the steps we did in the first lesson.

[pic]

Below are the names of the fields you will need.

[pic]

You'll notice that the RecordingNumberID is the Primary Key.  Just as we did in the Artist Table, you can establish the Primary Key last.  Essentially, the RecordingNumberID is what I use to make each record unique.  I recommend that you use the Access "Auto Number" feature as we did last time we established a Primary Key. If you're really serious about your music, you can use the Recording number issued on the CD or recording cover or case.  If you choose the latter, your "Data Type" will be "Text" as you see in the example above, rather than "Auto Number", but it will still need to be your Primary Key as it is unique.

• Enter "Titles" in the "Field Name" column, hit Enter or Tab, and leave the "Data Type" as Text.

• Do the same for "Recording Label".

• Let's use the Lookup Wizard for "Format" so we can make a list of values to select.  The values I have are CD, Eight-Track, Vinyl, Reel-to-Reel, Cassette, and Digital Media.

• If you're not sure how to use the Lookup Wizard, go back to the last lesson.

• For the ArtistID, choose "Number" for your "Data Type".  While "ArtistID" won't be the Primary Key in this table, that's the field that will be linked to the Primary Key in the Artist Table. Notice the two fields have the same name, yet they are in different tables.  In Access, we  establish relationships by identifying matching fields in two or more tables.

• Finally, for the "Artist(s)" field, just use Text as your "Data Type".  You can create a value list with the Lookup Wizard as you have done before, but you would need to modify the list when you add a new artist to your collection after you get paid next month.  Better to enter the artists as text.

• By now, you've realized that no one can tell you everything there is to know about Access.  Experimenting with Access truly expands your Zoped (Zone of Proximal Development).  Fool around with it.  I also recommend getting several books on Access.  Probably the best I've seen for beginners is  Teach Yourself Access 97 Visually by IDG books.  Most quality brick-and-mortar bookstores will sell books on Access 97 and 2000 in their computer section.  Running Microsoft Access 97 is the one I use for the tough questions.

• Save your table as Recordings Table.

Making the Primary Key for the Recordings Table

To establish the Primary Key for the Recordings Table, make a new Field Name called RecordingNumberID as I have done.  Press Enter or Tab and choose "Auto Number" as your "Data Type. Hit the little key icon in the toolbar or select "Edit => Primary Key", and you've got a Primary Key for this table.  Save your table. 

Let's add some records to our new Recordings Table.  Click on "View => Datasheet View" and you'll switch to "Datasheet View" where you may enter your records.  Enter a few records and save your table.  You must enter the correct ArtistID in the Recordings Table.  If you don't, you will have problems creating a relationshipe between your two tables.  

By the way, there are icons in the toolbar that allow you to switch views.  More Zoped.

Now, we're ready to establish a relationship between the two tables. 

Forming Relationships

Save and close all your tables and make sure the Database Window is open.

Click on the little relationships icon [pic]in the toolbar or go to "Tools => Relationships..." in the menu bar.  A large, blank window opens.  Now you will see another little icon [pic]in the toolbar.  You can click that icon or go to "Relationships => Show Tables..." to select the tables you want to place in the Relationships window.  Below is what you will see.

[pic]

• Choose each table by selecting and clicking on the "Add" button once for each table.

• The last step is to create the relationship between the Primary Key in the Artists Table and the matching field in the Recordings Table. You will see this screen below once you've added tables to the Relationship window.

[pic]

• Click and drag the ArtistID field in the Artists Table to the matching field (ArtistID) in the Recordings Table.  You just drop the one field on top of the other.  Access essentially does the rest. 

• Below is the new window you will see. 

[pic]

• Click on the option that allows you to "Enforce Referential Integrity" as well as the two boxes below that one.

• You now have a One-to-Many relationship between your two tables.

• Click OK and you're done.  Make sure you save your work.  Below is what you will see.

[pic]

Relationships in Access can get messy and confusing!  (Not like in real life, eh?)  For an explanation of the Primary Key concept along with examples and illustrations, see the website "Access to Relationships"  in this series of tutorials. 

Now, you can enter records into either of your Tables.  The next lesson will show you how to make a form for easier data entry.  For now, you've earned a break.

Go on to Creating a Form.

 

Creating a Form

We have created two tables in our Music Database.  The first table is called Artist and the second is named Recordings.  We've connected the two tables by creating primary keys and establishing a relationship between the primary key field in the Artist table and an identical field in the Recordings table.  Now, we return to the Database Window and create a form.

[pic]

Click on the Form Tab,  select "Create form by using wizard" and click on "New".

[pic]

Select "Form Wizard" and click in the pull-down menu to choose "Artist Table" from the choices.  Click OK.

[pic]

When you see the window above, you need to add all three of the fields for your form.  You can add them all by clicking on the "double arrow".  The results are shown below.

[pic]

You'll need only three fields from the Recordings Table.  When you see the window below, click on the pull-down menu to select the Recordings Table.

[pic]

Next, choose "Titles", "Recording Label", and "Format" by selecting each one and clicking on the "single arrow". The results are shown below.

[pic]

Click "Next" to move to the window you see below.

[pic]

Just click "Next" three times and then click "Finish" to complete your session with the Wizard.  If you followed these directions, you will probably have a form similar to the one below where you can enter your own records.

[pic]

Explore your new form.  Enter some records and enjoy your new relational database.  You need to realize that you can move between records on both the form and the Recordings Table subform. 

The next part of this tutorial will show you how you can use Access to locate data quickly and efficiently.  When you're ready, click below to move on.

Searching the database.

 

 

Databases have been shown to be powerful tools when used in conjunction with Hilda Taba's Inductive Strategy. We can see the learning potential when we examine the Concept Formation phase in a database lesson (brainstorming data and building the retrieval chart). By creating an opportunity for students to research and enter data into a database, we infuse technology into the curriculum. The materials we use in this procedure are "Retrieval Charts" which are constructed in class and "Forms" which are set up by the teacher beforehand. Below is a review of the procedure for creating a form in Access.

1. Open the database.

2. In the Database Window, click on the Forms tab and hit New.

3. Choose "Form Wizard" to make your form.

4. Follow the directions given to you by the Wizard.

5. If you want to change the appearance of the Form, you can do that after the form is finished by simply clicking on "View" and choosing "Design View" in the main menu.

6. Data entry is much simpler for students, especially elementary students, when using a form because the teacher chooses which fields are to be displayed on the form.

Because students may be slow at the keyboard or they may make many spelling errors, often we need to help them enter data. We can do this by creating pull-down menus for data entry. Below is a review of how to do this.

 

1. If the database is not open, open it.

2. Go to the Database Window and click on Tables.

3. Open a Table you have for your database.

4. When your table opens, you will be in Datasheet View.

5. Make sure you don't have the Form you created in the "Form" review section above opened. If you do, simply close the Form you created before you proceed to the next step.

6. Go to the Windows menu and select "View" and then choose "Design View".

7. Your "Design View" window will appear.

8. In this view, you may change the rules for data entry.

9. To change the selections in a drop-down menu or to add drop-down menus to your database, simply follow the instructions below.

10. In the Data Type column, click on the item you want to change.

11. Click on the Lookup tab at the bottom of the "Design View" window.

12. In Display Control choose Combo Box from the choices.

13. In Row Source Type choose Value List from the choices.

14. In Row Source enter the choices you want to give your students followed by a semi-colon with no spaces. You may create entries with spaces, but before and after the semi-colons there must be no spaces.

15. Select "Save" from the "File" menu and you're done. Go back to "Datasheet View" and see how it works.

16. A quicker way to do this is to use the Lookup Wizard in the Data Type pull-down menu while in Design View.

After students have entered data and you feel the data is accurate, you and your class will be ready to put the database to the test. You do this by searching, filtering, and querying the database so that your students gain information and make important connections. Below is a review of these procedures.

1. The simplest search is a "Find" procedure. Open a table in your database and make sure you're in Datasheet View.  Click the binoculars in the toolbar  [pic]  or go to the Windows menu and choose "Edit" and select "Find" or press Ctrl +F. The dialogue box that opens will give you the options that allow you to find something in the whole database. Uncheck the "Search only the current field" check box to find a word in the entire database. You can also "Match" any part of a field. You'll need to select these options on your machines for each "Find" you perform.

2. Sorting is also a way to search. Open a table in your database and make sure you're in Datasheet View.  To sort by ascending or descending order, click in the field you want and click on "Records" and choose "Sort Ascending" or "Sort Descending." You can sort two or more fields if you place them adjacent to one another. Select the fields you want to sort. Choose "Sort Ascending" or "Sort Descending" and the sort will be carried out with the leftmost field sorted first. The "Sort" icons on the toolbar eliminate the need to use the "Records" menu bar item. [pic]

3. Filtering is even more powerful. To filter the database, select a word in your database. Click on "Filter by Selection" in the toolbar [pic]. If you select a word in the middle of the entry, all words that match are filtered for you. Remove your filter when finished by clicking the "Apply/Remove Filter" icon [pic].

4. Filtering by Form is more powerful yet. Click on "Filter by Form" [pic]and then select or enter the words you want to filter. By surrounding the word you want to filter with asterisks, you can narrow your search significantly. Clicking "Apply/Remove Filter" icon [pic]clears the "Filter by Form" for new searches.

5. Queries are similar to "Filtering by Form" but you can select only the fields you want your students to see, and you can save the Query. Use the Query Wizard for a more general view which you can "Filter", or use the "Design View" query for more specifics. In either case, you may use the "Filter by Form" procedures for more specific searches. To run a Query immediately after creating it, click on the Exclamation Point in the toolbar. To run a query once it's saved, simply go to the Database window and click on the Query tab and double click on your query.

Making a "Report" of your database is easy. This is probably the most intuitive portion of database use. In the Database Window, simply click on "Reports" and use the Wizard to help you design your Report. You can use "Design View" to do this as well, depending upon your emerging interest and developing zoped with regard to databases.

After you complete your Report, you may, of course, print it, but you also have the option of making it into a Word 97 or 2000 report by clicking on the Office Links icon on the toolbar. After a few moments, Office will have translated your report to rich text format which can be saved, opened, copied, pasted and so on. Pretty cool, eh?

[pic]

 

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download