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. ;-^) 

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.

•  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.

• 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.

• 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.

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.

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.