Hi,
Latest db attached
Cheers
Hi,
Latest db attached
Cheers
So the function in the dB I posted didn't meet your requirements?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:
But, the booking that will be used to ID the renter and get the 4 digits from that renter, has to be a completed booking, yes? To do exactly as you describe (at least the way I envision that) could mean that your report will produce safe numbers for bookings that haven't even taken place yet (the renter has not yet occupied the property). Your statement "that has a startdate > than today" doesn't mean the booking has even started yet. There has to be something that distinguishes the one you want from the entire pack, but I'm not sure what that is. If you've clearly identified it, I'm missing it. Also, I can be as thick as a brick (OK, I mean I take very little for granted when helping out like this), meaning you need to be even more specific than what you seem to default to: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.closest to what? The date you want to create the report? The date of the last startDate for a property? Date of the last EndDate (which could make sense since the property would be vacated, thus ensure the digits you get belong to the last renter).Find the booking that happened in the same property that has the closest start date
Last edited by Micron; 02-19-2018 at 07:50 PM. Reason: added questions
HI Ssanfu and Micron,
Thank you Ssanfu for the function you posted in the dB but I do not think that that is what I am after.
It doesn't matter whether or not the booking has happened yet because the booking will still have a safe number based on the last 4 digits of the guests mobile number.
Let me try to explain in more detail.
We use a cleaning company to clean the properties for us. They have no access to our systems so rely on a report that we send them once a week containing a list of all the cleans that they need to carry out the following week.
That report, amongst other things needs to inform the cleaner of two vital pieces of information. The incoming guests key safe code ( so that the cleaner can set the new code for the new guest) and the outgoing guests key safe code (so that the cleaner can open the key safe to obtain the key.
It is the outgoing guests key safe code that we are trying to find here. It is solely for this report and does not need to be stored because we can always work it out from the guests mobile number.
So if on a given day we take a snapshot of all of the bookings whose start date occurs in the following week then
-Each of those bookings will be in a specific property
-That property would have had a previous booking with a different guest, the outgoing guest, (that guest may have left the same day, a week before or a month before if the property has been empty for a while)
-That previous guest would have had a key safe number allocated as the last 4 digits of their mobile number
So how do find that out in that snapshot. Who was the last person to stay in the property before the incoming guest and what was their key safe number.
If after the report has been produced someone else books a holiday and that makes them the most recent guest to leave the property then I understand that I would need to reproduce the report. What we are trying to achieve is a report that will give us the information that is accurate a given point in time. i.e when the report is produced
Does this help you guys at all?
It would be nice to have some safe numbers from you to be able to check, but look at this dB. I added a button to the main form.
Is this close(er) to what you want?
Well, I tried to make use of Allen Browne's example of getting the prior meter reading. However, your situation is such that you have not one "meter" but several (properties) so it pretty much negates me being able to do anything in sql without a lot of extra work as you'll soon see. I got rid of your table aliases in the beginning because I couldn't be sure if they weren't causing a problem, especially when you need to alias a table when you create self joins on it. I also created a query to put the start dates in descending order since the technique relies on using the TOP 1 predicate. However I could not get the desired result because of the "multi-meter" effect. What I was able to do is, for a given property, get the booking id just prior to the last one.
The ordering query and result for property 3:
Code:SELECT tbl_booking.StartDate, tbl_booking.propertyID, tbl_booking.bookingID, tbl_booking.EndDate FROM tbl_booking WHERE (((tbl_booking.propertyID)=3)) ORDER BY tbl_booking.StartDate DESC;The select subquery and result for same:
StartDate propertyID bookingID EndDate 10/20/18 3 141 10/24/18 8/17/18 3 119 8/24/18 8/06/18 3 109 8/10/18 7/29/18 3 100 8/01/18 7/24/18 3 98 7/28/18 6/21/18 3 78 6/25/18 5/18/18 3 56 5/21/18 2/01/18 3 168 2/02/18
Code:SELECT TOP 1 qryBookingSort.propertyID (SELECT TOP 1 T.bookingID FROM qryBookingSort AS T WHERE T.propertyID = qryBookingSort.propertyID AND T.StartDate < qryBookingSort.StartDate) AS BookID, qryBookingSort.propertyID FROM qryBookingSort;which looks right to me. I don't think you can use this because
BookID propertyID 119 3
- you don't want to have to run this property by property,
- nor create a code loop to do that (might as well write a whole code solution)
- it basically is restricted to the start date with the max value in the table
- and you want to filter the data based on a week from the report date, so it's getting too involved. I think the multi property thing is the main reason I couldn't get this to work. Can't say I didn't try. Let us know if the proposed code solution will work (haven't had the time to look at it and work on my own idea) or needs tweaking.
Hi Steve,
Bingo. Yeah,
This is exactly what i was after.
All of the lines in the report that have a previous and new key safe are accurate.
Ignore the first one on the report because the key safe at 149 Sandown Bay never changes but if you start at the second one: 3 Sandown Bay on the 19th March you will see that the new key safe is 3748. Then go to 30th March, which is the next booking in 3 Sandown Bay, you will see that the previous key safe code is 3748 which is the new key safe from the previous booking. This works so thank you for that.
What i am trying to figure out now is why there are so many unknowns because every booking has a mobile number and therefore your code should be able to get the last four digits.
Any ideas why it might be having problems with some of them?
You are a star and I really appreciate your help.
Cheers
Matt
Excellent!
Not sure what you mean. I looked at the dB in my last post (#20) and there was only 1 "Unknown", for 190 Sandown Bay .
If there are no earlier bookings for a property in the dB, there won't be a previous mobile number; thus the "Unknown". Other than that, I have no idea. I didn't have the dB you are now using to test with.
Check to see if there are previous bookings for those properties that have Unknown.
Hi Steve,
Again I am finding this quite interesting. I don't know if it is another one of access's quirks or a problem with individual records. I have included a PDF of my report to see if it returns the same report on your computer. You will see several bookings with UNKNOWN for the new key safe. Odd because the new key safe should be the easy one because it is from the current booking?
You will note that the same fields show as UNKNOWN on the frm_bookingNew which is why I think it may be an issue with individual records. I did compact and repair dB but this made no difference.
I also added a break point to the VBA code in module 1 and it seems that on the occasions when there is no "current safe code" produced on the form the module is only running once for the previous safe code. In other words it runs the module with the variable p for pCorP but not for the variable c.
Any ideas?
Cheers Matt
ReMaHolidays Booking System.zipCleaner Bookings Export.pdf
The report I get does not have all of the unknowns in it.....
rpt_bookingsCleaners.pdf
Doh! It just struck me - we are using different date formats. Access requires dates to be in American format (MM-DD-YYYY)
I added a function from Allen Browne for international date conversion to American format. (see Module1)
See if this fixes the problem....
Hi Steve,
Absolutely awesome. I really appreciate your effort on this one. We have achieved the final result. (at least my limited testing would suggest so.
Even with the dates function the results were still not right. If you have a look at the report you will notice that the previous key safe number for any given property is the same. I.e for 4 Sandown Bay it was always 3731 which was the initial key safe code. This got me to thinking that the issue was an ordering one and the top 1 result was being taken from the wrong end of a list.
I added an ORDER BY clause to the SQL statement to make sure that the most recent previous booking is always the first record in the data set and this now produces exactly the results that I am after.
If you run the report with the new code you will notice that, for properties where the key safe number changes the previous key safe number is always the current key safe number from the most recent booking in that property.
Your help has been invaluable.
Thank you once again
Matt
Code:Public Function fGetPrevSafeCode(pProperty As Long, pBkStartDate As Date, pCorP As String) As String 'pCorP = Current or Previous Dim r As DAO.Recordset Dim sSQL As String fGetPrevSafeCode = "Unknown" 'common SQL code sSQL = "SELECT TOP 1 Right([guestMobile],4) AS SafeCode" sSQL = sSQL & " FROM tbl_guest INNER JOIN tbl_booking ON tbl_guest.guestID = tbl_booking.guestID" Select Case pCorP Case "p" 'for previous booking sSQL = sSQL & " WHERE tbl_booking.propertyID = " & pProperty & " AND tbl_booking.bookingStartDate < " & fSQLDate(pBkStartDate) & "" sSQL = sSQL & "ORDER BY tbl_booking.bookingStartDate DESC;" Case "c" 'for current booking sSQL = sSQL & " WHERE tbl_booking.propertyID = " & pProperty & " AND tbl_booking.bookingStartDate = " & fSQLDate(pBkStartDate) & ";" End Select Debug.Print sSQL Set r = CurrentDb.OpenRecordset(sSQL) If Not r.BOF And Not r.EOF Then fGetPrevSafeCode = r("SafeCode") End If r.Close Set r = Nothing End Function
Sounds like it is getting closer.
I still have concerns about the table structures and relationships.
RE:This is because there is (apparently) still test data in the tables.previous key safe number for any given property is the same. I.e for 4 Sandown Bay it was always 3731
I was curious, so I did a lot of queries, looking at the data
Take a look at this query:
There are 15 properties that have a Mobile number pf "3531", all with a "BookingStartDate" of 1 Feb, 2018 and an "BookingEndDate" of 2 Feb, 2018 (or in American format -> mmdd,yyy - Start = 2/1/2018, End = 2/2/2018.)Code:SELECT tbl_buildings.buildingID, tbl_buildings.buildingName, tbl_guest.guestID, tbl_guest.guestFirstName, tbl_guest.guestSurname, tbl_guest.guestMobile, tbl_booking.propertyID, tbl_booking.bookingStartDate, tbl_booking.bookingEndDate, tbl_properties.isCleaningSubcontracted, tbl_booking.bookingStatusCode FROM (tbl_buildings INNER JOIN tbl_properties ON tbl_buildings.buildingID = tbl_properties.buildingID) INNER JOIN (tbl_guest INNER JOIN tbl_booking ON tbl_guest.guestID = tbl_booking.guestID) ON tbl_properties.propertyID = tbl_booking.propertyID WHERE (((tbl_guest.guestMobile)="3731")) ORDER BY tbl_buildings.buildingID;
Looking at this query, the earliest booking date for 15 properties is 1 Feb, 2018, ALL with a Mobile number of "3731".
Code:SELECT tbl_buildings.buildingID, tbl_buildings.buildingName, tbl_guest.guestID, tbl_guest.guestFirstName, tbl_guest.guestSurname, tbl_guest.guestMobile, tbl_booking.propertyID, tbl_booking.bookingStartDate, tbl_booking.bookingEndDate, tbl_properties.isCleaningSubcontracted, tbl_booking.bookingStatusCode, tbl_bookingStatus.bookingStatusDescription FROM tbl_bookingStatus INNER JOIN ((tbl_buildings INNER JOIN tbl_properties ON tbl_buildings.buildingID = tbl_properties.buildingID) INNER JOIN (tbl_guest INNER JOIN tbl_booking ON tbl_guest.guestID = tbl_booking.guestID) ON tbl_properties.propertyID = tbl_booking.propertyID) ON tbl_bookingStatus.bookingStatusID = tbl_booking.bookingStatusCode ORDER BY tbl_buildings.buildingID, tbl_booking.bookingStartDate;
Also look at the last 32 records of the above query (properties "149 Sandown Bay" and "190 Sandown Bay"), there are 22 records for guest David Prodger at "149 Sandown Bay". Look at the bookingStartDate.
Same for guest Tina Dunne at "190 Sandown Bay" (10 records). Look at the dates.
So, to me, it looks like a problem with the data?
--------------------------------------------------------------------------------
Good catch about the Order code. Don't know how I missed that.
I would make a minor change (for consistancy)
You have
I would write it asCode:sSQL = sSQL & " WHERE tbl_booking.propertyID = " & pProperty & " AND tbl_booking.bookingStartDate < " & fSQLDate(pBkStartDate) & "" sSQL = sSQL & "ORDER BY tbl_booking.bookingStartDate DESC;"
I have the space at the beginning (in BLUE) between the quote and the "O", instead of at the end.Code:sSQL = sSQL & " WHERE tbl_booking.propertyID = " & pProperty & " AND tbl_booking.bookingStartDate < " & fSQLDate(pBkStartDate) sSQL = sSQL & " ORDER BY tbl_booking.bookingStartDate DESC;"
Easier for me to see missing spaces between words.
HI Steve,
Sorry it has taken so long to get back to you. I have been so busy.
Firstly, I can assure you that there is nothing wrong with the data and secondly there is nothing wrong with your code. It is all working perfectly and seamlessly. Now let me try to explain.
The bookings for the properties 190 Sandown Bay and 149 Sandown Bay are for properties that are not owned by us. We also do not manage the bookings for those properties, we only manage the cleaning. The owners keep the key safe number the same for all of their bookings so I decided to just use the property owner as the guest and this populates the key safe number with the same number for every booking. It generates a booking on our system for the cleaners and a key safe coded for entry and exit.
The second issue of the multiple 3731 key safe codes was down to the issue of this being a brand new system. The first bookings in many of the properties had no previous booking and therefore no previous key safe code. To combat this I generated a fictitious booking each property lasting on night with me as the guest and a key safe number of 3731. I have then changed all of the key safes to this number so that the cleaners can gain entry on the first clean. I have set the fictitious bookings so that they do not appear in any queries or reports and so cannot mess with the data.
Im not sure if either of these solutions was the most glamorous way of overcoming each problem but it seems to be working.
Cheers
Matt