none
Access WEB App IN operator and subqueries RRS feed

  • Question

  • I have an app where users can make a booking for a sailing session.  I can list all sessions (in tblSessions) but cannot exclude sessions they have already booked (contained in tblBookings).  This works fine using NOT IN (subquery) in desktop Access even working to a Sharepoint database using ODBC but the WHERE clause to filter a pop up view in an Access App does not support the IN operator or subqueries. Is this likely to be the subject of future development?  Has anyone got any suggestions for a workaround?
    Wednesday, November 9, 2016 6:12 PM

Answers

  • Hi Colin,

    As you have known, we could not use in or not in in subqueries.

    >> I can list all sessions (in tblSessions) but cannot exclude sessions they have already booked (contained in tblBookings).

    For this requirement to get records in tblSessions but not in tblBookings, I suggest you follow below steps for a work around.

    1. Create a create by adding table tblSessions and tblBookings
    2. Add right join relationship between these two tables on the key you check whether the seesion is in tblBookings by drag and drop the field from tblSessions to tblBookings.
    3. Add criteria “Is Null” for key in tblBookings.

    Here is a screen shot for join action.

    In above image, you could select the second option to use right join, and add "Is Null" for id in table2.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Colin_W Monday, November 14, 2016 1:03 PM
    Thursday, November 10, 2016 6:56 AM

All replies

  • Hi Colin,

    As you have known, we could not use in or not in in subqueries.

    >> I can list all sessions (in tblSessions) but cannot exclude sessions they have already booked (contained in tblBookings).

    For this requirement to get records in tblSessions but not in tblBookings, I suggest you follow below steps for a work around.

    1. Create a create by adding table tblSessions and tblBookings
    2. Add right join relationship between these two tables on the key you check whether the seesion is in tblBookings by drag and drop the field from tblSessions to tblBookings.
    3. Add criteria “Is Null” for key in tblBookings.

    Here is a screen shot for join action.

    In above image, you could select the second option to use right join, and add "Is Null" for id in table2.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Colin_W Monday, November 14, 2016 1:03 PM
    Thursday, November 10, 2016 6:56 AM
  • Hello Edward

    Many thanks for your quick response to my problem.  It has led me to find a solution that uses the principle of your method.  My tables are as follows:

    tblSessions:  Session_ID + other fields
    tblBookings:  Booking_ID, Session_ID, Memb_ID (the membership number of the user)

    I created a parameterised query 'qryBookings' with a parameter 'MembNo' linked to Memb_ID.

    I used your example  to create a 2nd query 'qrySessions' where Table1 = tblSessions and Table2 = qryBookings.  The criterion for this query is Booking_ID is null.

    I was then able to create a datasheet based on qrySessions that opens with a pop up view where the parameter MembNo = the value from an Autocomplete control.

    Now the list of sessions excludes those already booked by the user - exactly the aim of my design.

    Brilliant!  Thanks again.

    Colin

    Monday, November 14, 2016 1:01 PM