Alpha Video Training
Results 1 to 7 of 7

Thread: Query for most recent records

  1. #1
    "Certified" Alphaholic
    Real Name
    Thomas Patten
    Join Date
    Apr 2000
    Location
    Duluth, MN
    Posts
    1,494

    Default Query for most recent records

    I have a table with multiple records for each Client based on date. I want to query the table to only show the most recent record based on Date for each Client. One record per client.

    Order: Clientid+dtoc(AssDate)
    Filter: Name = "Medication Assistance"

    The above gives me all the records for "Medication Assistance" but I only want the most recent record for each client based on AssDate.

    Suggestions Please

    Thanks

    ~ Tom

  2. #2
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,393

    Default Re: Query for most recent records

    Hi Tom,

    This is one of those times where knowing if you're using dbfs or sql (and which sql) really makes a difference.

  3. #3
    "Certified" Alphaholic
    Real Name
    Thomas Patten
    Join Date
    Apr 2000
    Location
    Duluth, MN
    Posts
    1,494

    Default Re: Query for most recent records

    Sorry. DBFs

  4. #4
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Query for most recent records

    I have a similar table and used this to mark the duplicate records first:

    filter: req_cat = "22" - in my table req_cat 22 is 'Medication'

    order: memb_id+invert(cdate(req_date)

    Then ran a saved query that excludes the marked records

    filter: req_cat = "22" .and. (.not. marked())

    order: memb_id+invert(cdate(req_date)

    This might work best run from a button on form that displays the resulting records in an embedded browse.
    Last edited by MoGrace; 08-16-2019 at 02:05 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  5. #5
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,369

    Default Re: Query for most recent records

    Have you tried the Query Options and set the Number of Records to 1?
    That way only the most recent should show up if I understand you.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  6. #6
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,393

    Default Re: Query for most recent records

    Sorry about the delay.
    I know, I'm asking horrible questions now.
    Are you looking to get a list of records with each clientID appearing only once,
    or are you looking to a record for one clientID with the max AssDate ?

  7. #7
    "Certified" Alphaholic
    Real Name
    Thomas Patten
    Join Date
    Apr 2000
    Location
    Duluth, MN
    Posts
    1,494

    Default Re: Query for most recent records

    I got it. I setup a Summary Table operation.

    Robin - interesting approach. I'll try it for further reference.

    Ted - I didn't see the one record option, but I'll look again.

    Thanks everyone for taking a look.

    ~ Tom

Similar Threads

  1. Only display most recent records by year
    By Rich Hartnett in forum Application Server Version 10 - Web/Browser Applications
    Replies: 9
    Last Post: 12-05-2010, 07:11 PM
  2. Query multible records the query against sql
    By steve745 in forum Alpha Five Version 9 - Desktop Applications
    Replies: 1
    Last Post: 05-10-2010, 07:42 AM
  3. Use variables in Xbasic for deleting duplicate records on query.order, query.filter,
    By leufj in forum Alpha Five Version 9 - Desktop Applications
    Replies: 2
    Last Post: 11-23-2009, 10:36 AM
  4. Browse does not clear records after query and 0 records found
    By georgefurnel in forum Alpha Five Version 8
    Replies: 3
    Last Post: 03-23-2008, 06:20 PM
  5. Exporting Most Recent Records
    By Ed in forum Alpha Five Version 5
    Replies: 3
    Last Post: 05-23-2005, 12:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •