For the project below, use the file Access  Project.accdb and Project Bookings.txt from Blackboard.

1. (5  pts)  Open Access Project.accdb and open the All Rentals table.  Copy all the records from the Villa Rentals table into the All Rentals table. (Do not overwrite existing records.)
2. (6 pts) Make the following modifications to the fields of the ALL RENTALS table:
   1. Property ID – type “Primary Key” under the description, change the field size to Integer
   2. Nightly Rate – Change the Data Type to Currency and format property to Standard, change Decimal Places to 2.
   3. VIP Program – Change the data type to Yes/No
3. (8 pts )Create a third table in this database by importing the text file “Project Bookings.txt”.
   1. The first row contains column headings
   2. Reservation ID is the primary key
   3. Name the table “Bookings”
   4. Don’t save the import steps
4. (8 pts) Modify the fields in the Booking table according to the list below:

1. (12 pts) Create a form from the Client table
   1. Select all the fields from the Client table for this form
   2. Make the layout a Columnar form (all the fields are lined up in a single column)
   3. Title the form “New Client Entry”
   4. Change the colors on the form using the AutoFormat named “Median”
   5. Change the name of the form to “Client Data Entry” (not the title but the name that shows in the Navigation pane).
   6. Move the Country field to be the last field on the form
2. (5 pts) Use your new form to enter the following data (Do NOT overwrite existing records):
   1. Guest ID:  208
   2. Guest First Name: (Your first name)
   3. Guest Last Name: (Your last name)
   4. Address:  ISM 3011-013
   5. City: Boca Raton
   6. State: FL
   7. Postal Code: 33433
   8. Phone: 561-555-0227
   9. Country: USA

1. (10 pts) Define two relationships in this database.  First, define a one-to-many relationship between the Bookings table and the All Rentals table.  Second, define a one-to-many relationship between the Bookings table and the Client table.  For both relationships, select the referential integrity option and the cascade updates option for this database.
2. ( 25  pts)  Create the following query:
   1. Choose the following fields (in THIS ORDER):

i.      Client table table – Guest ID, Guest Last Name

ii.      All Rentals table – Property Name, Country, Nightly Rate

iii.      Bookings table – Start Date, End Date

1. Choose only clients with Guest ID’s from 203 – 215
2. Sort by Guest Last Name in Ascending order
3. In a new column, calculate the length of vacation in days (Hint: use the start date and end date fields.)  Use “Days” as the label for this column.
4. In another new column, calculate the price of the trip by multiplying the Nightly Rate by the length of the trip (see D above.)  Label this new field “Total Package Price”.  Format this new field as currency.
5. Do not display the Start Date and End Date fields in the results of the query
6. Run and save the query as “Package Price by Client”

1. (15 pts) Create a second query:
   1. Use the All Rentals and Bookings tables and use the fields:  Country and Rental Rate
   2. Use an aggregate function to select an appropriate function to create 3 columns with these labels:

i.      Average Rental Rates – average of Rental Rates

ii.      Lowest Rental Rate – lowest of the Rental Rates

iii.      Highest Rental Rate – highest of the Rental Rates

1. Group by Country
2. Name Query  “Statistics by Country”
3. Run and save  this query.
4. (6 pts) Create a simple Report of the Client table.
   1. Select these fields:  Guest First Name, Guest Last Name, City, State and Phone
   2. Use any layout or accept defaults for all questions
   3. Name and Title this report “Client List”

1. Save your file as “Project YourName.accdb” where your name is the first letter of your first name plus your last name (for example – LFeidelman).  Upload your ACCESS project assignment..