Gullhaven Guest House

Queries

The questions this database answers, saved under a name. Open one to see its answer, or its design to see how it is built and change it.

Arrivals in the next 7 days

Who is coming in the next week, day by day and room by room, with their source, expected time, requests and what they will owe. Look at it every morning.

From Bookings, Guests, Rooms, Booking Sources. Shows Arrival, Days Until, Booking No, Guest, Room, Nights, Adults, Children, Source, Expected Arrival, Deposit Paid, Balance, Requests.

Calculated: Days Until is DateDiff("d", Date(), [Arrival]); Guest is [First Name] & " " & [Last Name]; Room is [Rooms].[Number] & " " & [Rooms].[Name]; Balance is Round([Room Total] - Nz([Deposit Paid]), 2).

Only where Days Until Between 0 And 7 and Status = Confirmed.

Departures in the next 7 days

Who leaves in the next week, with the room charge still to pay after the deposit.

From Bookings, Guests, Rooms. Shows Departure, Days Until, Booking No, Guest, Room, Arrival, Nights, Phone, Room Total, Deposit Paid, Balance on Room, Status.

Calculated: Days Until is DateDiff("d", Date(), [Departure]); Guest is [First Name] & " " & [Last Name]; Room is [Rooms].[Number] & " " & [Rooms].[Name]; Balance on Room is Round([Room Total] - Nz([Deposit Paid]), 2).

Only where Days Until Between 0 And 7 and Status In ("Confirmed", "Checked in").

Rooms free tonight and this weekend

The rooms still free tonight, and on the coming Friday and Saturday nights, for the phone call that asks "have you a room?".

From Room Nights, Rooms, Room Types. Shows Night, Weekday, Room No, Room, Room Type, Sleeps, Outlook.

Only where Weekday In ("Fri", "Sat") and Days Ahead = 0 and Days Ahead Between 0 And 6 and Status = Free and Status = Free.

Occupancy by month

For each month from a year ago to two months ahead: room nights there were to sell (rooms out of order left out), how many were sold, the occupancy, the average rate for a night sold, the room revenue for each room night there was, and the room revenue. Months ahead show what is on the books so far.

From Room Nights. Shows Month, Rooms Available, Nights Sold, Occupancy %, Average Rate, Revenue per Room, Room Revenue, with totals.

Calculated: Month is Format([Night], "yyyy-mm"); Nights Sold is IIf([Status] = "Booked", 1, 0); Occupancy % is IIf([Status] = "Booked", 100, 0); Average Rate is IIf([Status] = "Booked", [Rate], Null); Revenue per Room is IIf([Status] = "Booked", Nz([Rate]), 0); Room Revenue is IIf([Status] = "Booked", Nz([Rate]), 0).

Only where Status <> "Out of order" and Months Ago Between -2 And 12.

Revenue by source

Room takings for stays begun in the last twelve months, by where they were booked, with the commission each site took and what is left after it.

From Bookings, Booking Sources. Shows Source, Kind, Bookings, Nights, Room Takings, Commission, After Commission, with totals.

Calculated: After Commission is Round([Room Total] - Nz([Bookings].[Commission]), 2).

Only where Status In ("Checked in", "Checked out") and Days Ago Between 0 And 365.

Deposits outstanding

Bookings to come whose deposit is not yet paid, with the guest's phone and e-mail, soonest arrival first: the list for chasing.

From Bookings, Guests. Shows Arrival, Days to Arrival, Booking No, Guest, Phone, E-mail, Booked On, Deposit Due, Deposit Paid, Owed.

Calculated: Days to Arrival is DateDiff("d", Date(), [Arrival]); Guest is [First Name] & " " & [Last Name]; Owed is Round([Deposit Due] - Nz([Deposit Paid]), 2).

Only where Owed > 0 and Status = Confirmed.

Returning guests

Guests who have stayed more than once, with their first and last stay and what they spent on rooms, the most loyal first.

From Guests, Bookings. Shows Guest, City, Country, E-mail, Stays, First Stay, Last Stay, Nights, Spent on Rooms, with totals.

Calculated: Guest is [First Name] & " " & [Last Name].

Only where ID > 1 and Status = Checked out.

Housekeeping list for today

Every room in use today and what it needs: departures to strip and clean first, then stayovers, then arrivals to make ready.

From Bookings, Rooms, Guests. Shows Room No, Room, Task, Housekeeping, Guest, Adults, Children, Expected Arrival, Requests.

Calculated: Task is IIf(DateDiff("d", Date(), [Departure]) = 0, "Departure: strip the beds and clean in full", IIf(DateDiff("d", [Arrival], Date()) = 0, "Arrival: make up, check and put out the welcome tray", "Stayover: tidy, fresh towels, refill the tray")); Guest is [First Name] & " " & [Last Name].

Only where Since Arrival >= 0 and To Departure >= 0 and Status In ("Confirmed", "Checked in").

Room status

Every room with its type and its housekeeping state now.

From Rooms, Room Types. Shows Room No, Room, Room Type, Floor, Outlook, Beds, Sleeps, Housekeeping, Notes.

Guest register

The register of guests for the last twelve months: arrival, name, nationality, passport where it is kept, home address, room, departure and car.

From Bookings, Guests, Rooms. Shows Month, Arrival, Guest, Nationality, Passport Number, Passport Issued By, Home Address, Room, Departure, Car Registration.

Calculated: Month is Format([Arrival], "yyyy-mm"); Guest is [First Name] & " " & [Last Name]; Home Address is [Guests].[Address] & ", " & [Guests].[City] & IIf(Nz([Guests].[Postcode], "") = "", "", " " & [Guests].[Postcode]) & ", " & [Guests].[Country]; Room is [Rooms].[Number] & " " & [Rooms].[Name].

Only where Status In ("Checked in", "Checked out") and Days Ago Between 0 And 365.

Guests in the house

Guests staying now, with what has been charged to their room so far and the deposit held, so a bill can be ready when they leave.

From Bookings, Guests, Rooms, Charges. Shows Room No, Room, Booking No, Guest, Arrival, Departure, Charged So Far, Deposit Paid, with totals.

Calculated: Guest is [First Name] & " " & [Last Name]; Charged So Far is Round([Quantity] * [Unit Price], 2).

Only where Status = Checked in.

Extras sold by month

What the extras took each month over the last year, item by item.

From Charges, Extras. Shows Month, Extra, Sold, Takings, with totals.

Calculated: Month is Format([Date], "yyyy-mm"); Takings is Round([Quantity] * [Unit Price], 2).

Only where Days Ago Between 0 And 365.

Cancellations and no-shows

Cancelled bookings and guests who did not come, from a year ago to six months ahead, with the notice given and the deposit kept.

From Bookings, Guests, Booking Sources. Shows Arrival, Booking No, Guest, Source, Status, Nights, Value, Cancelled On, Days Notice, Deposit Kept.

Calculated: Guest is [First Name] & " " & [Last Name]; Days Notice is DateDiff("d", [Cancelled On], [Arrival]).

Only where Status In ("Cancelled", "No-show") and Days Ago Between -180 And 365.

Commission by month

Every commission statement of the last year, by date, with what it listed, what was owed and when it was paid.

From Commission Statements, Booking Sources. Shows Statement Date, Statement, Month, Site, Stays, Room Takings, Commission, Paid On.

Only where Days Ago Between 0 And 400.

Tariff

The rate card: each room type, season and plan with its weeknight and weekend rate.

From Rates, Room Types, Seasons. Shows Room Type, Season, Months, Plan, Weeknight Rate, Weekend Rate.

Forms

Every table has a form of the same name: Start Here, Business, Seasons, Room Types, Rates, Rooms, Booking Sources, Guests, Extras, Commission Statements, Bookings, Room Nights, Bills, Charges, Payments. A form shows one record at a time and adds new ones. A field that points at another table is a list of that table's records, and the records that point at this one are listed below it.

Reports

Pages to print or save as a PDF, laid out from a query or a table.

Arrivals in the next 7 days

The coming week's arrivals on a page, a group for each day, for the front desk.

From the query Arrivals in the next 7 days, a group for each Arrival: Booking No, Guest, Room, Nights, Adults, Children, Source, Expected Arrival, Balance, with the total of Nights, total of Balance.

Departures in the next 7 days

The coming week's departures, a group for each day, with the balances to take.

From the query Departures in the next 7 days, a group for each Departure: Booking No, Guest, Room, Nights, Phone, Room Total, Deposit Paid, Balance on Room, with the total of Balance on Room.

Housekeeping for today

Today's list for whoever cleans the rooms: room, task, guest and requests.

From the query Housekeeping list for today: Room No, Room, Task, Housekeeping, Guest, Expected Arrival, Requests, with the count of Room No.

Occupancy and room rate by month

Occupancy, average rate and room revenue for each month, with the totals.

From the query Occupancy by month: Month, Rooms Available, Nights Sold, Occupancy %, Average Rate, Revenue per Room, Room Revenue, with the total of Nights Sold, total of Room Revenue.

Room takings by source, last 12 months

Room takings over the last year by source, commission and what is left, with the totals.

From the query Revenue by source: Source, Kind, Bookings, Nights, Room Takings, Commission, After Commission, with the total of Room Takings, total of Commission, total of After Commission.

Deposits not yet paid

The deposits not yet paid, with how to reach each guest.

From the query Deposits outstanding: Arrival, Booking No, Guest, Phone, E-mail, Booked On, Deposit Due, Owed, with the total of Owed.

Register of guests

The register of guests month by month, as a guest house keeps it for inspection.

From the query Guest register, a group for each Month: Arrival, Guest, Nationality, Passport Number, Passport Issued By, Home Address, Room, Departure, Car Registration, with the count of Guest.

Room rates

The room rates, a group for each room type, to print or copy to the website.

From the query Tariff, a group for each Room Type: Season, Months, Plan, Weeknight Rate, Weekend Rate.

Commission statements by booking site

Each booking site's statements over the last year, with the total commission paid to it.

From the query Commission by month, a group for each Site: Statement, Month, Stays, Room Takings, Commission, Paid On, with the total of Room Takings, total of Commission.

Start here: the everyday tasks, step by step

The guide to this database printed: each everyday task and its steps, from the Start Here table.

From the table Start Here, a group for each Task: Step, What to do.

Print templates

What the business sends out, one for each record, with its own name, address and payment details at the head from the Business table. Print one from its form, or choose which to print under Print templates, or save them as a PDF. The words in braces are filled in from the record.