Create Simple Database

LSP 121

Activity 1

Creating a Simple Database

Let’s create a single-table database. While single-table databases do not take advantage of a real relational database (such as Access), they are a good starting point.

Open Access and create a new blank database. Name the database file Activity 1 and click on the Create button. As mentioned during lecture, when you first create a database, Access automatically creates the first table (calling it Table1) and pops you into Datasheet View. It will look like this:

We don’t want to use the Datasheet View just yet. So close Table1 by clicking on the X opposite the name Table1. Now click on the Create tab and then click on Table Design. You should now be in Design View. It should look something like the following.

Let’s create a single-table database for storing records for Dance Clubs in the Chicago area. Include the fields ClubID (make this the primary key), ClubName, StreetAddress, City, State, Zip, MgrLastName, MgrFirstName, MgrPhoneNumber, ClubPhoneNumber, GroupMinSize, and GroupMaxSize. Make all fields Text except GroupMinSize and GroupMaxSize – make those Number. When you are done entering the field names and types, close the table and give it a name (such as Dance Clubs).

1. Now that you have defined the table, enter the following records of data (the first value is the Club ID). To enter data into a table, you need to be in Datasheet View. Double click on your newly created table name in the left margin and enter the following four records:

111, Stomp, 410 Erie, Chicago, IL, 60045, Jones, Emil, 312-389-0091, 312-348-2231, 30, 300

222, Dancin Fools, 3990 Wabash, Chicago, IL, 60087, Cheswick, Linda, 847-929-2244, 312-432-9391, 15, 175

333, Dance Joynt, 14330 Main, Lombard, IL, 60445, Skywalker, Luke, 433-399-2242, 433-399-2243, 10, 2000

444, Happy Feet, 5 Monroe, Chicago, IL, 60045, Forouzan, Behrouz, 213-232-4329, 302-292-1193, 25, 400

After you have entered the data, click on the little square/triangle to the immediate left of ClubID. This should highlight the entire table. Then do a Copy (CTRL-C) and paste (CTRL-V) the table into a Word document. After you have copied the table, close it. It is usually best to close all tables (to save any changes) before you perform any queries or create a form or report. (To close, click on the X on the right side of the screen, about ¼ of the way down from the top.)

2. Now that we have the data in our database, let’s perform a couple simple queries. After each query, copy and paste the results of the query (just as you copied and pasted the table above) into your Word document.

a. Display all fields for all the dance clubs in the city Chicago (only).

b. Display the manager’s first name, last name and telephone number of the dance clubs in Lombard.

c. Display the Club name for the dance clubs that can accommodate 375 people (GroupMinSize <=375 and GroupMaxSize >= 375).

d. Display the Club name and address for all the clubs in zip code 60045.