Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Location
    Madison, WI
    Posts
    10
    Provided Answers: 1

    Question Answered: MS Access 2010 retrieve earliest date from series of visits

    I have inherited an MS Access 2010 database, used for medical research.

    I have several tables that collect information about patient visits. I'd like to be able to retrieve only the earliest visit per patient that meets certain criterion. Date information is collected at the ClinicVisitTable, and the related criterion are usually at the ExamTable.

    ClinicVisitTable
    ClinicNotesID AutoNumber
    SubjectID Number
    LastN Text
    Date Date/Time


    ExamTable
    ExamID Number
    SubjectID Number
    Symptoms Text

    The ExamTable ExamID field matches with ClinicNotesID for every given date. SubjectID obviously matches as well.

    Due to confidentiality and research needs, some information is redundantly collected at each visit. As a result, new row is generated for each Subject at each ClinicVisit and each Exam. Often, I just need to find the first visit that meets certain criterion.

    Typically, I will retrieve the needed information, and sort by SubjectID and then Date Asc., and have staff delete the older rows by hand. However, I realize there must be a better way to do this. I've tried selecting MIN(Date), but that seems to then retrieve only the earliest date in the table, not the earliest date for my Subjects. I'd like to find the earliest ClinicVisit date for each subject.

    Over the past several years, I generally have worked on databases over the web and used PHP or Ruby to search/retrieve information, so this is the first SQL DB I've worked with for a while. My SQL-fu is somewhat weak. Please teach me.

  2. Best Answer
    Posted by healdem

    "use the min predicate
    select min(Date) as EarliestVisit from ClinicVisitTable"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the min predicate
    select min(Date) as EarliestVisit from ClinicVisitTable
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Jun 2009
    Location
    Madison, WI
    Posts
    10
    Provided Answers: 1

    using min predicate

    Quote Originally Posted by healdem View Post
    use the min predicate
    select min(Date) as EarliestVisit from ClinicVisitTable
    thanks, would this best be accomplished as a subquery, or as a new query once i have found the set of results from which to draw these results?

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    i don't know what you are trying to achieve
    but I'd be surprised if a subquery was needed
    you may need to use a group by, you may not. depends on what you require
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Jun 2009
    Location
    Madison, WI
    Posts
    10
    Provided Answers: 1
    It's working!

    The mistake that I had made earlier, was to select both the MIN(Date) and just Date with no predicate, together. Selecting Date with no predicate, returned all the other dates for the Subject.

    This is what worked:

    SELECT ClinicVisitTable.SubjectID, Min([ClinicVisitTable].Date) AS MinOfDate1, ExamTable.Symptoms
    FROM ExamTable INNER JOIN ClinicVisitTable ON ExamTable.SubjectID = ClinicVisitTable.SubjectID AND ExamTable.ExamID = ClinicVisitTable.ClinicNotesID
    GROUP BY ClinicVisitTable.SubjectjID, ExamTable.Symptoms
    HAVING (((ExamTable.Symptoms) Like 'My Symptoms'))
    ORDER BY ClinicVisitTable.SubjectID, Min([ClinicVisitTable].Date);

Posting Permissions

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