Results 1 to 6 of 6
  1. #1
    PSI-Xtro is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Posts
    3

    Top 1 Subquery Returns no results

    A simplified version of my data structure is as follows:

    Room
    Field Name Type
    UniqueID UniqueIdentifier
    Room Name Text



    Room Booking
    Field Name Type
    UniqueID UniqueIdentifier
    BookedDate Date
    CheckedIn Yes/No
    CheckedOut Yes/No
    RoomID UniqueIdentifier


    What I need to retrieve is the UniqueID of the most recent room booking for each room that has been checked in to.

    My query is as follows.

    select R.UniqueID as RoomID, (Select top 1 UniqueID from RoomBooking as RB where RB.RoomID = R.UniqueID and CheckedIn=true order by BookedDate DESC, UniqueID Desc) as MostRecentID
    From Room R

    If found the subquery instructions here, and adding the tip to also sort by the UniqueID in the subquery solved other issues I was having.
    http://allenbrowne.com/subquery-01.html

    The problem is that my MostRecentID always comes back null. I know I have records that meet the criteria because if I change the top 1 to a count(*) and remove the Order By clause, the query returns results.

    Where am I going wrong? How can I rewrite this statement?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I presume UniqueID in both tables are autonumber primary keys. If so, roomID in room booking cannot be unique - do you mean it is a foreign/family key?

    I don't see anything wrong with the sql as such, so suspect it is the data. Do you still get a count(*) result if you leave in the order by clause?

  3. #3
    PSI-Xtro is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Posts
    3
    Apologies, the fields are actually GUIDS and the RoomID field in RoomBooking is a foreign key reference to Room.UniqueID.

    And no, leaving the ORDER CLAUSE in causes the statement to fail because the query does not include EventDate as part of an aggregate function. Makes little sense to me.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    guids may be the problem - I also seem to recall somewhere that you can't just sort on guid's, you have to convert them to text first. what happens if you run this query?

    SELECT *
    FROM Room R INNER JOIN RoomBooking RB ON R.UniqueID=RB.RoomID

    Assuming you get results with a populated EventDate and CheckedIn=true, run your subquery for an identified room - substituting [Enter Guid] with your room uniqueid

    Select top 1 UniqueID from RoomBooking as RB where RB.RoomID = [Enter Guid] and CheckedIn=true order by BookedDate DESC, UniqueID Desc


    Not sure why you are ordering by uniqueid since it adds nothing - if you have two bookings on the same day, presumably you need a time element, the guid is not going to provide that - so try removing it.

  5. #5
    PSI-Xtro is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Posts
    3
    In this case, the guid sort is just there to make sure that access can differentiate between two records that would otherwise be considered equal. Apparently JET doesn't always return 1 record just because you specified TOP 1 in the query. That tip was provided in the article I mentioned in my initial post.

    Doing the statement with the inner join works fine. Doing the statement with the specific uniqueID works fine.

    This is why I'm so frustrated.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Apparently JET doesn't always return 1 record just because you specified TOP 1 in the query
    good point, but ignoring that for now, does it work if you remove the guid sort?

    Can you attach your db with some example data in it? If so, compact and zip before attaching

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-12-2012, 11:34 AM
  2. Replies: 4
    Last Post: 02-22-2012, 12:43 PM
  3. Query returns ro results
    By MichealShinn in forum Queries
    Replies: 5
    Last Post: 02-21-2012, 01:24 PM
  4. Replies: 13
    Last Post: 01-13-2011, 10:15 AM
  5. Subquery returns Memo
    By Brainmart in forum Queries
    Replies: 0
    Last Post: 03-09-2009, 07:32 AM

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