none
The query doesn't show all desired records when I filter it RRS feed

  • Question

  • I have a query that is combined of three tables. It show all the insurance policies that has been issued and should be follow up for extending. When the operator follow up an insurance policy and can extend it , it tick a field named "completed" in "tblExtendingPolicyForm". Now I want when the operator runs the query again it doesn't show the fields that are ticked as completed, but when I add a criteria to query that show only uncompleted fields (False for completed field) , it show only some records that are in "tblExtendingPolicyForm" and don't show all the records in "tblInsurancePolicy" that are not completed.

    The SQL script is as follow:

    SELECT tblInsurancePolicy.InsurancePolicyID, tblInsurancePolicy.InsuranceType, tblInsurancePolicy.InsurancePolicyNumber, tblAccount.FinalName, tblInsurancePolicy.InsurancePolicyIssueDate, tblInsurancePolicy.StartDate, tblInsurancePolicy.EndDate, tblInsurancePolicy.CarModel, tblExtendingPolicyForm.RemindDate, tblExtendingPolicyForm.Priority, tblExtendingPolicyForm.Completed, tblInsurancePolicy.Lpp1, tblInsurancePolicy.Lpp2, tblInsurancePolicy.Lpp3, tblInsurancePolicy.Lpp4, tblInsurancePolicy.MotorCycle
    FROM (tblAccount INNER JOIN tblInsurancePolicy ON tblAccount.AccountID = tblInsurancePolicy.AccountID) LEFT JOIN tblExtendingPolicyForm ON tblInsurancePolicy.InsurancePolicyID = tblExtendingPolicyForm.InsurancePolicyID
    WHERE (((tblExtendingPolicyForm.Completed)=False))
    ORDER BY tblInsurancePolicy.EndDate;
    

    I don't know where is the problem.


    Karim Vaziri Regards,

    Monday, June 11, 2018 6:32 PM

All replies

  • Hello kvaziri,

    I tried to use some simple data to do test for your issue. In my test, the code should be able to return all uncompleted record. For avoiding any misunderstanding, I would suggest you share some data to explain what's your expected result and what's the current result you got.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 12, 2018 8:20 AM
  • Hi, 

    The issue could be with JOINS. I can see you are using INNER JOIN between tblAccounttblInsurancePolicy and applying LEFT join with tblExtendingPolicyForm. This will exclude records if its not available in tblAccount.

    If you need see all records from tblInsurancePolicy, you can try this.

    1. Apply LEFT join between tblInsurancePolicy(should be left side table) & tblAccount

    2. Apply another LEFT join with tblExtendingPolicyForm.

    3. Apply Filter on Completed field.


    Thanks and Regards, Bharath S.

    Tuesday, June 12, 2018 10:44 PM