Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Question Unanswered: Please help with a flat file query question

    I all,

    I need some desperate help with a report I'm working on. Let me try to explain what I'm going after. Basically, I have a table patient appointments. The goal is to show the last appointment for each patient and not show anything prior.

    I am working with a flat file in access and this is what I have accomplish so far. I have sorted and group the table in such a way that the last appointment is always on the top line and all same patients are group together by their medical record numbers.

    My only question is how do I get rid of all the prior appointments (lines below for the same patient) for the same patient? On last note, it is possible that there is only one appointment for one patient. In that case just leave that line.

    Attach is a screenshot of my query thus far. Please help!!

    Thank you all in advance.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Presuming you have a query that returns them in the correct order, adding the TOP predicate will let you limit the number of records returned:

    SELECT TOP 1 ...

  3. #3
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 1
    Hi tau

    I am not sure if you want only one record or the last appointment for EACH patient.

    If the latter, then the easiest way (and easiest to explain) is to construct another query to return the latest date and MRN for each patient and then add this query to the existing query with an INNER JOIN on LastDate = [APP DATE]) and MRN = MRN.

    The new query would be something like


    Is that what you had in mind ??

    You can also use a similar query as a sub query with the IN predicate in the Criteria field for the [APP DATE] field in the existing query, but relating it the existing table etc. is a little more difficult to understand (and explain) if you are not to familiar with SQL.

    BTW I know you know it’s a flat file but it really shouldn’t be. If you want to change it to a normalised state then do it sooner rather than later.

    Hope this is of some use.


  4. #4
    Join Date
    Mar 2004


    Thank you all for your help. I was able to get them what they wanted.

    The file was from an outside source and the data was outputed in a txt file that i had to import into ACCESS. Otherwise, I wouldn't be caught dead using 1 table for all these fields.

    Again, thank you all for your help. I love this site.

Posting Permissions

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