COSC1371 PROJECT-9 Due: Monday, 11/23/2009 ACCESS2007(2) Objectives: 1. Continued Use of ACCESS2007. 2. Download database files from "WWW.PRENHALL.COM/GRAUER" just as you did in Project-1. This time, it is "Exploring Access" that is to be downloaded 3. Create a "One-to-Many" relationship from a field of one table (Primary) table to the identical field of another table (Related or Secondary) table so as to enforce the Referential Integrity. The Referential Integrity is not to be enforced unless (a) each record of the Primary table has a unique value in the reference field with respect to which a one-to-many relationship is being established and (b) the primary table must contain exactly one record with each value of the reference field that the related table has. That is, all values in the reference field of the related table must be covered by records of the primary table. Note, however, that the converse is not necessary. That is, the primary table may have a value of the reference field that no record of the related table has. In this respect, ACCESS is referred to as a "Relational" database system. 4. Save the results of creating such a relationship onto the Primary table. In this case, the Primary table will have each record of its own turn into a folder containing all records of the secondary table with the matching reference field value. 5. Create a report using the Report Wizard out of a query containing records satisfying a certain filter criterion. This Wizard lets us select, among others, a. tables or queries out of which a report is to be created. b. some of their fields to appear on the report. c. one or more fields for grouping level or levels. d. the report layout. Steps: 1. Download the Exploring Access from our author's web page mentioned above. See step-2 and step-3 on pages 504-505 2. Open "Large Database" of Practice-4, Chapter-1. This database file has a table called "Employees" with 311 employees working in eleven different locations (US major cities). These Cities are: Atlanta, Boston, Chicago, Cleveland, Detroit, Kansas City, Los Angeles, Miami, New York, Phoenix and SanFrancisco. 3 You will create another table, called "Locations", that contains these eleven cities. This table will have following fields as shown on page-523: Location, Address, State, Zipcode and OfficePhone. Notice that these eleven records of this table have each a unique Location value as this field "Location" is to be used as the reference field with respect to which a one-to-many relationship is to be created from this "Locations" table to the "Employees" table. And, this field muct be designated as the (only one) Primary Key Field. 4. Create a "One-to-Many" relationship This envolves taking five sub-steps: (See Step-1 and -2 on pages 525-526) a. Click Relationships command on the Show/Hide Group on the Database Tools Tab. The relationships window will appear. b. Click "Show Table" Command on the Relationships window. The Show Table dialogue Box will show. c. Using this dialogue box, both tables will be added to the Relationships window. d. Left click the field (Reference) Location of the Primary table (Locations) and drag to the same field of the table Employees. At this time, "Edit Relationships" dialogue box will appear. e. Check "Enforce Referential Integrity" box and check "Create" box to finish creating the wanted relationship. 5. Save the results of creating the relationship by clicking the "Save" button on the Relationships Menu. This will result in each location of the table "Locations" turning into a folder that contains all employees of the table "Employees" with the same location field value. For example, Atlanta will have 37 employees, Boston 38, Chicago 39, and Cleveland 39, when properly done. 6. Create a report called EastCoast. a. create a query (called NYBoston) that contains 58 employees working in Boston or in New York b. Create a report using the Wizard out of the above query that includes the following five fields: Gender, Location, LastName, FirstName and EmployeeID. c. Use the Gender as the field of grouping. d. Save the report as EastCoast. That's all. Enjoy the project. As usual, turn in a USB containing the two tables: Employees and Locations, one query called NYBoston and one report called EastCoast all inside "Employ" Database file.