Access ProjectPage 1 of 7Fall 2006

Part I: The Condominium Corporation Database

You’re hired to assist the chief manager of The Condominium Corporation. managing new condominium projects in your area. You were assigned to maintain a database pertaining to current condo projects. The Condominium Corporation builds and sells condominiums to middle-class families in the area. Each condo project is assigned a Condo ID which is unique to that condo project. Each employee of The Condominium Corporation is assigned to one of the condo projects. It is your job to keep up with this data pertaining to condo projects and their assigned employees. This is a new database that you must create and then begin accumulating the data.

Note:- For steps 1-5, see Tutorial 2 p. 41+ if you need Guidelines

-For steps 6-9, and 13 see Tutorial 4 if you need Guidelines

-For steps 10-12, see Tutorial 5 if you need Guidelines

  1. Start Access and open a new Blank database. Called the database Condominium. Then, create the following three tables that will contain the following fields.
    Table name: Condo Projects

Field Name / Data Type / Field Size / Description
Condo ID / Text / 6 / Uniquely identifies the condo project and is the primary key
Condo Name / Text / 30 / Name of the condominium
Units / Number / Single / Number of units in the condominium
Condo Manager / Text / 15 / Name of the manager of the project
Start Date / Date / Default / Date the project was started
End Date / Date / Default / Estimated completion date

Table name: Employees

Field Name / Data Type / Field Size / Description
Employee ID / Text / 6 / Uniquely identifies the employee and is the Primary Key
Last Name / Text / 20 / Employee’s Last Name
First Name / Text / 20 / Employee’s First Name
Phone / Text / 10 / Employee’s Phone Number

Table name: Condos

Field Name / Data Type / Field Size / Description
Condo Name / Text / 30 / Name of the condominium and is the Primary Key
Style / Text / 20 / Architecture Style of the condo
Units / Number / Single / Number of units in the condominium
  1. Each of the tables above has a primary key that will identify each record in the table. Make sure that you have set the primary key for each table. Now, you must determine a field that will establish a relationship between the two tables. Each employee works on only one condo project at a time so the relationship will be a One-To-Many relationship. You will need to add a field that will serve as the common field on which the relationship between the Condo Projects and the Employees tables will be formed. Look at your two tables and determine what you need to add. If you have trouble, look at the data that you will add to the table Employees in Step # 5. This will give you a hint as to what field you need to add to your Employees table.
  2. Add the field needed for the relationship in the Employees table. Open the relationships window and establish a relationship between the two tables (Condo Projects and Employees). Choose the option to Enforce Referential Integrity, Cascade Updates, and Cascade Deletes.
  3. Create a relationship between the Condo Projects table and the Condos table.
  4. Add the following records to the following tables.
    Table: Condo Projects

Condo ID / Condo Name / Units / Project Manager / Start Date / End Date
CP0105 / East Gate / 30 / Davis / 05/18/03 / 09/01/03
CP0106 / West Gate / 25 / Dorcey / 04/01/03 / 08/01/03
CP0107 / North Gate / 30 / Dorcey / 04/01/03 / 08/15/03
CP0108 / South Gate / 25 / Dorcey / 04/30/03 / 09/15/03
CP0109 / Wood Ridge / 35 / Dorcey / 04/30/03 / 09/30/03
CP0104 / Woodlawn / 40 / Davis / 04/30/03 / 09/15/03
CP0102 / Prairies / 35 / Davis / 04/15/03 / 09/30/03
CP0101 / Des Plaines / 42 / Davis / 05/01/03 / 08/15/03
CP0120 / Belle View / 35 / Povoloski / 05/01/03 / 08/15/03
CP0121 / Holly Woody / 30 / Povoloski / 05/01/03 / 08/15/03

Table: Employees

Employee ID / Last Name / First Name / Phone / Condo ID
EO197 / Crenshaw / Bill / 890-7856 / CP0107
EO198 / Garnier / Candy / 907-8967 / CP0120
EO199 / Tindell / Don / 890-8744 / CP0120
EO200 / Smithson / Sarah / 890-5633 / CP0101
EO201 / Brooks / Hannah / 890-7822 / CP0121
EO202 / Brooks / Austin / 890-7822 / CP0121
EO203 / Stephens / Philip / 997-5645 / CP0104
EO204 / Scott / Tamara / 997-3321 / CP0107
EO205 / Richards. / Tim / 997-0009 / CP0109
EO206 / Soderman / Pam / 897-1234 / CP0101

Table: Condos

Condo Name / Style / Units
East Gate / Country / 30
West Gate / Country / 25
North Gate / Middle Age / 30
South Gate / Modern Age / 25
Wood Ridge / Middle Age / 35
Woodlawn / Country / 40
Prairies / Royal / 35
Des Plaines / Royal / 42
Belle View / Country / 35
Holly Woody / Modern Age / 30
  1. Create a query, Condo Projects Status, which shows a list of condos with their Condo ID, their start date, the expected end date, and the Condo Manager of the condo project. Hint: There is only one table involved in this query.
  2. Create a query, Available Employees, that will display the Condo ID, the Condo Name, the Employee ID, and the End date for those records that have an end date prior to August 30, 2003. Hint: There are two tables involved in this query.
  3. Create a form including a main form and a subform. Use the Condo Projects table as the data source for the main form. Condo ID and Condo Name should be selected from the Condo Projects table. Use the Employees table as the data source for the subform. Employee ID, Last Name , and First Name should be selected from the Volunteers table. Use the Datasheet layout for the subform, and the International style for your form. Name the main form Employee-Condos Data, and the subform Employees Subform.
  4. Create a report based on all fields of the Available Employees query. Use the Report Wizard and group the report by Employee ID. Use the Align Left 1 layout and landscape orientation (this choice appears on the same screen as the choice of layouts). Choose the Bold style. Name the report Available Employees Report
  5. Open the Condo Projects table and change the Condo Name field so that it is a Lookup Wizard that looks up condo names in the Condos table. Note: If you got a message about deleting a relationship in order for you to be able to make the change, you can open the relationship window (by clicking Tools, and then Relationships), and delete the appropriate relationship.
  6. Create a parameter query that displays all the fields of the Condo Projects table. The parameter query should prompt the user to enter the name of a Project manager and, as a result, display all Condo IDs and Condo Names the project manager is in charge. Name the query Condo Parameter Query. Run the query to display only those condos that Dorcey is the project manager.
  7. Create a query that displays Condo IDs, Condo Names, Condo Managers, as well as employees’ first names and last names. The query should only show: (1)those recordswhere Povoloski is the manager, and somebody with Davis as last name is employed, or (2) those records where the condo name begins with the letter H or the word Belle. Name the query Povoloski Brooks Query.
  8. Create a report based on the Condo Projects table (using Condo ID, Condo Name, Condo Manager fields) and the Employees table (using Employee ID, First Name, Last Name, and Phone fields). The report data must be grouped by Condo ID and by Condo Name. Use the Align Left 1 layout and the landscape orientation, and the Bold style. Name the report Condo Employees Report.
  9. Backup your database using the Back Up database item from the File menu under the name FirstLastProject where FirstLast represents your first and last names (e.g. for John Doe, the database must be called JohnDoeProject).
  10. Email the database file (i.e. your FirstLastProject file) as attachment to the instructor (at ) on Tuesday 11/28/2006 no later than 5:00 PM. Note: DO NOT send your assignment before the due date.
  11. Make sure that you have a copy of your database on your flash drive.

Student Name: ______
Part II: The Habitat Houses Database

In this part of the Access project, you will answer Multiple Choice questions about the Habitat Houses database you worked on in your Lab Project 1.

Consider the following Access screenshot showing the Volunteers Assignments query in design view and the same query in datasheet view.

Q.1. What criteria could we use in the Volunteers Assignments query to get only those volunteers assignments with an address on the Elm Street? (Circle all correct answers)

a) 1?? Elm Street

b) *Elm*

c) 14?? Elm*

d) None of the above

Consider the following Access screenshot showing the Volunteers Assignments query in design view and the Houses table in datasheet view.

Q.2. What field(s) we need to add to the Volunteers Assignments query and what criteria could be used in order to get only those volunteers assignments on the Elm Street that ended prior to September 30th 2003. (Circle all correct answers)

a) We need to add the Start Date field and use <9/30/2003 as criteria

b) We need to add the End Date field and use >9/30/2003 as criteria

c) We need to add the End Date field and use <9/30/2003 as criteria

d) We need to add the Start Date field and use >=9/302003 as criteria

e) None of the above

Consider the following Access screenshot showing the Houses table in design view.

Q.3. How can we set a rule that prevent any user of the Houses table form creating a record with a project manager other than Carpenter, Davidson or Brinkley AND an end date beyond December the 30th 2005?

a) We need to set the Default Value property to Carpenter or Davidson or Brinkley for the Project Manager field.

We can to set the Validation Ruleproperty to Only Carpenter, Davidson or Brinkley for the Project Manager field.

We need to set the Default Value property to <12/30/2005 for the End Datefield.

We can to set the Validation Ruleproperty to End Date must be prior to 12/30/2005 for the End Date field.

b) We need to set the Validation Rule property to Carpenter or Davidson or Brinkley for the Project Manager field.

We can to set the Validation Textproperty to Only Carpenter, Davidson or Brinkley for the Project Manager field.

We need to set the Validation Rule property to <12/30/2005 for the End Datefield.

We can to set the Validation Textproperty to End Date must be prior to 12/30/2005 for the End Date field.

c) We need to set the Format property to Carpenter or Davidson or Brinkley for the Project Manager field.

We can to set the Default Valueproperty to Only Carpenter, Davidson or Brinkley for the Project Manager field.

We need to set the Format property to <12/30/2005 for the End Datefield.

We can to set the default Valueproperty to End Date must be prior to 12/30/2005 for the End Date field.

b) None of the above