Access Project

Part 1: Implementing the Database Design

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.

Structure of our Customers table

Customer ID

(text:5 as Primary Key)

Last Name

(text:10)

First Name

(text:10)

Address

(text:50)

City

(text:15)

State

(text:2)

ZIP

(text:5)

· Open up Microsoft Access to create a new database and name it as ProjectDB

· In this database, create a new table in Design view (for table objects) and named it as Customers.

· In Design view of the Customers table, define the names and the types of the fields in the table as described above. In particular, when you define the Customer ID field, you should make it the primary key of the table, which allows Microsoft Access to process the information more efficiently using the Customer ID field as the key.

Part 2: Entering the Database Data

· Switch to Datasheet view (for tables objects) of the Customers.table to enter the following records into the table.

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

Part 3: Creating Some Queries

· Switch to Create queries by using Wizard (for Queries objects) to create queries to retrieve the following information from the Customers table. For each query, do the following: First, in step 1 tell the wizard to select the fields of First Name, Last Name, City, and State. Second, in step 2 tell the wizard you want to Modify the query design. In the criteria rows of the design view properly specify our constraints on the fields.

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

Part 4: Try Your Own Queries

· Apply your queries (double click them under Queries objects) to see whether correct information is retrieved from the current Customers table.

· Switch to Datasheet view (for tables objects) of the Customers table to enter the following records into the table and check to make sure the queries still work fine to retrieve the updated information from the table.

*CustId*	*Last Name*	*First Name*	*Address*	*City*	*State*	*Zip*
00005	Jones	Cleo	201 Maple St.	Arcadia	MA	02914
00006	Jones	Clifford	16 River Court	Boston	MA	02915

In your weekly progress report, report whether you are able to go through these steps to create a database with a single table.