Laboratory Exercise 1.2: Creating and Using a New Database in Access 2010

This laboratory will provide practice in

· Creating a new Access database

· Creating tables-specifying fields and keys

· Specifying relationships between tables

· Entering records

· Editing records

· Creating queries

· Creating simple reports

· Creating simple forms

For this lab, you will create a database that keeps track of your vintage music collection and the friends who borrow music from you.


1. Open Access to the Backstage View by choosing the File tab. Choose New from the File menu, then Blank Database from Available Templates at the top left of the middle panel. In the File Name window in the right panel, enter the database name, MyMusic. (Note that if you wish to specify a different directory, you can do so by clicking on the folder icon to the right of the window and changing the destination.) Click on the Create button on the bottom of the right-most panel.


2. You will see the Tables object chosen in the Navigation Pane, with a new table, Table1, listed. Right-click on the name and choose Design View. In the pop-up window, enter the table name, Music, then click on the OK button. On the first line you will see column headings Field Name and Data Type. In these columns you will specify the names and data types of each of your table columns. The first line under the headings has the name ID and type AutoNumber already displayed. Notice that there is a key icon to the left of the ID field, which means that Access is assuming that the ID field is the key for the table. If you click on that icon and look at the ribbon (the horizontal band near the top of the screen) you will see the Primary Key symbol is highlighted. On the second line, just under ID, enter the name title. Tab to the Data Type window on the same line, and click on the down arrow to see the available data types. Choose text. Move down to the Field Properties window in the bottom panel and type 35 as the field size in the first line. Move back to the third line under Field Name and enter the field name artist, then data type text, and enter the field size 20 in the Field Properties window in the bottom panel. Make the fourth field dateAcquired and choose Date/Time as the data type. Make the fifth field status with data type of text, size 10. Close the table design panel by clicking on the X button in the panel, making sure to choose the option of saving the changes to the design.


3. Now you will create a second table of friends who might borrow your music. Choose the Create tab. Choose New Table from the ribbon. A new table, Table1, appears in the Navigation Pane. Right click on that name, choose Design View, and enter the name Friends in the Save As window that pops up, then choose OK. For the first field delete ID and enter lastName, and make the data type text with field size 20. Again you see that Access displays a key icon to indicate that this field is assumed to be the primary key. Continue to create the rest of the fields

firstName text, field size 15

areaCode text, 3

phone text, 7

Save this table by clicking on the X button on the top right of the panel and choose to save the changes.


4. Create a third table, Borrow. This time for the first field, ID, change the data type to Number, with field size Long Integer. For the remaining fields, specify

lastName text, field size 20

dateBorrowed Date/Time

dateReturned Date/Time

This table needs a composite key consisting of ID and lastName. To create the composite key, move the cursor to the first column to the left of ID and, holding the Ctrl button down, move the mouse down one line to select both the ID and the lastName lines. With both highlighted, click the Primary Key icon in the ribbon. Save the table with its design changes.


5. To create relationships between the tables, click on the Database Tools tab and the Relationships icon on the ribbon. It consists of three rectangles connected by lines. In the Show Table window that pops up, highlight Music, click on the Add button, highlight Borrow, click on Add, highlight Friends, click on Add, then click on Close. The Relationship window opens, showing the three tables. Click on lastName in Friends and drag to lastName in Borrow. In the Edit Relationship window that pops up, you should see both fields listed. Check the box Enforce referential integrity and then click on the Create button. You will see a line connecting the two tables with 1..∞ on it. The 1 should be near the Friends table and the infinity symbol near the Borrow table. (If this is not the case, you made a mistake in designing the tables. You can click on the relationship line and press the Delete key to remove the relationship. Then you can return to the table design and correct any error.) Repeat the process, dragging ID in Music to ID in Borrow and create another relationship, checking on the field names and clicking on Enforce referential integrity in the pop-up window. Close the relationship window, saving changes.


6. Double click on the name of the Music table. Now you will enter data into that table. For the Music table, the system will enter an ID (1,2,3..) automatically (because you allowed it to create an AutoNumber key for you), but you should enter the names of albums you might have, the singer or artist, the date you got the album (in the US, use the form mm/dd/yyyy; otherwise use the local convention for dates), and the status. You can leave the status blank or enter borrowed,OK,scratched, or any appropriate string value. You can print the table or any other object at any time by choosing the File tab, then Print, and then choosing from the print options. When you have entered several albums, save the Music table and close it.


7. Enter data in your Friends table. This time lastName is the key. You must be careful not to enter two records with the same last name. You can either remember the values you enter for the next step or you can print this table by choosing File, Print.


8. Now you can enter data in the Borrow table. Access will check to see that any ID you enter matches an ID in the Music table, and any lastName matches one in the Friends table, so be sure the values are valid. The checking is done because you informed Access of the relationships and asked it to enforce referential integrity. Save this table.

9. Once you have created a table, you can always add new records by simply opening the table and entering the data. You can update an existing record by moving the cursor to the field to be changed and typing in the new value. You can delete a record by highlighting it and pressing the Delete key. Practice each of these operations on any table you choose.


10. Now you will create a query that you design yourself. Choose the Create tab. Choose Query Design from the ribbon. In the Show Table window, highlight Music, click on the Add button, highlight Borrow, click on the Add button, highlight Friends and click on the Add button again. Click on the Close button. You are now in the query design window. Observe that you can move the tables around in the top panel by clicking on their title bars and dragging them. You can also right click on any table title and remove that table or see its properties. Choose whatever fields you would like to include in your query. Remember you can choose any of the tables, choose different fields, use <. <=, >, <>, and so on, use is null to indicate missing values, write compound conditions for a field using AND, OR, or NOT, put multiple conditions for different fields on the same criteria line to indicate AND, or put conditions on different lines to indicate OR. Explore the options and create several queries. Note that if you wish to compare strings the case must match exactly, so be careful of capitalization. Decide what questions you would like to ask, and design and execute several queries, saving them under names you choose that suggest their contents. Close the query design window.


11. Now you should design a report. Choose the Create tab, and the Report Wizard icon from the ribbon. Notice the drop-down window that lists all the tables and queries. You could choose any combination of these objects for your report. Choose one table. From the field list just below, highlight any field you wish to include and press the right arrow button to select it for the report (or simply double click on the field name). Remember that if you accidentally choose an incorrect field, use the back arrow to deselect it. After you have chosen the fields you wish from the tables and/or queries, click on the Next button at the bottom of the Report Wizard window. You have to option to view your data by various orders and grouping levels, and to choose a style for your report. Add a title that includes Created by. Choose Finish to end the design and run the report. Print the report by choosing File, Print, and a suboption. Close the report, which will be saved under the title you entered.


12. Click on the Create tab and choose the Form Wizard icon from the ribbon. Choose the Friends table, and click on the double arrow to select all its fields. Choose all the default specifications. The form should pop up, showing a record. Use the navigation arrows at the bottom of the form to step through the table’s records one at a time. Remember you can also enter data using the form. After the last record, you will see a blank form, on which you should enter data that you make up about a friend.


13. Save all your changes and exit Access.



qr.png