Microsoft Access 2000 – Getting Started



Microsoft Access – Getting Started

Part I – Creating and Linking Tables

I. Starting Access

a. Open the Access Program

Start(Programs(Microsoft Access or

Start(Programs(Office(Microsoft Access

b. Select “Blank Access Database”

c. Select a Location and type in a file name then select “Create”

d. Window should look something like this:

Note: The left hand side has icons for the types of things you can create – Tables, Queries, Forms, Reports,…

II. Creating Tables

a. Let’s build our customer table using the “Create table by using wizard” option

1. Double click on the “Create table by using wizard” icon

2. Select “Business” then “Customers” under the sample table heading

3. Individually select the fields you want and hit the “>” key after each one to move it into your table

4. Once you have selected all the fields (columns/Attributes) you need, your screen should look something like this:

[pic]

Please notice that we did not include the Foreign Key to the region entity. That’s okay, we will do that later.

5. Select “Next” and Name your table (NOTE: convention is to always start your table name with “tbl” then Capitalize the first letter of every word and do not include spaces. For Example if I wanted my table to be named “Beach Bum” I would actually name it “tblBeachBum”). This is also the place where you can either let Access define your primary key for you or you can do it yourself later. Let’s let access decide for us and select “Next”

6. Now Access asks you what you want to do after it creates your table. Let’s select the “Modify the table design” option and click on “Finish”

Your screen should now look something like this:

[pic]

Things to Note:

1) The “key” symbol to the left of the Field Name “CustomerID” indicates that “CustomerID” is the Primay Key for this table

2) CustomerID Data Type is “autonumber” – Access will automatically give each new record or row of your table a new number ensuring that no 2 rows have an identical value for “CustomerID” – NOTE: We are going to change the type to text because it is good database design to only have things that are going to be added or subtracted as numbers.

3) The Field Names have no spaces in them (NOTE: Access will work with spaces in the field names however not all databases will allow spaces in field names. It is good database practice to not use spaces in field names.)

4) In the lower left hand corner you see a tab called “General” which has some field characteristics. This defines some characteristics of the field.

b. Let’s now create the region table using the “Create table in Design view” option

1. Double-click on “Create table in Design view” and your screen should look something like this:

[pic]

2. Start typing the first “Field Name” – in this case “RegionName”

3. Tab to the Data Type and select one from the drop down list – NOTE: You may only select one from the list! – In this case since the Region Names are names, we should select “Text”

4. Tab twice to get to the next row (new column name)

5. Let’s type in “Description” and once again select Text data type.

6. Now to set the Primary Key – select anywhere in the “RegionName” row and then select the “Key Icon” from the menu bar (if you do not have the menu bar – select: Views(Toolbars(Table Design from the menu bar).

7. This changes the Indexed characteristic to “No Duplicate” an integrity constraint for the primary key.

8. Now let’s set the maximum length of the “RegionName” to 12 characters (Click on the “RegionName” field name and then in the lower left tab – set the “Field Size” value to 12)

9. Set the maximum length of the Description to 255 (this is the maximum number of text characters any field can have in Access).

Your table should look something like this

[pic]

10. Now select: File(Close and Access prompts you for a table name. Let’s call this table “tblRegions”.

Congratulations! You have just created 2 tables for your database – Now create the other 3 that are part of our E-R diagram.

Wait – first we need to add the Foreign Key “RegionName” to our Customer Table “tblCustomers”. To do this, Right-Click on the table “tblCustomers” and select “Design View” or select “tblCustomers” with a single-click and then select the “Design” Icon from the toolbar.

Select the First free row and set the “Field Name” = RegionName, Data Type = Text, and set the Field Size = the same value as we had for the Primary Key “RegionName” in the “tblRegions” table. Make sure that the general criteria under “Indexed” is set to either “No” or “Indexed (Duplicates OK)”.

Now we are done with these 2 tables – Now create the remaining tables from E-R Diagram. Don’t forget to set the Primary Keys (NOTE: To Set a Composite Primary Key you must first select all the Fields that are part of the composite primary key – use the CTRL key to select multiple fields – before you select the key icon from the toolbar).

Your screen should now look something like this:

[pic]

III. Linking Tables – Creating a Relational Database

a. Now that we have our 5 tables, we need to link them. To do this we need to select: “Tools(Relationships” from the menu and add the 5 Tables that we created and select “Close”.

Your screen should look something like this:

[pic]

b. Now let’s move these tables around on the screen to get them arranged similar to our E-R Diagram.

c. Now to create a relationship. To create the “Customer – M:1 – Region” relationship, we click-hold-drag the Primary Key in the Region Table, “RegionName” to the Foreign Key in the Customer Table “RegionName” and let go. This should yield a screen similar to the one shown below (NOTE that a pop-up relationships window should have appeared):

[pic]

d. Your screen should display a relationship from “RegionName” of table “tblRegions” to “RegionName” of table “tblCustomers”. At the bottom, you should note that the Relationship Type is “One-To-Many” which matches our E-R diagram automatically (Access figures out what type of relationship you have based upon the properties you identified for each field when you created it). Before we “Create” our relationship, we need to select the “Enforce Referential Integrity” option. This will then make the “Cascade Update” and “Cascade Delete” options available which we won’t work with for this exercise. “Enforce Referential Integrity” means that when you enter a value for “RegionName” in the Customer Table, Access will check to make sure that this value exists in the “RegionName” field of the Regions Table. This is part of those “Integrity Constraints” that we mentioned as part of relational databases.

After you select create – your screen should look something like this:

[pic]

e. Now create the other relationships on our E-R diagram making sure to “Enforce Referential Integrity”

Your finished diagram should look something like this:

[pic]

Congratulations! You have just completed the most important part of relational database – you have properly design it using an E-R diagram and set up the logical database structure in your database software program.

- - - - END OF PART I - - -

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

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

Google Online Preview   Download