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 
