Reference no: EM132163294
USE MICROSOFT ACCESS
1. Flushing Hotel Rents hotel rooms to regular business customers. The room rates depends on the type of room and the number of days the room was rented. There are two types of rooms, the Studio, and the Suite. Flushing Hotel bills the customer’s company at the end of each month. The database includes the following tables ( make up your own data): Create the database with the following tables as follows: The name of the database is Quiz8+your last name.
1. Company: CompanyID (text, primary Key), CompanyName(text), Address (text)
2.. Rates: TypeOfRoom (text, Primary Key), PricePerDay (Currency)
3. Rentals: OrderID (Auto Number, Primary Key), CompanyID(text), RoomNo (text), CheckOut (date), CheckIn (date).
4. Rooms: RoomNo (text,Primary key), TypeOfRoom (text)
Enter six company records, Two Rate records, 10 rooms and 15 Rentals records. All Rentals should be on May , June, or July of 2016. Make five rentals from each month. Make up your own data.
2. Create an Rental query, named QryRental, which includes Order ID, Days Rented, SubTotal, Order Month and Order Year. Note that Subtotal is days Rented times price per day. Note that days rented is Check out minus check in (DaysRented:Checkout-Checkin). The month of order month shall be the month of check out ( ordermonth:month(checkout)). Note that checkout date is always later than check in date.
3. Create a query, named qryMonthlyRevenue, which will compute the total sale (sum of subtotal ) for each month. The result should look like as follow:
4. Create a query, named qryDaysRentedByTypeOfRoom, which includes Type of Room and total number of days rented for each type of room.
6. Create a parametric query, named paraqryCustomerInvoice, which includes Company Id, Company Name, Address, OrderID, Days Rented, Subtotal, Tax, and Total. The query allows the user to display the invoice of one selected customer by answering the prompt “ Please Enter Company ID.” Note that tax is 10% of the subtotal. Total is subtotal plus tax.
7. Create a parametric query, named paraqryMonthlyInvoice, which include all the field names from the Invoice query above. The query allows user to display the invoices of a certain month. The user select a month by answering a prompt “ please enter month”.