Lab#1: Part 1

We would like to create a database to manage information of customers, products, sales, and so forth. Accordingly, we will create related tables in our database. For example, the following diagram illustrates the names of fields, their data types and length to be included in our customer table.

Design of our Customers table

Customer ID (text:5)

Last Name (text:10)

First Name (text:10)

Address (text:50)

City (text:15)

State (text:2)

ZIP (text:5)

Step 1: Implementing the Database Design

Implement this database design in Microsoft Access. Create a new database ProjectDB and then create in it a new table, Customers with the structure as described above.

Step 2: Entering the Database Data

CUSTOMERS

CustId	Last Name	First Name	Address	City	State	Zip
00001	Jones	Bill	100 Oak Drive	Pasadena	CA	91775
00002	Jones	Nan	102 Lake View	Arcadia	CA	91778
00003	Smith	Jill	201 Maple St.	Boston	MA	02914
00004	Jones	Joe	16 River Court	Boston	MA	02915

Step 3: Creating Simple Queries

Create queries to retrieve the following information from the Customers table. Name the queries as noted.

1. all the customers (display their first and last names, cities, and states) who live in Boston (name the query Cust1)
2. all the customers (display their first and last names, cities, and states) who live in Boston OR Arcadia (name the query Cust2)
3. all the customers (display their first and last names, cities, and states) whose last name is Jones (name the query Cust3)
4. all the customers (display their first and last names, cities, and states) whose last name is Jones AND live in Boston (name the query Cust4)

Apply your queries to see whether correct information is retrieved from the Customers table.

Step 4: Record in your reading & progress report whether you can complete this part and get the correct results.