Week 8: Databases & Microsoft Access
Monday Class: Class as Usual
Saturday Class: Holiday (no class meeting)
Introduction to Databases
Activity: Build a Simple Database
The following activity is not meant to be a complete how-to on Access, but rather a "dip in the pool."
- Open Microsoft Access.
- Click on the Blank Database icon.
- On the right-hand side, name the database invoices.accdb and click the Browse button to navigate to where you'd like to store this file.
- Click Create.
- Under the Table1 tab, double-click on ID and rename it as CustomerID.
- Double-click on Add New Field and rename it as CustomerName.
- Click in the first cell of the row that starts with *.
- Change the Data Type to Number in the new Datasheet ribbon that has appeared.
- Type the number 1001.
- Click in the same row under CustomerName and type ACME Home Goods.
- Complete two more rows with the following data:
|1002||Baths R Us|
|1003||Ernie's Shopping Club|
- Double-click on the vertical bar separating column headings to resize the columns to fit the data.
- Press Ctrl+S, name the table CustomerList and click OK.
- Click on the Create tab, and then the Table button.
- Make a second table with the following data, and name it Invoices when you save it. You'll need to change the Data Type for the InvoiceNo column to Number and for the InvoiceDate column to Date/Time:
- Make a third table with the following name and data. (The ID field will create non-editable automated numbers):
- Make a fourth and final table as follows. You'll need to change the Data Type for the SKU column to Number and for the Price column to Currency:
- Click on the Relationships button in the Datasheet ribbon.
- Click on each table in the dialog box and click on the Add button, one at a time. Then click Close.
- Rearrange the boxes in this order:
- Click on the Edit Relationships button in the Design ribbon, then on Create New...
- Connect the two CustomerID fields in this way, then click OK, and then Create:
- Repeat steps 21 and 22 to match up the InvoiceNo field in the Invoices table with the same field name in the InvoiceItems table, and then to match up the SKU field in the InvoiceItems table with the same field name in the Products table.
- You can click-and-drag the table boxes around to make the relationships clearer:
- Click on the Create tab and then on Report Wizard.
- In the Tables/Queries dropdown, select Table: Invoices.
- Click on the > button to add all three table fields to the report.
- In the Tables/Queries dropdown, select Table: CustomerList.
- In the Available Fields column, click on CustomerName and then click on the > button to add this field to the report.
- In the Tables/Queries dropdown, select Table: InvoiceItems.
- In the Available Fields column, click on SKU and then click on the > button to add this field to the report. Then add the Quantity field as well.
- Finally, in the Tables/Queries dropdown, select Table: Products and then add the Description and Price fields to the report.
- Click the Next > button.
- Make sure that By Invoices is selected and click Next >.
- Skip the Grouping Levels option by clicking Next >.
- Skip the Sorting option by clicking Next >.
- Leave the report layout options at their defaults and click Next >.
- Select a style you like from the scroll list, then click Next >.
- Leave the Report title as Invoices, and leave the Preview button checked. Click Finish.
- Click on Close Print Preview. You arrive at the Design View for the report.
At this point you could resize field width, perform calculations such as the total price per item and per invoice, or other modifications. However, we will end the lesson at this point.
- Save the file (Ctrl+S) and turn it in.
Final Project Overview
Please review the Final Project specifications and begin working on the project during today's lab period.
Logo Design Inspiration
Here are some links to websites with great logos. Notice how the logos communicate about what the product is through their use of type, graphics, color and size.
Business Card Design Inspiration
Business cards are typically 3½" wide by 2" tall. You can also flip it vertically so that it is 2" x 3½".
Complete the Build a Simple Database assignment as described in the left-hand column.
Upload the following to the Week 8 Lesson basket: