Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    Tattybecks is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    14

    Hi,
    Latest db attached
    Cheers
    Attached Files Attached Files

  2. #17
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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:
    So the function in the dB I posted didn't meet your requirements?

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    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:
    Find the booking that happened in the same property that has the closest start date
    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).
    Last edited by Micron; 02-19-2018 at 07:50 PM. Reason: added questions

  4. #19
    Tattybecks is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    14
    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?

  5. #20
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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?
    Attached Files Attached Files

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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;
    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
    The select subquery and result for same:
    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;
    BookID propertyID
    119 3
    which looks right to me. I don't think you can use this because
    - 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.

  7. #22
    Tattybecks is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    14
    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

  8. #23
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Tattybecks View Post
    Bingo. Yeah,
    This is exactly what i was after.
    Excellent!

    Quote Originally Posted by Tattybecks View Post
    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?
    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.

  9. #24
    Tattybecks is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    14
    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

  10. #25
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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....
    Attached Files Attached Files

  11. #26
    Tattybecks is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    14
    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

  12. #27
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sounds like it is getting closer.


    I still have concerns about the table structures and relationships.


    RE:
    previous key safe number for any given property is the same. I.e for 4 Sandown Bay it was always 3731
    This is because there is (apparently) still test data in the tables.

    I was curious, so I did a lot of queries, looking at the data
    Take a look at this query:
    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;
    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.)


    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
    Code:
    sSQL = sSQL & " WHERE tbl_booking.propertyID = " & pProperty & " AND tbl_booking.bookingStartDate < " & fSQLDate(pBkStartDate) & ""
           sSQL = sSQL & "ORDER BY tbl_booking.bookingStartDate DESC;"
    I would write it as
    Code:
    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.
    Easier for me to see missing spaces between words.

  13. #28
    Tattybecks is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    14
    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

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need to pull query for 2 Status with recent and prior dates
    By undefatedskillz26 in forum Queries
    Replies: 7
    Last Post: 12-23-2014, 03:33 PM
  2. Rental Records
    By attman in forum Programming
    Replies: 1
    Last Post: 01-21-2014, 11:49 AM
  3. Tools Rental DB Help
    By abusaif in forum Access
    Replies: 6
    Last Post: 07-11-2013, 07:31 AM
  4. Trouble finding closest match
    By cutsygurl in forum SQL Server
    Replies: 1
    Last Post: 02-22-2013, 03:59 PM
  5. Trouble Finding "Oldest" Data
    By bigdan5428 in forum Access
    Replies: 5
    Last Post: 04-26-2012, 05:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums