I Need A Query Method

[keyem]

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....

[justsomeguy]

I'm not quite clear what your question is. What code do you have so far?

[keyem]

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?

[justsomeguy]

Are you building everything in Access, or are you using a scripting language like ASP to communicate with the database?

[keyem]

Sorry I forgot to say. I am using classic ASP to communicate with the database

[justsomeguy]

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.

[keyem]

Thanks but I think these query returns Syntax error (missing operator)

[justsomeguy]

I'm not seeing a missing operator, unless Access has problems with a plus sign in a string.