Creating a Database

Return to Tutorial homepage.

Create the Database | Create Tables  

Create the Database

1. Since you'll be creating a new database, when you've started Access, click on the Blank Access Database option.  Then click 'OK'.
   
2. To create a new database you must provide a name and specify a directory for the database (.mdb) file.  Then, click on Create.
   
   

Create Tables

1. When the database has been created, it initially exists as a file, an empty shell.  The database becomes useful when we create so-called database objects within it.   In Access, the starting point for creating database tables, queries, forms, reports, macros, and modules is known as the 'Database Window', shown here.  To create a particular object, click on the tab for that object on the left (here, 'Tables').
   
   Access provides three options for creating a new table.  Create table by using wizard allows one to create a variety of common databases (e.g. customer, employee, order, etc.).   Create table by entering data allows one to quickly create a tabular arrangement of data without first creating the table's design.  The third, Create table in Design view provides the greatest amount of control over the design of the table.  
   
   To create a table in design view, do one of the following:
   
   1. Double-click on Create table in Design view, or
   2. Click on New, and then select Design View in the New Table dialog box:
      
      
2. The design of a table requires the specification of four elements:
   1. The name of each column
   2. The data type of each column
   3. The column(s) whose value(s) will be unique for each row in the table (the Primary key)
   4. The name of the table.
3. Type in the Field Name, Data Type, and description for each field you wish to have in your table.  Notice that for each data type there are additional Field Properties you may set.  In the figure below, the CD_Category field is marked as the current one (by the black triangle on the left).  Therefore, the Field Properties pertain to the CD_Category field.  
   
   In the Field Properties area we see that the following properties have been set:
   • The maximum length of the field is 30 characters.
   • The Caption property allows you to define a default label for this column on a Form.
   • The Validation Rule property allows you to define more precisely restrictions (constraints) on the values that may be inserted into this column. For example, to reduce variations in spelling, we are requiring that any value placed into the CD_Category column be spelled as one of the following options: Rock and Roll, Country, Jazz, Classical, Other.
   • The Validation Text property allows you to define an error message the user will receive should he or she enter in a value that is not valid according to the Validation Rule property.

   You should set the field properties appropriately for the other fields as well.

4. Choose one or more fields to be the Primary Key. You do this by clicking in the grey area to the left of your field definitions (holding the CTRL key and clicking selects individual fields if the Primary Key is to consist of more than one column.
5. From the Edit menu, choose Set Primary Key
6. From the File menu, choose “Save As...” and give your table a name.
7. Close the table by either choosing “Close” from the file menu, or clicking on the Close button in the upper right-hand corner of you window.
   
8. Repeat this table creation process to create another table called TRACKS with the following columns:
   • Track_No (Data type:  Number,  Field size: Integer)
   • Track_Title (Data type: Text, Field size: 80)
   • Track_Duration (Data type: Date/Time,  Format: Short time)
   • CD_ID (Data type: Number, Field size:  Long Integer).    CD_ID in the TRACKS table is what is known as a Foreign Key.  It is a column (or columns) within one table that is also a Primary Key column(s) in another table.   Within Access it is important to note that if the datatype of a Primary Key is Autonumber, then the data type of any Foreign Key that references that Primary Key must be Number, and the Field size must be Long integer.

Create Relationships

1. Fundamentally, relationships within a relational database systems are represented by using Foreign Keys to establish associations between data in one table with data in another.   However, Microsoft Access likes to have a little bit more explicit information about what columns are foreign key columns, so that it can make life easier for you when you create forms, and so that it can better enforce data integrity within the database.  You provide this information to Access via the Relationships... view.  
2. Get a graphical view of the tables and relationships by clicking on the “relationships” button on the toolbar, or by selecting “Relationships...” from the Tools menu.
   
3. In the Show Table window, click on the Add button (for each table) to place a graphical representation of the table into the relationships diagram.  Click on Close when finished to dismiss the Show Table window.
   
4. To tell Access that a particular field in one table represents a field from another table, click and drag from the field in one table to its counterpart in another field.  In the following image, I clicked on the CD_ID of the CD table, and dragged it to the CD_Id field of the Tracks table.  Access then offers me the opportunity to edit that relationship.
   
   The relationship between the CD table and the Tracks table is a one-to-many, since one CD is associated with (contains) many tracks, while each track is located on precisely one CD.  Checking the box to Enforce Referential Integrity means that you may not enter a Track whose CD_ID value does not match one of the existing CDs.  In other words, each Track must be associated with a known CD.   Click Create to complete the creation of this relationship.
5. When the "relationship" is created, Access will show a line between the participating columns.  Note that this graphical representation is simply a convenience.  The actual relationship is established by the data values within the tables themselves.
   

Return to top

Next Section: Creating Forms