Browse Instructions
How To Create a Database With Microsoft Access 2003
Posted By: wndschnberg;
Category: Computer, Internet & Information Technology;
October 26, 2008
Author Yuhao Lin;
Microsoft’s Access 2003 is the latest version of the simple Windows-based database application. While Access is not a sophisticated database technology, it is intuitive and therefore suits the common computer user. In this instruction, you will create a small database to track volunteer information with Access. No knowledge of relational databases or the structured query language (SQL) is necessary to follow this instruction.
Click Image To Enlarge
1
Start Access 2003 by selecting it from the Windows Start Menu. Access shows up:
Click Image To Enlarge
2
In the section to the right of the screen, click on the Blank Database option:
Click Image To Enlarge
3
The File New Database dialog box comes up:
Click Image To Enlarge
4
The database will be stored on a computer as a file. Browse to the directory where you want to save the database file; also type in the file name of your choice in the File Name textbox. In this tutorial you will name the file volunteer.mdb. Then click on the OK button
After you have created the new file, you are ready to manipulate the database:
After you have created the new file, you are ready to manipulate the database:
Click Image To Enlarge
5
Double click the Create table in Design view link on the top center of the screen. You will see a new form:
Here you will define the structure of the database table.
This form itself resembles a database table, but do not confuse it with an actual table. This form is merely for you to define the column (or field) names of the table. There are 2 pieces of information required to define a field: you need to give the field a name and a data type; you may also wish to provide a description for the field, although that is not required.
Here you will define the structure of the database table.
This form itself resembles a database table, but do not confuse it with an actual table. This form is merely for you to define the column (or field) names of the table. There are 2 pieces of information required to define a field: you need to give the field a name and a data type; you may also wish to provide a description for the field, although that is not required.
Click Image To Enlarge
6
Imagine that we need to store five pieces of information about each volunteer: first name, last name, social security number (SSN), phone number, and birth date.
Now you will add each field to the table structure on this form, starting with first name. Click on the first blank textbox in the Field Name column:
Now you will add each field to the table structure on this form, starting with first name. Click on the first blank textbox in the Field Name column:
Click Image To Enlarge
7
Type First_name in the Field Name column:
Hint: It is standard practice to name columns in the form of Firstword_secondword_..._nthword.
Hint: It is standard practice to name columns in the form of Firstword_secondword_..._nthword.
Click Image To Enlarge
8
Tab to the next column titled Data Type. This is a drop down list containing the data types that you can choose from. Since the first name will contain alpha letters, the data type that best suits it is Text, which you will select from the drop down list:
Click Image To Enlarge
9
At the bottom of the form, there are additional options that you should pay attention to:
The Field Size property specifies the maximum number of characters the field can hold. Leave the value to the default of 50.
The Required property specifies whether a value is required to be entered in this field when you add a new record. In this case, we do need the first name of each volunteer, so change the value to Yes.
The Field Size property specifies the maximum number of characters the field can hold. Leave the value to the default of 50.
The Required property specifies whether a value is required to be entered in this field when you add a new record. In this case, we do need the first name of each volunteer, so change the value to Yes.
10
Congratulations, you have created your first field in the table! Hopefully you have understood the steps you took to accomplish this task. You will repeat the same steps—albeit filling in the appropriate values—to create the rest of the fields: last name, social security number (SSN), phone number, and birth date.
Note: You will not provide a description for any field in this instruction.
Note: You will not provide a description for any field in this instruction.
Click Image To Enlarge
11
To get you started on creating the last name field, now click on the second line in the Field Name column. Type in the name Last_name:
Click Image To Enlarge
12
For the last name field, you will choose the same Data Type, Field Size, and Required value as you did for the first name field. When you have filled in all the properties for last name, the form should appear as shown below:
Click Image To Enlarge
13
Create the SSN field as such:
a. Field Name: SSN
b. Data Type: Text (the only field that should have the data type Number is if the value would be required for calculation, which SSN does not quality)
c. Field Size: 9 (to restrict the entry to 9 digits, which is the standard for all SSNs)
d. Required: Yes
a. Field Name: SSN
b. Data Type: Text (the only field that should have the data type Number is if the value would be required for calculation, which SSN does not quality)
c. Field Size: 9 (to restrict the entry to 9 digits, which is the standard for all SSNs)
d. Required: Yes
Click Image To Enlarge
14
Create the phone number field as such:
a. Field Name: Phone_number
b. Data Type: Text (the only field that should have the data type Number is if the value would be required for calculation, which phone number does not quality)
c. Field Size: 50
d. Required: No (perhaps not all volunteers have a phone number)
a. Field Name: Phone_number
b. Data Type: Text (the only field that should have the data type Number is if the value would be required for calculation, which phone number does not quality)
c. Field Size: 50
d. Required: No (perhaps not all volunteers have a phone number)
Click Image To Enlarge
15
Create the birth date field as such:
a. Field Name: Birth_date
b. Data Type: Date/Time
c. Required: Yes (there may be a law that dictates only people over age 14 may volunteer)
a. Field Name: Birth_date
b. Data Type: Date/Time
c. Required: Yes (there may be a law that dictates only people over age 14 may volunteer)
Click Image To Enlarge
16
The Primary Key of a table is one or more fields that together uniquely identify a record. In our case, say that we have 9000 volunteers in our database; we need a way to identify each volunteer. Which fields would make a good candidate for the Primary Key? Remember, the primary key in a table must contain a unique value for each record. So, the first name and last name combo cannot guarantee a unique value (there maybe 2 Joe Smiths); neither can the birth date or phone number fields. The only field that can produce a unique value for every record is the SSN field, since every US resident has a unique SSN. So, the SSN field will be our Primary Key.
Follow these steps to make SSN your primary key:
Highlight the SSN row in the Table Creation form:
Follow these steps to make SSN your primary key:
Highlight the SSN row in the Table Creation form:
Click Image To Enlarge
17
Right click anywhere in this row, and select the Primary Key option:
Click Image To Enlarge
18
Now the SSN field will have a key to the left:
Click Image To Enlarge
19
Next, click on the Close button of the Table Creation form (not the Close button of the Access window!).
You will then be asked if you want to save the table. Click Yes. Now you will be given the opportunity to name the table:
You will then be asked if you want to save the table. Click Yes. Now you will be given the opportunity to name the table:
Click Image To Enlarge
20
Name the table Volunteer and click OK.
Hint: It is ok to give the same name to the database and a table.
Now you should be taken back to the Database window with the Volunteer table, which we just created.
Hint: It is ok to give the same name to the database and a table.
Now you should be taken back to the Database window with the Volunteer table, which we just created.
Click Image To Enlarge
21
Now that you have created the Volunteer table, you can add to or edit its content:
Double click on the Volunteer table, which takes you to the table with no data yet:
Double click on the Volunteer table, which takes you to the table with no data yet:
Click Image To Enlarge
22
Enter the following values in the first record:
a. First name: John
b. Last name: Smith
c. SSN: 999999999
d. Phone number: 395-433-3443
e. Birth date: 1/23/1990
a. First name: John
b. Last name: Smith
c. SSN: 999999999
d. Phone number: 395-433-3443
e. Birth date: 1/23/1990
Click Image To Enlarge
23
Click on the next record; notice that since we entered all valid data, no error message was encountered.
In the following steps, you will enter invalid data into the fields. Note how Access performs error checking:
For the second record, enter all of the same data as you did with the first record. When you finish and try to click outside the record, you get an error message stating that you have entered duplicated data in the Primary Key:
In the following steps, you will enter invalid data into the fields. Note how Access performs error checking:
For the second record, enter all of the same data as you did with the first record. When you finish and try to click outside the record, you get an error message stating that you have entered duplicated data in the Primary Key:
Click Image To Enlarge
24
Click OK and Press the Escape key on the keyboard to clear the second record.
Enter more invalid data into the second record. This time, enter valid data for all fields except Birth date, for which you will enter 12/35/2000. Because December does not have a 35th day, Access will return an error message stating the date entered is not valid:
Enter more invalid data into the second record. This time, enter valid data for all fields except Birth date, for which you will enter 12/35/2000. Because December does not have a 35th day, Access will return an error message stating the date entered is not valid:
25
Click OK and Press the Escape key on the keyboard to clear the bogus value in the Birth date field.
Enter any valid date for the Birth date field.
Close the table, which automatically saves any change made since opening the table.
Enter any valid date for the Birth date field.
Close the table, which automatically saves any change made since opening the table.
26
This instruction has walked you through the steps of creating a simple Access database with one table to track client information. As you have seen, databases can provide extremely useful functionality for real world businesses. When the database is used in conjunction with a front-end application to display and enter the data (which is out of scope of this instruction), the combination brings out the true power of modern business software.
Only registered users can add comments
