Project – AccessDue Date: November 15, 2015For the project below, use the file Access Project.accdb

Project – AccessDue Date: November 15, 2015For 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:a. Property ID – type “Primary Key” under the description, change the field size to Integerb. Nightly Rate – Change the Data Type to Currency and format property to Standard, change Decimal Places to 2.c. VIP Program – Change the data type to Yes/No3. (8 pts )Create a third table in this database by importing the text file “Project Bookings.txt”.a. The first row contains column headingsb. Reservation ID is the primary keyc. Name the table “Bookings”d. Don’t save the import steps4. (8 pts) Modify the fields in the Booking table according to the list below:Field Name:Data Type:Description:Field Size:Other Properties:Reservation IDNumberPrimary KeyIntegerGuest IDNumberForeign KeyIntegerProperty IDNumberForeign KeyIntegerStart DateDate/TimeFormat: Short DateEnd DateDate/TimeFormat: Short DatePeopleNumberNumber of people in partyIntegerRental RateCurrencyCurrency format, no decimal places5. (12 pts) Create a form from the Client tablea. Select all the fields from the Client table for this formb. Make the layout a Columnar form (all the fields are lined up in a single column)c. Title the form “New Client Entry”d. Change the colors on the form using the AutoFormat named “Median”e. Change the name of the form to “Client Data Entry” (not the title but the name that shows in the Navigation pane).f. Move the Country field to be the last field on the form6. (5 pts) Use your new form to enter the following data (Do NOT overwrite existing records):a. Guest ID: 208b. Guest First Name: (Your first name)c. Guest Last Name: (Your last name)d. Address: ISM 3011-013e. City: Boca Ratonf. State: FLg. Postal Code: 33433h. Phone: 561-555-0227i. Country: USA7. (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.8. ( 25 pts) Create the following query:a. 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 Dateb. Choose only clients with Guest ID’s from 203 – 215c. Sort by Guest Last Name in Ascending orderd. 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.e. 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.f. Do not display the Start Date and End Date fields in the results of the queryg. Run and save the query as “Package Price by Client”9. (15 pts) Create a second query:a. Use the All Rentals and Bookings tables and use the fields: Country and Rental Rateb. 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 Ratesc. Group by Countryd. Name Query “Statistics by Country”e. Run and save this query.10. (6 pts) Create a simple Report of the Client table.a. Select these fields: Guest First Name, Guest Last Name, City, State and Phoneb. Use any layout or accept defaults for all questionsc. Name and Title this report “Client List”11. 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..