Project – Access

Due Date:July 29th

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

• (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.)
• (6 pts) Make the following modifications to the fields of the ALL RENTALS table:
  • Property ID – type “Primary Key” under the description, change the field size to Integer
  • Nightly Rate – Change the Data Type to Currency and format property to Standard, change Decimal Places to 2.
  • VIP Program – Change the data type to Yes/No
• (8 pts )Create a third table in this database by importing the text file “Project Bookings.txt”.
  • The first row contains column headings
  • Reservation ID is the primary key
  • Name the table “Bookings”
  • Don’t save the import steps
• (8 pts) Modify the fields in the Booking table according to the list below:
• (12 pts) Create a form from the Client table
  • Select all the fields from the Client table for this form
  • Make the layout a Columnar form (all the fields are lined up in a single column)
  • Title the form “New Client Entry”
  • Change the colors on the form using the AutoFormat named “Median”
  • Change the name of the form to “Client Data Entry” (not the title but the name that shows in the Navigation pane).
  • Move the Country field to be the last field on the form
• (5 pts) Use your new form to enter the following data (Do NOT overwrite existing records):
  • Guest ID:208
  • Guest First Name: (Your first name)
  • Guest Last Name: (Your last name)
  • Address: ISM 3011-013
  • City: Boca Raton
  • State: FL
  • Postal Code: 33433
  • Phone: 561-555-0227
  • Country: USA
• (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.
• ( 25 pts)Create the following query:
  • Choose the following fields (in THIS ORDER):
    • Client table table – Guest ID, Guest Last Name
    • All Rentals table – Property Name, Country, Nightly Rate
    • Bookings table – Start Date, End Date
  • Choose only clients with Guest ID’s from 203 – 215
  • Sort by Guest Last Name in Ascending order
  • 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.
  • 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.
  • Do not display the Start Date and End Date fields in the results of the query
  • Run and save the query as “Package Price by Client”
• (15 pts) Create a second query:
  • Use the All Rentals and Bookings tables and use the fields:Country and Rental Rate
  • Use an aggregate function to select an appropriate function to create 3 columns with these labels:
    • Average Rental Rates – average of Rental Rates
    • Lowest Rental Rate – lowest of the Rental Rates
    • Highest Rental Rate – highest of the Rental Rates
  • Group by Country
  • Name Query“Statistics by Country”
  • Run and save this query.
• (6 pts) Create a simple Report of the Client table.
  • Select these fields:Guest First Name, Guest Last Name, City, State and Phone
  • Use any layout or accept defaults for all questions
  • Name and Title this report “Client List”
• 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..