Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Indiana, Indiana, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Minimum Value Query Problem (Access 97)

    I have two tables, one with patient data (patient database) and the other with notes written about the patient (note database). There can be more than one note record for each patient. Patient and note records are linked by a unique case number. Each record in the note database has a sequence number assigned to designate when the note was written and may or may not have information about use of a consultant.

    I want to display the sequence number (plus some other fields) of the note record that has the first consultant used. I never know in which note record the use of a consultant will be charted. It could be the 1st, the 3rd or the 6th. Different consultants can be in different note records on a given patient, but I am only interested in the first one used. I

    have designed a select query that gives me the case number and sequence number, but it returns all of the note records for each patient where any consultant was used. (There is a specific list of consultants available.) How do I limit the query to give me only the note records with the lowest sequence number (e.g., the first consultant used) for each patient?

    I plan on using this in an update query to fill in missing information in the patient database on what the first consultant use was for each patient.

    Thank you for any replies. Hope this makes sense!

    Jim <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minimum Value Query Problem (Access 97)

    Change your query to a "Totals" query (click the button that looks sort of like an "E", it's actually the greek letter known as "sigma"). Now all of your fields should show "Group By" in the total line. Change each field except the case number field, to "First". Sort the sequence number field "Ascending". Now put "Is Not Null" as criteria for the consultant(name?) field. This will now only show the first record (based on sequence number) for each patient that has a consultant. <img src=/S/chatter.gif border=0 alt=chatter width=38 height=16>

  3. #3
    New Lounger
    Join Date
    Apr 2001
    Location
    Indiana, Indiana, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minimum Value Query Problem (Access 97)

    Thanks for putting me on the right track. Actually, I got some really funky results using the "First" in the total field. If there was more than one note record with a consultant in it, it would return the sequence number for the last record, not the first. Changing the sort order or the Total to "Last" still gave wrong results. But only sometimes. Some records were correct, some were wrong. I can't figure that one out. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    When I changed the sequence number field to "Min" I got the right result.

    Thanks for your help. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Jim

Posting Permissions

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