From classes.lanecc.edu, download the file Rentals.accb and save it.
1. Create a query named "qryVillaProperties" that selects only those properties whose name starts with Villa. The query should display the property id, property name, property type, location, country, nightly rate, and number of bedrooms.
2. Create a query named "qryNonVillaProperties" that selects all those properties whose name does not start with Villa. The query should display the same fields as number 1 above. Sort the display in ascending order on the property name.
3. Create a Parameter query named "qryCountryParameter" that allows the user to enter any country. The query should display the same fields as number 1 above.
4. Create a query named "qryEnglandSelectedProperties" that selects only those properties that are in England with a nightly rate greater than 1500. The query should display the same fields as number 1 above.
5. Create another query named "qryEnglandFranceSelectedProperties" that selects any property in England or France with a nightly rate in the range of 1000 to 2000. The query should display the same fields as number 1 above.
6. Create a query named "qryGuestsReservations" that displays the guests with reservations. The query should display the guest id, guest first and last name, country the guest is from, guest phone number, reservation id, property id, start and end dates of the reservation, the number of people staying, and the rental rate of the reservation. Sort the display in ascending order on the guest last name.
7. Create a query named "qryGuestsReservationsCostPerPerson" that displays the same information as number 6 above. In addition one more column named Cost Per Person should be added that uses the rental rate and people fields to calculate the cost per person of each reservation. The output of this column should be formatted as currency with 2 digits after the decimal point displayed. Also sort the display in ascending order on the guest last name.
8. Create a query named "qryRateStatistics" that uses the Reservation table to display the lowest rental rate, the average rental rate, and the highest rental rate of all the reservations. Hint: Your query will output only one row of data.
9. Create a form named "frmGuest" for the Guest table that looks like the following: see image.
10. Create a report named "rptGuest" that displays all guests. The report should look like the following(you will loose points if only part of the field name and contents are displayed on your report: see image.
11. Create a report named "rptEnglandFranceSelectedProperties" that uses the qryEnglandFranceSelectedProperties query you created above as its source.
12. In the Relationships windows
a.Add the work table and the personnel table to your relationship window which should include all 5 tables now. Now attempt to Link all the tables. This will yield an error similar to the one discussed in the "Referential Integrity Problems" document you can find on Moodle in week 4. Read this document before continuing since it will help you understand how to answer parts b and d below. Also you will find explicit directions in chp. 6 starting on A6-381 for creating an unmatched query. Following this will make it easier to complete the following exercises
b.Now find the record that does not meet referential integrity using the Find unmatched query wizard described above in chp 6. You currently don't have the work table and the personnel table linked to the other tables.
Specify here which table has a field with info that does not have a correct entry
c. Now alter that same record so that referential integrity can be enforced and create the one to many relationship that enforces referential integrity.
d. Explain here what you did to alter the record and explain the problem with altering the record in that way.