Create queries and reports from the data

Assignment Help Basic Computer Science

Reference no: EM13976001

Project Description:

The main portion of the resort is the hotel. The hotel wants to store information about hotel guests, reservations, and rooms. You will design tables, import data from Access and Excel, and create relationships. Then you will be able to create queries and reports from the data.

Instructions: For the purpose of grading the project you are required to perform the following tasks:

1. Start Access. Open the downloaded file named a01_grader_h1.accdb. Save the file with the name a01_grader_h1_LastFirst, replacing LastFirst with your name. In the Security Warning bar, click Enable Content.

2. Import a table from the downloaded Access database a01_grader_h1_Guest.accdb selecting the tblGuests table. Do not save the import steps.

3. Open tblGuests. In record 25, change GuestLastName to Student and GuestFirstName to Access. Close the table.

4. Create a new table in Design view. This table will store reservations. Add the following fields, data types, and descriptions, in this order. Change field sizes as noted.

Field Name       Data Type           Description                 Field Size/Format
ReservationID       AutoNumber     A unique identifier for the reservation         Long Integer
GuestID                Number          The guest id from tblGuests (foreign key)     Long Integer
RoomNumber        Short Text                                                                            30
CheckInDate         Date/Time                                                                       Short Date
NightsStay            Number                                                                             Integer
NumberOfGuests   Number                                                                             Integer

Assign ReservationID as primary key. Save the new table as tblReservations. Close the table.

5. Import the downloaded file a01_grader_h1_Reservations.xlsx from Excel, using the tblReservations worksheet and appending it to tblReservations. The Excel column headers match the Access field names so you can use them. Do not save the import steps.

6. Use the Form tool to create a form for tblReservations, and then save the form as frmReservations_aStudent.

7. Enter the following data into the append record in frmReservations_aStudent. GuestID = 25, RoomNumber = 105, CheckInDate = 4/20/2015, NightsStay = 8, and NumberOfGuests = 1. Close the form.

8. Create a new table in Design view. This table will store information about the hotel rooms. Add the following fields, data types and field sizes in this order:

Field Name           Data Type           Field Size
RoomNumber        Short Text                30
RoomType            Short Text                20

Assign RoomNumber as the primary key. Save the new table as tblRooms. Close the table.

9. Import the hotel rooms from the downloaded text file a01_grader_h1_Rooms.csv, appending it to tblRooms. Ensure that Delimited, Comma, and First Row Contains Field Names are selected. Do not save the import steps.

10. Open the Relationships window. Add tblGuests, tblReservations, and tblRooms. Create a one-tomany relationship between GuestID in tblGuests and GuestID in tblReservations. Enforce referential integrity. Do not cascade update or cascade delete.

11. Create a one-to-many relationship between RoomNumber in tblRooms and RoomNumber in tblReservations. Enforce referential integrity. Do not cascade update or cascade delete. Save the changes.

12 Create a relationship report, saving it with the report name Relationships for a01_grader_h1. Close the report and close the Relationships window.

13. Use the Simple Query Wizard to create a query. The query results should list GuestID, GuestFirstName, GuestMiddleInital, GuestLastName, CheckInDate, NightsStay, and RoomType (in that order). Save your query as qryMyRservations_aStudent and then run the query.

14. In Design view, enter criteria to select the guest with GuestID = 25. Sort in ascending order by CheckInDate. Run and then save the query. Close the query.

15. Create a report using the Report Wizard. Add ReservationID, CheckInDate, NightsStay, and RoomType (in that order). View by tblRooms. Sort by CheckInDate and ReservationID in ascending order (in that order). Accept all other defaults. Name your report
rptReservations_aStudent and then finish the wizard.

16 View the report in Layout view. Modify the report title to be Reservations Report by A Student. Save and then close the report.

17 Close the database, and submit as directed.

Attachment:- Assignment.rar

Reference no: EM13976001

Questions Cloud

What are some potential returns on investment opportunities : What are some potential returns on investment opportunities a business may have as a result of investing in analytics?

How much charge resides on the outer surface : The membrane that surrounds a certain type of living cell has a surface area of 7.1 x 10-9 m2 and a thickness of 1.1 x 10-8 m. Assume that the membrane behaves like a parallel plate capacitor and has a dielectric constant of 5.4. The potential on ..

What percentage of the kettles will last between months : What percentage of these kettles will last between 90 and 110 months? The appliance giant offers a replacement guarantee on kettles which malfunction within a certain time of the purchase.

What is its average angular speed : A merry-go-round makes 27 revolutions in a 3.5min ride. What is its average angular speed in rad/s? What are the tangential speeds of two people 3.8m and 4.5m from the center, or axis of rotation?

Create queries and reports from the data : The main portion of the resort is the hotel. The hotel wants to store information about hotel guests, reservations, and rooms. You will design tables, import data from Access and Excel, and create relationships. Then you will be able to create que..

An account that is expected to earn 17 : 1)In 2 years from today, Dru plans to invest 8,300 dollars in an account that is expected to earn 17 percent per year. In 1 year(s), Dru plans to invest 8,100 dollars in the same account. In 9 years from today, how much money will Dru have in his acc..

Find the velocity of the wire motion as a function of time : The track lies in a vertical uniform magnetic field vector B. Generator G produces a constant current i in the wire and the rails (even as the wire moves). Find the velocity of the wire's motion as a function of time, assuming it to be stationary..

Implementation phase of strategic management : Identify at least three ways that company culture impacts the strategic plan. Provide one example using a company with a distinctive culture.

How can binary variables used to model logistical conditions : How can binary variables be used to model logistical conditions. Provide examples. Can someone please provide some assistance with how to restate.

User Account

All Pages