Ex19 Ac Ch02 Grader ML2 Homework Instructions

Ex19 Ac Ch02 Grader Ml2 Hw Instructions

Understand that the assignment involves working with an Access database for the Prestige Hotel chain. The tasks include importing data from Excel, establishing relationships between tables, creating and modifying queries, and managing data filters and sorting. The main goal is to help the hotel managers in Denver and Chicago identify their customers who stayed in a room last year with exactly two persons in their party. The specific steps involve data import, table design adjustments, relationship creation, query design with specific filters and sorting, copying and modifying queries, and finally saving and closing the database for submission.

Paper For Above instruction

The assignment centers on utilizing Microsoft Access to organize and analyze data related to the Prestige Hotel chain’s customer stays. This project requires importing external data, configuring database tables, establishing referential integrity through relationships, designing tailored queries for specific locations and customer criteria, and manipulating query designs for comparative analysis. These steps are aimed at enabling hotel managers in Denver and Chicago to efficiently identify repeat customers who enjoyed stays last year with parties of exactly two persons, improving targeted marketing and customer service strategies.

The initial task involves opening the provided Access database, then importing location data from an Excel sheet named Location.xlsx. During import, the first row is specified as headers, the LocationID field is set to be indexed with no duplicates, and designated as the primary key. After importing, the Location table's design view is utilized to change the field size of LocationID to Long Integer, and the table is saved, finalizing the data import process. If prompted about data loss, confirmation is given to proceed, understanding that some data may be affected.

Following data import, relationships are established among four tables: Location, Orders, Members, and Service. Each relationship enforces referential integrity, with cascaded updates where appropriate, to maintain consistency across the database. The relationship between Location and Orders uses the LocationID key, while the Members and Orders tables are linked via the MemNumber field, and the Service table relates to Orders through ServiceID. The relationships are saved and the window closed to proceed to query design.

The core of the project involves creating a query in Design view that combines data from all four tables. This query includes fields such as ServiceDate, City, NoInParty, ServiceName, FirstName, and LastName, selected in a specific order. Filters are applied to refine the data: the city must be Denver, the ServiceDate should fall between July 1, 2017, and June 30, 2018, and the NoInParty must be exactly 2. The results are sorted in ascending order by ServiceDate. Once run and verified, the query is saved with the name “Denver Rooms 2 Guests.” Design view adjustments reorder the fields for a more logical presentation, and changes are saved.

Afterward, the same query structure is duplicated to create a comparable report for Chicago, with only the city filter changed from Denver to Chicago. The new query, named “Chicago Rooms 2 Guests,” is run and saved, providing a tool to compare customer stay patterns between the two cities. The database is then closed, and the project is complete for submission.

References

• Allen, T. (2020). Microsoft Access Bible. Wiley.
• Cantor, P. (2018). Learning Microsoft Access 2019. Microsoft Press.
• Walters, M. (2021). Mastering Access for Windows. Packt Publishing.
• LinkedIn Learning. (2019). Access Essential Training. LinkedIn Learning.
• O'Neill, M. (2020). Practical Access 2019. Packt Publishing.
• Karim, A. (2022). Data Management with Access. Springer.
• Turban, E., et al. (2023). Business Intelligence and Analytics. Pearson.
• Rouse, M. (2019). Relational Databases. TechTarget.
• Stefan, S. (2021). Access 2019 Programming By Example. Packt Publishing.
• Microsoft. (2023). Microsoft Access Help & Learning. Microsoft Documentation.