Exp19_Access_Ch02_ML2 – Prestige Hotel 1.0
The Prestige Hotel chain caters to upscale business travelers and provides state-of-the-art conference, meeting, and reception facilities. It prides itself on its international, four-star cuisine. Last year, it began a member reward club to help the marketing department track the purchasing patterns of its most loyal customers. All of the hotel transactions are stored in a database. Your task is to help the managers of the Prestige Hotels in Denver and Chicago identify their customers who stayed in a room last year and who had two persons in their party.
Start Access. Open the downloaded Access file named Exp19_Access_Ch02_ML2_Hotel. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.
Import the location data from the Excel file Location.xlsx as a new table in the current database. Using the wizard, specify that the first row contains column headings, set the LocationID field to be indexed with no duplicates, and set the LocationID field as the primary key. Import the table with the default name Location and do not save the import steps.
View the Location table in Design view and change the field size for the LocationID field to Long Integer. Save the table. Click Yes in the dialog box indicating that some data may be lost. Close the table.
Add the Location, Orders, Members, and Service tables to the Relationships window to begin establishing relationships in the database. Close the Show Table dialog box. Create a one-to-many relationship between the Location table and the Orders table using the LocationID field. Enforce Referential Integrity. Select the option to cascade update the related fields.
Create a relationship between the Members and Orders tables using the MemNumber field, ensuring that you enforce referential integrity and cascade update related fields.
Create a relationship between the Service and Orders tables using the ServiceID field, ensuring that you enforce referential integrity and cascade update related fields. Save and close the Relationships window.
Create a new query in Design view using Location, Members, Orders, and Service tables. Add the following fields to the query (in this order): ServiceDate from the Orders table; City from the Location table, NoInParty from Orders table; ServiceName from the Service table; and FirstName and LastName from the Members table. Set the criteria in city field to limit the output to Denver.
Display only service dates from 7/1/2017 to 6/30/2018 (Hint: Use the Between command).
Set the NoInParty criterion to 2. Sort the results in ascending order by the ServiceDate. Run and save the query with the name Denver Rooms 2 Guests.
Use Design view to change the order of the query fields so that they display as FirstName, LastName, ServiceDate, City, NoInParty, and ServiceName. Run, save the changes to the query, and close the query.
Copy the Denver Rooms 2 Guests query and paste it in the Navigation pane, renaming the new query Chicago Rooms 2 Guests.
Open the Chicago Rooms 2 Guests query in Design view, and change the City criterion from Denver to Chicago. Run the query and save the changes. Close the query.
Close the database and exit Access. Submit the database as directed.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more