Trouble finding the most recent prior booking for a given rental accommodation

[Tattybecks]

Hi,
I am usually capable of getting to the bottom of things in access but this one has me totally confused. I just cant seem to get my head around how to structure the query and any help would be appreciated.
My data comes from three tables:

• tbl_property contains
  
  • propertyID
  
  
• tbl_booking contains
  
  • bookingID PK
  • propertyID FK
  • bookingStartDate
  • bookingEndDate
  
  
• tbl_guest
  
  
  
  • GuestID PK
  • bookingID FK
  • guestMobie (text field contain guests mobile number)

For each booking that we have we change the key safe number of the property to the last 4 digits of the customers mobile. On the daily reports we use for the cleaners this is detailed simply using the code

right([guestMobile], 4)

What I can't work out is how to add the old key safe number. i.e. the key safe number from the most recent booking in the property. What I am trying to obtain is:

For each booking in the bookings table

SELECT the last 4 digits of the guest mobile number
FROM prior booking
WHERE the prior booking is the most recent prior booking AND start date is less than the current bookings start date
AND the prior bookings propertyID is the same as the current bookings propertyID.

I hope someone can help at least point me in the right direction. I managed to write the SQL to check for double booking but I can not do this.
kind Regards
T

[ranman256]

wouldnt it be the most recent BookingID using Phone4 (and you can thro in PROPid too)

[Tattybecks]

Unfortunately not. The bookingID's are applied as the bookings are made so a booking in August could have a lower ID than a booking next week. It has got to be done by finding the most recent prior booking for the property using the bookingStartDate. I tried to start simply like you suggest but I keep ending up in a mess. I think it is because the the data i am looking for has got to come from the most recent of the prior bookings in a property

[Micron]

You probably need a subquery written in the date field. Take a look here http://allenbrowne.com/subquery-01.html

[Tattybecks]

HI Micron,
Thank you for the link. It looks like I need to use the gt values in another record option. I will give this a go adn get back to you.

[Tattybecks]

HI Micron,
Thanks for your help earlier. Having used the allenbrowne link I have managed to get the data I require however I think there may be a far more efficient way of doing it.

My first query

SELECT tbl_booking.bookingID, tbl_booking.propertyID, tbl_booking.guestID, (SELECT TOP 1 Dupe.bookingID FROM tbl_booking AS Dupe WHERE Dupe.propertyID =tbl_booking.propertyID AND Dupe.bookingStartDate < tbl_Booking.bookingStartDate ORDER BY Dupe.bookingStartDate DESC) AS PriorBooking, (SELECT [tbl_guest].[guestMobile] FROM [tbl_guest] WHERE [tbl_booking].[guestID] = [tbl_guest].[guestID]) AS NewMobile, Right([NewMobile],4) AS NewKeySafe
FROM tbl_properties INNER JOIN (tbl_guest INNER JOIN tbl_booking ON tbl_guest.guestID = tbl_booking.guestID) ON tbl_properties.propertyID = tbl_booking.propertyID
WHERE (((tbl_properties.IsCleaned)=True) AND ((tbl_booking.bookingStatusCode)=3 Or (tbl_booking.bookingStatusCode)=5));

gets the booking number for the booking immediately preceding each booking. It seems that you cant use this subquery result to calculate another column so my second query

SELECT (SELECT [tbl_booking].[guestID] FROM [tbl_booking] WHERE [tbl_booking].[bookingID] = [qry_bookingsList1].[PriorBooking]) AS OldGuest, *
FROM qry_bookingsList1;

gets the guest Id for the previous booking. The third query

SELECT qry_bookingsList2.OldGuest, qry_bookingsList2.bookingID, (SELECT [tbl_guest].[guestMobile] FROM [tbl_guest] WHERE [tbl_guest].[guestID] = [qry_bookingsList2].[OldGuest]) AS OldMobile, *
FROM qry_bookingsList2;

then guests the previous guests mobile number form which can be extrapolated the previous key safe code. i.e. the last 4 digits.
When I run the 3rd query however my computer lags and access stops responding for about 20 seconds so i'm guessing that although I have achieved my result it has not been done in the right way? Have you any advice please?

[Micron]

If you want to upload a copy of your db I will see if I can tweak it. Me and subqueries are not on good terms, but I suppose I could use the practice. If there's sensitive info, use an update query to change names to Daffy, Donald, Daisy, whatever, or all phone numbers to 123-555-1212 etc.

[Tattybecks]

ReMaHolidays Booking System - Copy.zip
Thank you very much for your help with this. I really appreciate it.
Cheers
Tattybecks

[Micron]

I'd rather start from scratch first, as opposed to trying to follow your method, especially if you're hoping for a better method. It's just that I can't make sense out of

For each booking in the bookings tableSELECT the last 4 digits of the guest mobile number
FROM prior booking
WHERE the prior booking is the most recent prior booking AND start date is less than the current bookings start date
AND the prior bookings propertyID is the same as the current bookings propertyID.

There is no "prior booking" table, there are several booking tables, and tblbooking has no phone numbers. Can you clarify what has to come from where?

[Tattybecks]

HI Micron,
The current and prior booking should come from the table tbl_booking

So i think we need two copies of the table tbl_booking. The original and a dupe.

Then for each record in tbl_booking find in the dupe table (this is what I mean by the prior booking table) the previous booking. This will be the booking where the property id's match and the arrival dates are the closest. For each record in tbl_booking the sub query will return a list of all bookings whose property ID's match and whose holiday start date is before the start date of the booking in the record. If that list is sorted by bookingStartDate ascending and we take the last record from the list we must have the previous booking in that property.

Once we have the most recent booking for each record in the table tbl_booking we can then using GuestID find the mobile number for the previous guest and therefore the last 4 digits of the number.

Does that make more sense.

Example

BookingID	PropertyID	StartDate	GuestID
1	1	3-Aug-18	1
2	2	6-Jul-18	2
3	1	2-Apr-18	3
4	1	8-Jun-18	4
5	1	9-Jul-18	5
6	2	1-Jul-18	6
7	2	15-Jun-18	7

From the example data above if we take booking ID 1 then the sub query of previous bookings would return the following results:

BookingID	PropertyID	StartDate	GuestID
3	1	2-Apr-18	3
4	1	8-Jun-18	4
5	1	9-Jul-18	5

This table is already sorted by startDate so the most recent booking in the property with the propertyID 1 is the booking with the bookingID 5. From this we can take the guest ID of 5 and use this to extract the guest mobile number form the the table tbl_guest.

Cheers
Matt

[ssanfu]

PMFJI....

I would suggest you step back and fix the errors in the code before moving on.
EVERY code module should have these two lines as the first two lines

Option Compare Database
Option Explicit

Then do a DEBUG/Compile to find the errors. I added "Option Explicit" to the form module "frm_bookingNew" and found 2 errors.
In the sub "btn_CloseForm_Click", there is a line

        If DLookup("ReMaReference", "tbl_booking", "ReMaReference= '" & ourBookingRef & "'") > 0 Then
            MsgBox "The booking reference " & ourBookingRef & " already exists and can not be used again. Please enter a new booking reference."
            Me.txt_RemaRef.SetFocus
            Me.txt_RemaRef.SelStart = l  '<<--this is a lower case l (ell) and it should be a 1 (one)
            Me.txt_RemaRef.SelLength = Len(Me.txt_RemaRef)
            allowClose = False
            GoTo Exit_closeForm
        End If

and in the sub "txt_bookingID_Change" there is an error in the line

    Forms!frm_bookingGuests!txt_bookingID = Me.txt_bookingID
    Forms1frm_bookingFinacials!txt_bookingID = Me.txt_bookingID   '<<-- there is a 1 (one) after FORMS instead of an exclamation mark

There are probably/possibly more errors, so add the "Option Explicit" line and compile until no more errors.
You can set a switch to always add "Option Explicit" when a NEW module is created.
In the IDE, Tools/Options/Editor Tab - "Require Variable Declaration".

I also have concerns as to why you have tables in a one-to-one relationship.
I also question the use of compound PK fields. See Microsoft Access Tables: Primary Key Tips and Techniques




Now for your question. Instead of trying to use queries, I used VBA to write a UDF ("fGetPrevSafeCode").
It gets the previous last 4 of the mobile number and the current guest mobile number.

I added a couple of text boxes to the booking form.... maybe it is close to what you are looking for.

I also changed the mobile numbers to be able to see if the function was working. (used the code in Module1 that I wrote - the module can/should be deleted)

[Micron]

WHERE the prior booking is the most recent prior booking AND start date is less than the current bookings start date

I think this is not possible/reliable using the table as a source.

I do have an almost working query/subquery. However, if you sort tbl_booking by property ID, you should see

bookingID	ReMaReference	SourcebookingID	propertyID	Guest	Booking Status	dateBooked	bookingSource	StartDate	EndDate
92	B001568	1574855068	2	71	3	1/06/18	2	7/21/18	7/26/18
131	B001424	1145610070	2	104	3	1/09/18	2	8/24/18	8/26/18
140	B001566	1226187510	2	112	3	1/06/18	2	9/08/18	9/10/18
147	B001578	1351229926	2	18	3	1/25/18	2	4/07/18	4/10/18

Note that for property 2, as the bookingID increases, so does the startdate, which at first makes sense - until id 147. Its startdate is less than the previous booking. At first, I thought this was bad data, but then figured it would be possible to book property 2 for September, then at a later time (thus a higher booking id) book it for an earlier open startdate than what the most recent bookingID is.

BUT
what is the "current booking's start date" (see quote above). What makes a booking "current"? I would think that a booking can be in the future, and you want to pass on the safe digits based on the prior renter, but to my mind, that booking isn't necessarily "current" - especially if there is a gap in dates that suddenly gets filled (property rented). Now you need a different set of digits because you inject a renter before the next upcoming renter.

I'm going to hold off until this has been dealt with in a response from OP. Too bad 'cause at first I thought I nailed it after several hours of playing around (told you, me and subqueries are not on good terms). I think this is something that you might be able to do on a singular basis if you pass the propertyID to a query, but only at specific points in time. Perhaps this was envisioned and the code solution has taken care of it. I haven't had the time to look.

ssanfu: as for PMFJI, AFAIC, your insight is always appreciated.

[Tattybecks]

HI Micron,
I really do appreciate the time that you have spent on this for me.
I now understand where you might be having an issue.
I may not have explained well enough to begin with. I do not need the information to be up to date in any table. The reason for needing the data is to send a list of the changeovers (property cleans) to the cleaners. So for instance I would, on a Monday, produce a report for the cleaners, showing the properties that, in the following week, have new guest arriving. In order to do this the cleaners will need to know the keysafe number (last 4 digits of the mobile number) for the person who stayed in the property last.
So it would only need to produce the data at the fixed given time that the report was produced. It would then update that information when the report was reprinted again if a new booking happened that was closer to the start date of the new holiday.
On that basis the logic would be:

For each booking in tbl_booking that has a startdate > than today()
- Find the booking that happened in the same property that has the closest start date
-- And get the guest ID for that booking in order to extrapolate the guest mobile number.

This information does not need to be stored or recorded as it it is only to then produce and print a report that I will keep reproducing as new bookings are added.

Does this information make life any easier?

I have taken onboard ssanfu's suggestions and I am looking at his updated database at the moment. Will let everyone know how I get on.

Cheers
Tattybecks

[Tattybecks]

HI Ssanfu,
Thank you for taking the time to look at my problem. I really do appreciate it.

I have added the "option Explicit" line to all modules and compiled until no more errors. As you say a good place to start. I have also removed all compound PK fields and added a unique PK to each record. Having gone through the tables I am left with only two tables in a one-to-one relationship with the table tbl_booking. These are tbl_bookingGuests and tbl_bookingFinances. My reason for this is that each booking can have only one group of guests and each booking can have only one set of financial criteria and vice versa each set of financial criteria can only apply to one booking and each group of guests can only apply to one booking. All other tables are now in a one-to -many relationship.

If you have a look at my last response to micron i think I may have cleared up the when and why of needing this data and that that might make finding a solution easier. I do not need to store the result but just find the answer at a given point in time for every future booking so that I can send the details to the cleaning company. As more bookings are added I can send updated reports that reflect the changes.

Thanks again for all your help.

Kind Regards
Tattybecks

[ssanfu]

Could you post your latest dB?