Yejun Wu
This brief tutorial records the steps I took to build the RideFinder database (in Access 2007). 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 (or the class on Moodle) or search the Web. I may use terms different from the official MS-Access documentation. If you find any mistake 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 2007, Click the Office button at the top-left corner --> click "New" --> click the "open folder" icon right to the file name box to specify the folder where the database file will be saved --> click the "OK" --> a file called mydb.accdb is to be created under the specified dicrectory --> click "create".
Click the "create" tab, an interface for defining the fields of Table1 is shown:
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. Here 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 the Office button -> click "Save".
On the tool bar, click "Create" --> click "Table" in the Ribbon. Table1 is created. Highlight Table1, click "View" in the Ribbon -> "Design View". In a pop-up windows, 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 the Office button -> click "Save".
Fill in records for the Ride table (same as Step 3). Highlight the Ride table. In the Ribbon, click View -> Datasheet View --> input data for each record. Click Office --> Save 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.
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. 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 "Show Table" again, 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 linking "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 built by displaying a link.
Similarly, create relationships between Booking's Ride Number and Ride's Ride Number, between Person's Passenger Number and Booking's Passenger Number.
Click Create --> Query Design:
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 "College Park" to "New York" 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 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 "College Park" to "New York" 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 Office -> Save -> Define query name (MyQuery01), click OK.
