FS104 Computer Applications :: Art Institute

Week 8: Databases & Microsoft Access

Monday Class: Class as Usual

Saturday Class: Holiday (no class meeting)

Introduction to Databases

View Slideshow: PPT

View Slideshow: PDF

Sample Access Database

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."

  1. Open Microsoft Access.
  2. Click on the Blank Database icon.
  3. 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.
  4. Click Create.
  5. Under the Table1 tab, double-click on ID and rename it as CustomerID.
  6. Double-click on Add New Field and rename it as CustomerName.
  7. Click in the first cell of the row that starts with *.
  8. Change the Data Type to Number in the new Datasheet ribbon that has appeared.
  9. Type the number 1001.
  10. Click in the same row under CustomerName and type ACME Home Goods.
  11. Complete two more rows with the following data:
1002 Baths R Us
1003 Ernie's Shopping Club
  1. Double-click on the vertical bar separating column headings to resize the columns to fit the data.

Access table1

  1. Press Ctrl+S, name the table CustomerList and click OK.
  2. Click on the Create tab, and then the Table button.
  3. 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:

Access table2

  1. Make a third table with the following name and data. (The ID field will create non-editable automated numbers):

Access table 3

  1. 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:

Access table 4

  1. Click on the Relationships button in the Datasheet ribbon.
  2. Click on each table in the dialog box and click on the Add button, one at a time. Then click Close.

Access relationships 1

  1. Rearrange the boxes in this order:

Access relationships 2

  1. Click on the Edit Relationships button in the Design ribbon, then on Create New...
  2. Connect the two CustomerID fields in this way, then click OK, and then Create:

Access relationships 3
Access relationships 4

  1. 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.

Access relationships 5

  1. You can click-and-drag the table boxes around to make the relationships clearer:

Access relationships 6

  1. Click on the Create tab and then on Report Wizard.
    1. In the Tables/Queries dropdown, select Table: Invoices.
    2. Click on the > button to add all three table fields to the report.
    3. In the Tables/Queries dropdown, select Table: CustomerList.
    4. In the Available Fields column, click on CustomerName and then click on the > button to add this field to the report.
    5. In the Tables/Queries dropdown, select Table: InvoiceItems.
    6. 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.
    7. Finally, in the Tables/Queries dropdown, select Table: Products and then add the Description and Price fields to the report.

Access report wizard 1

  1. Click the Next > button.
  2. Make sure that By Invoices is selected and click Next >.

Access report wizard 2

  1. Skip the Grouping Levels option by clicking Next >.
  2. Skip the Sorting option by clicking Next >.
  3. Leave the report layout options at their defaults and click Next >.
  4. Select a style you like from the scroll list, then click Next >.
  5. Leave the Report title as Invoices, and leave the Preview button checked. Click Finish.
  6. Click on Close Print Preview. You arrive at the Design View for the report.

Access report wizard 3

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.

  1. 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½".

Assignment

Complete the Build a Simple Database assignment as described in the left-hand column.

 

eCompanion Dropbox

Upload the following to the Week 8 Lesson basket:

  • invoices.accdb