Page 1 of 2 12 LastLast
Results 1 to 15 of 16

A subquery inside a subquery

  1. #1
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    76

    A subquery inside a subquery

    Hi All, I wonder if someone can help me on this one.
    I am working on a system to manage the cleaning of holiday homes so I need a list of people arriving, leaving and changing over (if on the same day). I've done this but I'm afraid that for historical reasons the field and query names are a mixture of Spanish and English. So general arrivals are LlegadasEnGeneral, general departures are SalidasEnGeneral and from these two queries I have made the following for changeovers (when there is an arrival and a departure at the same property on the same day). Changeovers are Cambios. The queries and tables are linked by CusCode which is the property, to find when these occurrences are at the same place.
    LlegadasEnGeneral, SalidasEnGeneral and Cambios work but I now need ONLY arrivals not arrivals in general, in other words, arrivals where there is no changeover because the property is empty. I thought this would be simply a matter of changing the subquery slightly to say
    Code:
    WHERE (([qryLlegadasEnGeneral].[Arrive] <> [qrySalidasEnGeneral].[Depart]))
    but no such luck.

    Here is the code I have to find the Cambios (Changes).



    Code:
    SELECT qryLlegadasEnGeneral.Arrive, qrySalidasEnGeneral.Depart, qryLlegadasEnGeneral.Cuscode, qrySalidasEnGeneral.Cuscode, qryLlegadasEnGeneral.ArrTime, qryLlegadasEnGeneral.PartyName, qryLlegadasEnGeneral.Pax, qryLlegadasEnGeneral.LimpCode, qryLlegadasEnGeneral.Extras, tblCustomers.LANZADDRESFROM tblCustomers INNER JOIN (qryLlegadasEnGeneral INNER JOIN qrySalidasEnGeneral ON qryLlegadasEnGeneral.Cuscode = qrySalidasEnGeneral.Cuscode) ON tblCustomers.CusCode = qrySalidasEnGeneral.Cuscode
    WHERE (([qryLlegadasEnGeneral].[Arrive] = [qrySalidasEnGeneral].[Depart]))
    ORDER BY qryLlegadasEnGeneral.Arrive;
    Thanks for any help but please bear in mind I'm new to Access and SQL.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,457
    Welcome,
    Here is a link to reservation/booking sample code that may be helpful.

    PBaldy has a link to show time periods and overlaps which highlights the concepts involved.

    When you post, it is often best for you and readers if you describe the overall business generally, then the specific issue in context.
    Even show some sample data --what goes in , what you want out. A high level model of the "things" involved and how they relate can help communication.

    It's hard to work with code in isolation.
    Good luck with your project.

  3. #3
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    76
    Thank you Orange,
    I have looked at the links and they do seem to be simpler than my attempt. I've tried substituting my Cuscode for the roomid as they are the same concept. Arrive, depart and cuscode are all in my tblBookings so there are no join issues. So the trial code looks like
    Code:
    SELECT tblBookings.CuscCode, tblBookings.Arrive, tblBookings.Depart
    
    FROM tblBookings
    
    
    WHERE 
    
    
    tblBookings.Cuscode NOT IN
    
    
     (Select Cuscode from tblBookings where
      Arrive = Date() AND
      Depart = Date())
    Looking at this, it should show me where there are no bookings for any CusCode where the depart date and arrive date are the same (today). It doesn't though, instead there is a parameter request for a CusCode and when I enter one, the query displays all of the dates for the CusCode even though there are entries for arrivals today, where there is no departure.
    I do appreciate your comments about more information, I thought it looked off-putting as it was !
    The system has to produce lists for the cleaners, arrivals, departures, changes and services (mid-week cleans). The lists contain this information for just the coming week and the cleaners then get a new list. tblBookings contains the Customer code (property CusCode) party name, arrival and departure dates and number of persons arriving at what time. So it's been fairly easy to pull out the records by 'Date() between' on arrive and depart. The cleaners do need to know about changeovers so that they know that they have to do that job on that day, between the departure and the new arrival. The arrivals only, which is what this question is about, can be done the day or 2 before because the property is empty. I hope this helps you and members a bit more.
    Thanks again.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,457
    I've been off internet for 24 hours (freezing rain).
    Please show us your table design and some data or a copy of the database.

  5. #5
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    76
    LPS 13042018 1520 for zipping.zipHello again, Orange, sounds as if you've had some interesting weather.
    Here is the database, I've removed some tables for d.p. so there will be lots of things that don't work and missing relationships but I don't think it will affect what I am trying to do right now.
    I am doing this for my daughter whose business is property management and has been doing for many years, so some of the aspects are the way that they are for historical reasons. Her business is in the Canary Islands so the few staff are Spanish which is why the field names are in Spanglish. The purpose of most of the queries related to the tblBookings table are for the cleaners and to tell them who is arriving, what date, what time and what needs doing. The 'what needs doing' part is demonstrated to them by a cleaning code (LimpCode) and although that and the number of people arriving (Pax) are needed on the reports, they don't feature as a part of the queries apart from just being shown.
    I have made queries called qryLlegadasEnGeneral and qrySalidasEnGeneral which are general arrivals and general departures, they, in actual fact contain all the data needed for the rest of the queries but I am stuck on qryCambios (changeover of parties, where there is an arrival AND departure on the same day) and qrySoloLegadas
    (where the property is vacant for a day or more before the arrival and so doesn't HAVE to be cleaned on the arrival day). The sum of changeovers and standalone arrivals is of course the General Arrivals. Almost all tables are linked on CusCode which is the short text customer code.


    I thought that I had cracked the changeover sub-query on the above basis, but on extensive testing, it occasionally includes an arrival that isn't a changeover. I also thought that a 'WHERE NOT' statement instead of 'WHERE', would give me the Only Arrivals query, but no, 'twas not to be.


    I have a feeling that the reason may be that the sub-query part, in brackets, is in the wrong place and the parts of the query need swopping about. So I've tried some experiments on that basis but I can't seem to get any further because I clearly don't understand sub-queries sufficiently (or at all).
    Thanks for getting back to me, I hope the weather's a little better now.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,457
    Quick comments -- just looked at the database; did not try to understand the logic.
    -Every table should have a Primary Key
    -tables that contain fields such as Other1, Other2, Other3 raise a red flag suggesting non Normalized table structure

    It would be helpful if you could take 1 or 2 sets of conditions and tell us what you want to do (plain English), and where exactly it seems to be going off the rails.
    I'll look at it but we are sort of working in the dark.

  7. #7
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    76
    Hello again,
    Thanks for looking. I am aware of normalisation but I'm equally not yet finished with the database, I have got everything working except the question in hand though. I also had to remove a couple of primary keys that were part of relationships when I removed tables. I thank you for pointing these things out though, and I will revisit them.
    For the moment, you only need to look at the tblBookings table and the associated queries. The query called qryLlegadasEnGeneral pulls the 'Arrive' field records that are between today - 1 and today +20. Run the rptLlegadasEnGeneral which is the recordset that you requested, and you see the results showing arrivals during those dates. Run rptSalidasEnGeneral and you should see the other record set, Departures between today - 1 and today + 10. This comes from qrySalidasEnGeneral. What I am trying to do in SQL is find;
    a) Properties (CusCodes e.g. 005, 025 etc.) that are actually each an accommodation, where there is a departure AND an arrival at that same CusCode (property) on that same date, and;
    b) The reverse of the above, i.e. where the arrivals are NOT on the same date as an arrival during that same period for that same accommodation.

    So the first will give a list that says 'These property numbers have an arrival within the next couple of weeks where there is a departure on the same day', and then;
    'These are properties where there are arrivals in the next week or so, that do NOT have a departure at the property on the same day as the arrival.'
    Hope this helps, I do appreciate that I know exactly what I want, but conveying it succinctly without rambling isn't easy for me.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,457
    I am not following the logic.
    Why are there Bookings with no CustomerID?
    You don't need to create a report to see which records match some stated criteria.

    Please tell us exactly what this means
    where there is a departure AND an arrival at that same CusCode (property) on that same date

  9. #9
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    76
    Hello again,
    I honestly don't know how to explain this in a very different way but I'll try. Did you open 'rptLlegadasEnGeneral' and 'rptSalidasEnGeneral' and the underlying queries of the same names? In the first report, you see a list where the first column is of Customer numbers, let's say owner numbers, customer code numbers of owners of a property that they let out to holidaymakers. The rptLlegadasEnGeneral's list starts with 073, 047, 005 etc..The list is ordered by the date of arrival of the groups of holidaymakers about to arrive in the near future.


    The second is the same but ordered on the departure field (depart) i.e. the day that a party of people go away after their holiday. This in effect, is so the cleaners know that people are leaving and they can collect the dirty laundry and clean up.


    For operational purposes the cleaners (they get these lists every week) need to know if they can clean and prepare a property a day or two before the arrival. This will ease the workload on the days where there is an arrival AND a departure, to and from the same property, on the same day.


    So the first query will answer the question 'Give me a list of properties (owners/customer codes) where there is an arrival which is only an arrival of people on the specific date'?


    The second will ask 'Give me a list of properties (owner's accommodation) where there will be a departure of some holidaymakers AND an arrival of a different group of holidaymakers to that same house (CusCode) on that same date.


    If you take the 'rptLlegadasEnGeneral' and you manually cross out the result of question one for the next 8 days, you should be left with the results of question two. In other words if the arrivals aren't just arrivals, they must be changeovers of groups on the same date. I don't know whether it would be that simple in code.


    If you look at arrivals (rptLlegadasEnGeneral) and departures (rptSalidasEnGeneral) you should see that, for instance, 027 has an arrival where there is no previous departure date of previous people since 18-04-2018 so the property is free from 18-04 until 26-04 for any work that needs doing. The same applies to 016 arriving on 29/04. These codes would be included in the answer to question 1.


    And for instance, 073, 039, and 025 are properties where people arrive maybe only a couple of hours after the previous group has departed, but on the SAME date, these properties would be in the answer to the second question.


    This was the case on 15-04-2018 when the lists that I am looking at were created and I don't think it will have changed much since then.


    Does this help? Hope so.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,457
    tbj,
    Freezing rain and 4 inches of snow later.

    Try this query -- just test it -- don't change your code or set up.
    Just run this as a test.

    Code:
    SELECT Cuscode
    	,arrive
    	,depart
    	,datediff("d", arrive, depart) AS stayLength
    	,PAX
    	,limpcode
    	,Partyname
    	,datediff("d", depart, nextarrival) AS FreeDaysBefore
    	,(
    		SELECT TOP 1 Dupe.arrive
    		FROM tblbookings AS Dupe
    		WHERE Dupe.cuscode = tblbookings.cuscode
    			AND Dupe.arrive >= tblbookings.depart
    		ORDER BY Dupe.Depart ASC
    			,Dupe.cuscode
    		) AS nextArrival
    FROM tblbookings
    ORDER BY cuscode
    	,arrive;
    What this code is intended to do:
    -using only the tblBookings
    For each cuscode and booking
    Select some fields from your bookings,
    identify the length of stay in days,
    identify the number of "free" days between bookings (I did not work with hours)

    Sample output:
    Click image for larger version. 

Name:	SampleShowingDaysBetweenArriveDepart.png 
Views:	16 
Size:	47.8 KB 
ID:	33633

    This should show the days between Depart and Arrive for each CusCode/property. If acceptable, then
    the "Cambios" are those records where FreeDaysBefore is 0.

    Snowing again this morning.
    Last edited by orange; 04-19-2018 at 04:16 AM. Reason: updated weather report

  11. #11
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    76
    That's very good, I would never have thought of doing it that way! Time on the day is of no importance because 99% of these people arrive and depart by air so arrivals could actually be before departures, which is up to the owner to manage before accepting the booking.


    So anything where 'FreeDaysBefore' = 0 is a changeover, and everything where 'FreeDaysBefore' is any positive number is just an arrival only, brilliant. This could also be used in the future to alert us of a lack of future bookings where any CusCode = 'null' is only a few days away.
    I just need to find a way of selecting the correct record for the changeover (the arrival part) This would be Cuscode = tblBookings.Cuscode and (query1.Arrive = nextArrival) AND (Between Date()-1 AND Date()+10) would it? Would I put this (if it's correct) above SELECT Cuscode,arrive and put that section in brackets around everything else?


    Thank you again.

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,457
    Sometimes it takes someone with no knowledge of the details to try a solution.

    I have a query called qCambios that uses the query sent in the last post.
    That query I named qFreedaysBeforeNextArrival

    so the sql for query qCambios is
    Code:
    SELECT qFreedaysBeforeNextArrival.*
    FROM qFreedaysBeforeNextArrival
    WHERE (((qFreedaysBeforeNextArrival.FreeDaysBefore)=0));
    Here is a revised qCambios that will identify all Cambios/changeover (same day arrive depart)
    You enter a parameter TheDate in MM\DD\YY format and the calculation is for 1 day previous through 10 days forward.

    Code:
    Parameters TheDate date;
    SELECT qFreedaysBeforeNextArrival.*
    FROM qFreedaysBeforeNextArrival
    WHERE (((qFreedaysBeforeNextArrival.FreeDaysBefore)=0))
    and nextArrival between TheDate -1 and TheDate +10;
    I also noticed that in the previous query, the StayLength has to be increased by +1 since it includes the arrive date.
    Here is the revised code that accounts for arrive date in the StayLength.

    Code:
    SELECT Cuscode
        ,arrive
        ,depart
        ,datediff("d", arrive, depart) + 1 AS stayLength
        ,PAX
        ,limpcode
        ,Partyname
        ,datediff("d", depart, nextarrival) AS FreeDaysBefore
        ,(
            SELECT TOP 1 Dupe.arrive
            FROM tblbookings AS Dupe
            WHERE Dupe.cuscode = tblbookings.cuscode
                AND Dupe.arrive >= tblbookings.depart
            ORDER BY Dupe.Depart ASC
                ,Dupe.cuscode
            ) AS nextArrival
    FROM tblbookings
    ORDER BY cuscode
        ,arrive;
    Revised sample output"

    Click image for larger version. 

Name:	RevisedSampleArriveDepartNextArrival.png 
Views:	15 
Size:	49.0 KB 
ID:	33646


    Here is a sample using the revised qCambios with 4/17/18 as the parameter value.
    Note: You must use US Date format MM/DD/YY

    Click image for larger version. 

Name:	qCambiosWithParameter.png 
Views:	14 
Size:	26.6 KB 
ID:	33647

    If you just want the number of arrive and departs in the next time period, and the number of free days between
    you could remove this from qCambios
    Code:
    (((qFreedaysBeforeNextArrival.FreeDaysBefore)=0))
    and
    Good luck with the project.

  13. #13
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    76
    Thank you so much, Orange, not only for the expertise, but for the patience. I've only just got in and I won't be able to look seriously tonight, but I've had a quick look and I'm sure I will be able to sort it out over the weekend. Thanks also for your good wishes, but whilst you're still a bit familiar with my project can I ask your opinion before I let you go?
    You will remember you asked my why there were only a few customer ID's in the Bookings table, here is the reason. I propagated the bookings table with some bookings and only after that, realised that almost all my tables had the Cuscode, which is a text field, as what looked to me as the 'natural' linking field. So I then decided to add a number field to the bookings table from what WAS the auto number field in the customers table. I later changed my mind before I had entered all the numbers to the existing bookings records. I thought it would be better to use a number field to 'represent the Cuscode', but without the zeros. So "047" would be 47 etc.. The purpose of which would give me some number fields to link to auto number fields in other tables so that I don't have to use Cuscode for everything.
    My question is 'when I finish this off, would you use a conversion IN the table so that when someone enters a cuscode, the corresponding number ID field is created every time (strip off the 0's and convert to numeric)'? I know they say not to do calculations in tables but it would be less work each time a booking is entered. Or would it be better to create an query and gather the number from the customers table because there it only ever needs to be entered once?
    Thanks again, and have a good weekend.

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,457
    Glad it is helpful.
    As for your database, if I understand the basics, you have Properties that are leased/rented to Customers for specified time periods. These leases/rentals can be thought of a Bookings.
    You are also concerned with cleaning/support staff for maintenance purposes (which I have not dealt with in any detail). You also have some rates/terms to be applied for each Booking and for payment of staff for work performed.

    I'm going to suggest this link on Database Planning and Design for info that will help with concepts. I like to start with a description of the business, starting at an overview level and gradually adding details. Then, identify the subjects involved and get some idea of how they relate to one another. This link (which is contained in the previous link) will lead you through the process.

    Each Subject should be in its own table. Every table should have a primary key that uniquely identifies each record in that table. Relationships are a representation of your business rules/facts. I treated your CusCode more like PropertyID--since that's what it meant to me.

    It all starts with a clear description of the business.

    Sometimes, when you are so familiar with the business, you don't necessarily see individual trees ---its all one forest. And if you are new to database and the software, you tend to jump in. Like most things it's experience and some recognition/understanding of concepts that brings you to improve designs and a more methodical approach to development. Since you have a database, you could
    start to work on design of version 2. It would be a good learning exercise and you'd be amazed with the amount of analytical detail you already have. But you know your environment and what you want to do with your time better than readers.

    Some of the videos and tutorials in the link I provided will help you decide on your next steps.

    The forum is always available for help and advice ---especially to those who want to learn and put in a honest effort.

    This is how I see a quick draft model of your set up.

    Click image for larger version. 

Name:	BookingsDraft2.png 
Views:	11 
Size:	30.8 KB 
ID:	33658

    Good luck.
    Last edited by orange; 04-20-2018 at 03:50 PM.

  15. #15
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    76
    Thanks again Orange.
    I integrated the code and everything is working - for this week's data anyway. I did notice that the date format seems irrelevant to Access, my Access is set up for UK dates and it seems to know the data required. I tried with UK and with US formats with equal results, probably works with Japanese format too !
    Thanks also for your advice which I requested, your layout of the database is extremely close to the way I will approach the project in V2. Also for the link, I will certainly read up on it.
    Hope your weather improves soon,
    Regards,
    TBJ.

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

Similar Threads

  1. Subquery Help
    By MTSPEER in forum Queries
    Replies: 1
    Last Post: 06-05-2017, 08:54 AM
  2. Subquery Inside Insert query statement
    By Sitakanta1989 in forum Access
    Replies: 4
    Last Post: 05-04-2015, 11:05 PM
  3. Subquery
    By Daryl2106 in forum Access
    Replies: 20
    Last Post: 01-13-2013, 06:21 AM
  4. Subquery
    By tomclavil in forum Queries
    Replies: 3
    Last Post: 02-27-2012, 02:05 AM
  5. Subquery
    By combine21 in forum Queries
    Replies: 2
    Last Post: 09-17-2010, 03:33 PM

Posting Permissions

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