Grouping results of a query, ordering them, then showing only the top result

[matt_terje]

I have a database designed to diagnose a disease based on selected symptoms. I have had trouble making the diagnosis because my Symptoms fields have multiple values. The solution I have been able to almost figure out involves a query that generates all matches and I just need to group the matches, sort them from most common to least common, then I want to just return the top result for my query.
I have attached the database and you will be able to see, Query 1 and Query 2 don't work correctly. Query 3 returns multiple rows per patient and I just need to see the Common Name for the disease(s) that appears most. Eg. 1 row with First Name, Last Name, Diseases [Common Name].
Any help or advice is much appreciated - this is a project I am hoping use run through with my students.
Cheers,
MattDiagnosis Database Task1.zip

[Bob Fitz]

Hi Matt. Welcome to the forum.


IMHO you have a number of problems with your tables:
Avoid the use of spaces or special keys (e.g. punctuation marks etc) in the names of fields.
Use meaningful names rather than just "ID" for the name of your PrimaryKeys (e.g. "PatientID")
Do not use "Lookups" in tables. Use "Lookups" in forms.
Do not use multi-valued fields as you have in the "Symptom" field in your "Diseases" table. Create a one to many relationship with the "Symptoms" table instead.
Review your relationships between tables.
Review the fields that are duplicated (e.g. "FirstName", "Last Name"). Create proper relationships instead.

[Bob Fitz]

In the attached DB I have removed the relationships that you set.
I have made some changes to your table “Patients” which is now called “tblPatients”.
I have created two new tables (“tblPhoneNums” and “PhoneTypes”) and created the relationships between these tables and “tblPatients”.
I have also created a form called “frmPatients” which is bound to “tblPatients”, which has a subform for the telephone details of each patient. Each patient can now have many phone numbers (work, home, mobile, Next of Kin etc) as shown by the subform “frmPhoneNums” on the “frmPatients” form. You might use this method to handle "Symptoms". The subform “frmPhoneNums” also makes use of lookups on the form rather than at the table level.

[madpiet]

I have had trouble making the diagnosis because my Symptoms fields have multiple values. The solution I have been able to almost figure out involves a query that generates all matches and I just need to group the matches, sort them from most common to least common, then I want to just return the top result for my query.

You may be missing some tables.
There's one for "Diagnosis Symptoms" (the symptoms a Patient has) and then another for "Malady Symptoms" that relate to a specific diagnosis. Then you can join those two and then with the "Malady Symptoms" would also have a column pointing the the likely disease (for want of a better term). Then you have Disease INNER JOIN MaladySymptoms and Group by Disease, return a count, and finally ORDER that result in descending order and finally add the TOP 1 clause.

[matt_terje]

Thank you for this response. My apologies, but the first part of your response is beyond me and I will need to go and investigate this further, but the last sentence reflects most what I think I need to do. The image of my query result below, shows the result of appointment 3, where a long list of matches is presented and what I want to do is; do a count, order the result and only show the top result. In this case, the Test disease would show, as it appears 3 times. Is it possible to add this function to my current query, or do I need to make some changes to the tables in order to do this?
Cheers,
Matt

[madpiet]

Maybe we should back up a step or two. The first thing we have to do to fix this is never ever ever use multi-valued fields. They're way more trouble than they're worth. Sure, they save you a step or two, but the cost is pretty high. Okay, on to basic design.

You basically have an Appointment (AppointmentID (Identity, Primary Key), PatientID (foreign key to Patient), Date, PhysicianID (foreign key to Physician)) that stores information about a Patient's appointment with his Physician. So you have several tables now:

Physician(PhysicianID, Physician name etc)
Appointment(AppointmentID, PatientID, PhysicianID, AppointmentDate...)
AppointmentSymptom(AppointmentID, SymptomID, Severity)
SymptomList(SymptomID, SymptomName)
Patient(PatientID, Patient name, etc)

Bold = Primary key (uniquely identifies the records in the table... so each value is unique).
Underline = Foreign key (joins from child table ("many side" of noun-verb-noun) to the Primary key of another table.)

The "weird" one here is "AppointmentSymptom" (basically the Appointments are discrete events... one Patient may visit several times, and display a different set of symptoms). it's a junction table between a many-to-many relationship (Appointment and Symptom), because in each Appointment a Patient can display many symptoms AND the same patient can display the same symptoms at many appointments. (Just because Fred has flu at his first appointment doesn't mean he can't have it again later).

So if you were going to create a form for this, you'd base it on Appointment. Then you could select a Patient and a Physician. At the time of the appointment, the Patient can display multiple symptoms. (So that's in the subform).

Now that we've gotten all that set up, an Appointment (which relates to only one patient) can display a group of symptoms - then we can join the symptoms to DiseaseSymptoms table to get to the name of the disease. Then we can do the count you were talking about. But since we only want to keep the one with the highest number of matches so we sort that result in descending order (so the diagnoses with the most matches is at the top) and then do a TOP 1 so we only return a single value from that.

I'm going to have to finish this tomorrow, I think... just not awake enough to answer it clearly enough right now. But basically, once you have the information in the right tables, Oh wait... there's a Diagnosis table (DiagnosisID, Diagnosis) that's missing and then the related DiagnosisSymptom table (DiagnosisID, SymptomID).

You join the AppointmentSymptom to DiagnosisSymptom and then to Diagnosis. Then you do a count on DiagnosisSymptom, and get the one with the highest count. (I think... I reserve the right to change my mind when I'm no longer asleep).

But basically, you'd write a query so that you return Diagnosis and a count of DiagnosisSymptoms for each, and then choose the one with the highest number of matches. (So you'd create another query on top of the previous one, and sort by the count in descending order. And then you'd just add a TOP predicate to it and return only the TOP 1, and that would be your answer.

I'll test it out in SQL Server tomorrow. If the syntax is different, I'll patch it up so it'll run in Access. =)

You've asked a much more complex question than I think you realize - that's why it's so hard or the answer isn't obvious. But once you have all the tables you should, the question becomes a whole lot easier.

More tomorrow,
Pieter