Yejun Wu
This brief tutorial records the steps I took to build the RideFinder database (in Access 2010). Please refer to the slides for the entity relationship model (or table structures) of the database. This tutorial does not address the various mistakes you might make. If you encounter an error, please either ask me, TA, your classmates, or search the Web. I may use terms different from the official MS-Access documentation. If you find any mistakes (including typos) in the tutorial or if you have better terms than I use to describe the objects on the interfaces, please let me know.
Activate MS-Access 2010, select "File" --> "New" --> "Blank database." This can be shown to you automatically when you initiate the software.
On the right panel (see figure below), the default database filename is shown as "Database1" and the default path is shown below the file name. Click the folder icon to select the path (or folder) of the database file you want to save, and specify the database file name (such as "mydb2012" here). Once the file name is specified, the file extension (.accdb) is added automatically. Click the "Create" button.
Click the "create" tab, an interface for defining the fields of Table1 is shown below:
Highlight and righ-click "Table1", the following interface is shown.
Select "Design View". In a pop-up window, specify the table name (e.g., Person), click OK.
Type in the Field Name and specify Data Type for the Person table. Field name must be unique, but only within the same table. Select field type from a menu. Use date/time for times; use text for phone numbers. I use number for Passenger Number. If AutoNumber is used, only one AutoNumber is allowed in one table. Below is the structure of the Person table.
By default, the first field is automatically specified as the primary key of the table and a "key" icon appears to the left of the field. However, the primary key can be specified by moving the mouse cursor to the empty button-like cell left to Passenger Number (that is the most top-left cell), right-click that empty cell, select "Primary Key"; a "key" icon appears to the left of the field of Passenger Number. (Note that empty cell and button-like cell are my terms, the official MS-Access documentation may use different terms.)
Click "File" -> "Save".
Highlight the Person table. In the Ribbon, click the "View" icon -> "Datasheet View" (see figure below).
Input data for each record. Click "File" --> "Save" or just the Save icon on the top left corner. Here are several records of the Person table.
On the tool bar, click "Create" --> click "Table" in the Ribbon. Table1 is created. Highlight and right-click Table1, select "Design View". In a pop-up window, specify the table name (e.g., Ride, while default table name is Table1), click OK.
Define fields and specify primary key (same as Step 2). Here is a screenshot of the fields of the Ride table.
Click "File" -> "Save", or just the Save icon.
Fill in records for the Ride table (same as Step 3). Highlight the Ride table. In the Ribbon, click the "View" icon, select "Datasheet View". Input data for each record. Click "File" --> "Save" (or just the Save icon) when done. Here are several records of the Ride table.
Take the similar steps to create the Booking table. Here is the structure of the Booking table. The primary key is "Booking Number."
Here are several records of the Booking table.
"Driver" in the Ride table is actually a Person entity which has the same attributes as a passenger. I should have used "Person Number" rather than "Passenger Number" in the Person table to be more intuitive. However, I intentionally use "Passenger Number" in the Person table for the two purposes: (1) distinguishing passenger from driver, and (2) demonstrating manually relating/linking two fields in two tables. "Driver" in the Ride table is a foreign key that refers to "Passenger Number" which is the primary key of the Person table. Note that "Driver" and "Passenger Number" are of the same data type (i.e., number), so I can relate/link them. Fields with different data types (such as text and number) can not be linked; if you try to do so, an error will be reported. In order to relate/link "Driver" with "Passenger Number", I need a copy of the Person table so that the relationship between the tables can be shown clearly. Click the "Show Table" icon, and click the Person table again and click "Add" to create a copy of the Person table, which is Person_1, as shown in the following screenshort, then click "close."
The tables can be enlarged and moved around (using the mouse) to display a better view.
To create/add a relationship between two tables, put cursor on one field of a table, click and drag it onto a field of another table. If you have created an incorrect relationship (or link) by mistake, it can be deleted by right-clicking the link and selecting "delete".
Here is a way to link "Driver" in the Ride table and "Passenger Number" in the Person table. Click and drag "Driver" in the Ride table (do not release the mouse) onto "Passenger Number" in the Person_1 table, then release the mouse. An "Edit Relationship" window pops up, click "create", the relationship between the two fields is created by displaying a link.
Similarly, create relationships between Booking table's Ride Number and Ride table's Ride Number, between Person table's Passenger Number and Booking table's Passenger Number. The created relationships are shown below.
If you happen to create a wrong relationship between two fields, you can either edit or delete it by right-clicking the link.
Click "Create" tab --> "Query Design" (the highlighted icon on the ribbon in the following figure):
Select the involved tables from the pop-up "Show Table" window (see the figure below), then define fields and criteria. My query is to find all the passengers who need a ride from "Baton Rouge" to "New Orleans" with cost less than $20.00. So I need all of the 3 tables. I need to highlight a table, then click "Add" to add that table to the query pane, which is a visual programming environment.
Note that when all the three tables are added once, the three tables are in a cyclic relationship (see the figure below), which may cause a query to fail to run. To remove the cyclic relationship, I added the Person table one more time, and got a copy of the Person table (which is Person_1); then removed the relationship between Person's Passenger Number and Ride's Driver (by right-clicking the link, then clicking "delete"), and added the relationship between Person_1's Passenger Number and Ride's Driver. Click "close" when done.
The bottom pane allows me to select the fields I want to display/project on the result of the query, and the conditions I want to restrict. I selected Person.Person Name, Ride.Origin, Ride.Destination, Ride.Cost, and and specified my restriction conditions as shown in the following screenshot. Again, my query is to find all the passengers who need a ride from "Baton Rouge" to "New Orleans" with cost less than $20.00. The notation "Person.Name" means the Name field of the Person table. If the "Show" checkbox is checked, that field will be shown on the query result.
Click File --> Save object as --> Specify query name (such as "MyQuery01"),
Click OK, the query name is changed to MyQuery01 (from its default name query1).
