Jump to content

I Need A Query Method


keyem
 Share

Recommended Posts

We have two combobox whose names are Allergy and diagonostic.In this contextState1:the selected Allergy test results from the Allergy combobox will be ('+ + +' Or + + + +)State2: the selected Diagonostic results from the Diagonostic combobox will be 1Desired Result:The list of the patients who have both state Now our database tables (by the way we use Access 2003 as database.)Table 1: Allergy_Tests Fields:Allergy_test_id Allergy_test_name1 Mite 2 Cat3 Dog. .. .. .Table 2: Diagonostics Fields:Diagonostic_id Diagonostic_name1 asthma2 high blood presure 3 urticaria. .. .. .Table 3: Diagonostic_Results fieldsResult_id D_result Diagonostic_id Patient_id 1 1 1 12 1 2 1 3 2 3 14 1 1 25 1 2 26 2 3 2....Table 4: Allergy_test_resultsresult_id A_result Allergy_test_id Patient_id1 +++ 1 12 ++++ 2 1 3 - 3 14 ++ 1 25 +++ 2 26 +++ 3 2Table 5: PatientsPatient_id patient_name patient_surname123....

Link to comment
Share on other sites

I need to make a query which is belowAssume that we have 5 patientsAllergy Results2 patients olive allergy's result is (+++) and mite allergy result is(++++)1 patient dog allergy's result is(+++)2 patients cat allergy's result is (+++) and mite allergy result is(++++)diagonostic Results2 patients' high blood pressure diagonostic result is true and urticaria diagonostic result is true2 patients' urticaria diagonostic result is true1 patients' asthma diagonostic result is trueA query sampleHow many patients' are cat allergy's result and dog allergy's result (+++)or(++++) and asthma result true? Its answer is zeroAnother oneHow many patients' are asthma result true and mite allergy's result (+++)or(++++)? One patient... if the patient who is asthma has mite allergy otherwise the answer is zero.Now I have 2 multiple comboboxesAllergy Tests DiagonosticsTest 1 Dia 1Test 2 Dia 2Test 3 Dia 3. .. .. .Such as I will choose (Test1, Test2) and (Dia1,Dia2,Dia3) The query I need will bring this result below.The patients Alergy Test Result Test1= (+++) or (++++) and Test2=(+++) or (++++) and Dia1=true and Dia2=true and Dia3=trueIs it enough clear?

Link to comment
Share on other sites

You'll need to build the query in ASP then, and add each field and value you're looking for based on what they picked. Your query would probably look something like this:

SELECT Patients.* FROM Patients AS p INNER JOIN Diagnostic_results AS d ON p.Patient_id = d.Patient_id AND d.D_result = 2 AND (d.Diagnostic_id = 111 OR d.Diagnostic_id = 222 OR d.Diagnostic_id =333) INNER JOIN Allergy_test_results AS a ON a.Patient_id = p.Patient_id AND (a.A_result = '+++' OR a.A_result = '++++') AND (a.Alergy_test_id = 111 OR a.Alergy_test_id = 222 OR a.Alergy_test_id = 333)

So you would need to loop through the IDs they chose and add each ID to the appropriate section in the query. That query assumes that a value of 2 for the diagnostic result means true. Note that will return one record per patient per test result, so you would need to loop through the records and make sure that each person has all of the test results there. It will return records for patients who have any combination of the things you're looking for, so you would need to additionally verify that the patient has all of the records.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...