Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49

    Select only the top row from each group

    Hello everyone.


    I really struggle to find a way to do the following:

    Table: tbl_DialysisInfo
    DialysisInf_ID (PK, autonumber) Pt_ID (patient id) Info_Date DialysisMod_ID (number)
    1 5 23/11/2017 68
    2 6 12/10/2018 68
    3 6 05/09/2008 69
    4 5 03/07/2006 67

    I need a query that will select only the most recent record for every Pt_ID. In this example:

    DialysisInf_ID Pt_ID Info_Date DialysisMod_ID
    1 5 23/11/2017 68
    3 6 12/10/2018 68

    The resulting table will act as a lookup table in another query.

    Any ideas on this?
    Last edited by jabarlee; 06-02-2018 at 11:58 AM. Reason: wrong numbers

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Perhaps

    http://www.baldyweb.com/LastValue.htm

    though your example doesn't appear to match your description.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi

    First of all, with the data you supplied the results should be:

    DialysisInf_ID Pt_ID (patient id) Info_Date DialysisMod_ID (number)
    1 5 23/11/2017 68
    2 6 12/10/2018 68

    There are several ways of getting these results.
    One way is to use two queries:

    Query1: used to get the most recent date for each Pt_ID
    Code:
    SELECT tbl_DialysisInfo.[Pt_ID (patient id)], Max(tbl_DialysisInfo.Info_Date) AS MaxOfInfo_DateFROM tbl_DialysisInfo
    GROUP BY tbl_DialysisInfo.[Pt_ID (patient id)];
    Query2: join this to the original table to get all 4 fields for those 2 records
    Code:
    SELECT tbl_DialysisInfo.*
    FROM tbl_DialysisInfo INNER JOIN Query1 ON (tbl_DialysisInfo.Info_Date = Query1.MaxOfInfo_Date) AND (tbl_DialysisInfo.[Pt_ID (patient id)] = Query1.[Pt_ID (patient id)]);
    There are other ways but this may be the easiest to understand

    EDIT: Paul replied whilst I was typing - same solution
    Last edited by isladogs; 06-02-2018 at 11:32 AM. Reason: Added EDIT comment
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by ridders52 View Post
    EDIT: Paul replied whilst I was typing - same solution
    Takes longer to deliver the whole fish.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Certainly does - I was originally going for a one query solution but decided against it!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Thank you both for your replies.
    Paul, the title talks about the top row because I though that I could have the records sorted by Pt-ID, Date and then use something like SELECT TOP 1

    The solution you both gave me works fine, but I have the problem that the final query is not updatable. Is there any workaround for this?

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can try Allen's method:

    http://allenbrowne.com/subquery-01.html#TopN

    I wasn't talking about the title. You sample result didn't match the description of most recent. I see you've changed it now.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by jabarlee View Post
    The solution you both gave me works fine, but I have the problem that the final query is not updatable. Is there any workaround for this?
    The solution both of us gave was based on an aggregate query which are always read only i.e. not updateable.
    However if the underlying data changes, the results will 'update' automatically
    So, from your description, the solution is still valid

    I said there were other solutions including using a subquery as in Allen Browne's site, but I don't believe that will help you in this particular case
    Subqueries are usually also read only
    see http://allenbrowne.com/ser-61.html for a list of reasons why queries are read only
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I fail to see why you want the list of latest record of each patient to be updateable - sounds like you are trying to apply excel mentality - i.e. show every record, then user searches the list for the record they want to update - which in databases is slow and inefficient.

    @Colin - I believe a query will be updateable if the subquery is in the criteria, but I agree - not the select part of it. i.e.

    SELECT tbl_DialysisInfo.*
    FROM tbl_DialysisInfo
    WHERE DialysisInf_ID=(SELECT TOP 1 DialysisInf_ID FROM tbl_DialysisInfo AS Tmp WHERE Pt_ID =tbl_DialysisInfo.Pt_ID ORDER BY Info_Date Desc)

    should be updateable

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Ajax
    Yes I agree with you regarding which types of subquery are updateable
    Perhaps I should have said 'sometimes' rather than 'usually' but decided not to go into details as I couldn't see any reason for editing the results from the query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    First of all, thank you again for the answers.
    The second method produces the same result, but the query is updateable. I need this because, as I said earlier the resulting table will be a lookup table for another query that is involved in a continuous form. When my lookup query was not updateable, the related query was not updateable as well. I post a screenshot for you to see the relations.
    The qry_AllPatients_Modality query is the one that will be the data source for my continuous form and it will pull the most recent modality value from the dry_LatestModality. This value I want it to show up in my continuous form (and also in another, single form). I am not interested in editing this, but all the other values of the tbl_Patients.

    The first method presented seems easier to grasp for someone like me that has basic only knowledge of Access and SQL. Seeing the second method I understand the concept, but I wouldn't have figure it out on my own in any way.
    Click image for larger version. 

Name:	quey.jpg 
Views:	20 
Size:	70.5 KB 
ID:	34275

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK - the reason for it needing to be updateable makes more sense now.

    2 questions on looking at your query links.
    1. Do you really need all those tables? As a general rule, tables with an ID field and only one other field may not really be necessary. For example, isn't the gender table somewhat OTT?
    2. Do all the joins need to be outer joins?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Colin, I have minimal knowledge of database design and implementation so I probably do mot of the stuff the wrong way (and every piece of advice is highly appreciated)
    As far as lookup fields/tables , this way seemed the most obvious and easy. I wouldn't know to do it some other way. Maybe an example would help me.
    Isn't outer join the safest, in not only way to have my full list of patients, even if I don't have all the fields complete?
    For example, if I want to list all the patients with whatever info is available at the time, wouldn't I have to use outer joins?

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Undoubtedly with this setup, some will need to be outer joins if data is missing.
    BUT if you instead stored Gender (M/F ..), BloodType (A/O etc), Shift, AccessType in tblPatients you could scrap the other 4 tables.

    Furthermore, consider having a main form (tblPatients) and subform based on your top1 query or the aggregate version.
    Link the two using the two fields used in the first aggregate query
    You will then have an editable main form with just the latest records and it will run faster
    BTW as the subform is just used to filter your results, it could be hidden

    Finally, your tblPatients should only contain information that will never or only rarely change.
    E.g name, DOB, gender.
    Fields that may change or be repeated would be better in separate tables e.g. Dialysis start date?

    Hope that helps....
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Thanx again for the advice.
    My tbl_Patients has indeed info that never change, except form 3 fields, that change very rarely. Your other ideas about the TOP 1 version of the main form seem very useful, I will try to implement them.

    I think though that my initial question is officially answered, so maybe this thread can get this nice "solved" flag ?

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

Similar Threads

  1. Replies: 9
    Last Post: 01-31-2017, 05:13 PM
  2. Replies: 2
    Last Post: 01-27-2016, 08:38 AM
  3. Replies: 6
    Last Post: 06-09-2015, 09:51 AM
  4. Select into with group
    By Dennis Willis in forum Queries
    Replies: 1
    Last Post: 07-08-2014, 08:23 PM
  5. GROUP by yet SELECT fields not within GROUP BY
    By johnseito in forum Access
    Replies: 25
    Last Post: 11-03-2013, 10:20 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
  •  
Other Forums: Microsoft Office Forums