locked
Merge several query in one query RRS feed

  • Question

  • In my program I have a table named "tblInsurancePolicy" that holds the information about an insurance policy and total amount of it.The insurance holder can pay the total amounts by check, internet (bank) transaction, cash or a mixture of them. For example we issue an insurance policy for 1,000 $, the customer pay it as follow:

    $ 250 as cash when the IP was issued

    $ 250 a week later as cash

    $ 100 two weeks later as check

    $ 200 three weeks later as check

    $ 200 four weeks later as bank transaction

    Now I want to sum all payments by Insurance holder and compare it with total insurance amount in a form. For summing the payments I used an aggregate query that its structure is as follow:

    it's SQL code is as follow:

    SELECT tblInsurancePolicy.InsurancePolicyID, Sum(tblInternetTransaction.AmountReceived) AS SumOfAmountReceived, Sum(tblCreditAccount.CreditAmount) AS SumOfCreditAmount, Sum(tblCashReceipt.Amount) AS SumOfAmount, Sum(tblCheckReceipt.TotalOfReceivedChecks) AS SumOfTotalOfReceivedChecks, Nz([SumOfAmountReceived],0)+Nz([SumOfCreditAmount],0)+Nz([SumOfAmount],0)+Nz([SumOfTotalOfReceivedChecks],0) AS TotalCredit
    FROM (((tblInsurancePolicy LEFT JOIN tblCashReceipt ON tblInsurancePolicy.InsurancePolicyID = tblCashReceipt.InsurancePolicyID) LEFT JOIN tblInternetTransaction ON tblInsurancePolicy.InsurancePolicyID = tblInternetTransaction.InsurancePolicyID) LEFT JOIN tblCheckReceipt ON tblInsurancePolicy.InsurancePolicyID = tblCheckReceipt.PolicyID) LEFT JOIN tblCreditAccount ON tblInsurancePolicy.InsurancePolicyID = tblCreditAccount.InsurancePolicyID
    GROUP BY tblInsurancePolicy.InsurancePolicyID;

    It works but sometimes it has problems and mistakes. When for example there is 2 checks and one Bank transaction, the query duplicate the bank transaction and the final amount is incorrect . I work on it a lot but I don't know how to solve the problem. I don't know using this type of queries is appropriate or there is a better solution!


    Karim Vaziri Regards,

    Friday, July 22, 2016 7:19 AM

Answers

  • The original problem is that your query contains multiple unrelated 1-to-many relationships. That's why you see inaccurate results. You could redesign it with your existing InsurancePolicies table and a single child table InsurancePolicyPayments. Each payment would be a single row in InsurancePolicyPayments. That table could have attributes like paymentAmount and paymentType, which could be a foreign key from a PaymentTypes table. Then the single join from InsurancePolicies to InsurancePolicyPayments would always be valid.

    If you wanted to have different details for the different payment types, you could either include all of those attributes in the InsurancePolicyPayments table, or you could use a category-subcategory approach with separate tables for each payment type. You would still have the InsurancePolicyPayments table, and it would have a 1:1 relationship to each of the specific payment type tables.


    Paul

    Friday, July 22, 2016 7:53 PM

All replies

  • It works but sometimes it has problems and mistakes. When for example there is 2 checks and one Bank transaction, the query duplicate the bank transaction and the final amount is incorrect . I work on it a lot but I don't know how to solve the problem. I don't know using this type of queries is appropriate or there is a better solution!

    Hi Karim,

    The way I handle these problems is to have an Insurance-account per Insurance. In this account you write the amount to be payed, and all further transactions regarding this Insurance. When the balance of this account = 0 all is handled in order, else there is still something to do: or ask the client for additional payment, or pay back some money, or - when the balance is too small to take action, book it as "additional payments".

    In the Insurance-account table you can make a reference to the different Bank-accounts, or to the Cash-account (or if you want an additional Check-account). When a Bank-account transactions refers to two different Insurances, then the two Insurance-account records refer to the same Bank-account records (or if possible, split the Bank-account in two records).

    In the way I use this principle, I have an additional field in the Insurance record, tyhat displays the balance of that Insurance. Whenever a new Insurance-account record is created, or a record is deleted, or the amount field in the record is changed, the balance is automatically updated. You then only have to check those Insurances where the balance <> 0.

    You can even go a step further by automatically make a link between the Bank-account records and the Insurance-account records, after downloading the Bank-account transactions, based on special features.

    Imb.

    Friday, July 22, 2016 8:15 AM
  • Greetings Kvaziri,

    While keeping a 'main table' with outstanding amount like imb-hb is a good idea from an architectural point;
    to answer your question;

    I would create a view over the different transaction-tables with a UNION ALL, as i want to sum all different transactions and not relate them to one another.

    for a simplistic version of this, please review the code below:

    Create table #bltInsurrancePolicy
    (
    insurrancePolicyId int,
    InsurrenacePolicyAmount dec(12,4)
    )
    
    Create table #tblCreditAmount
    (
    CreditAmount dec(12,4),
    InsurrancePolicyId int
    )
    Create table #tblCashReceipt
    (
    CreditAmount dec(12,4),
    InsurrancePolicyId int
    )
    Create table #tblInternetTransaction
    (
    CreditAmount dec(12,4),
    InsurrancePolicyId int
    )
    Create table #tblCheckReceipt
    (
    CreditAmount dec(12,4),
    InsurrancePolicyId int
    )
    
    insert into #tblCreditAmount values (1, 1)
    insert into #tblCashReceipt values (1, 1)
    insert into #tblCashReceipt values (1, 1)
    insert into #tblInternetTransaction values (1, 1)
    insert into #tblCheckReceipt values (1, 1)
    insert into #tblCreditAmount values (1, 1)
    
    select InsurrancePolicyId, sum(CreditAmount) as TotalPaid
    from ( 
    	select InsurrancePolicyId, CreditAmount from  #tblCreditAmount
    	UNION ALL
    	select InsurrancePolicyId, CreditAmount from #tblCashReceipt
    	UNION ALL
    	select InsurrancePolicyId, CreditAmount from #tblInternetTransaction
    	UNION ALL
    	select InsurrancePolicyId, CreditAmount from #tblCheckReceipt
    )  as T
    group by InsurrancePolicyId
    
    drop table #bltInsurrancePolicy
    drop table #tblCashReceipt
    drop table #tblCheckReceipt
    drop table #tblCreditAmount
    drop table #tblInternetTransaction

    Friday, July 22, 2016 8:52 AM
  • Dear Imb,

    Thanks for your quick reply. As I think you mean that I put a table between Insurance Policy table and other tables. But I think it doesn't solve the problem because for summing the received amount from different methods I have the problem again in querying them. Maybe I misunderstand!


    Karim Vaziri Regards,

    Friday, July 22, 2016 9:29 AM
  • Thanks for your quick reply. As I think you mean that I put a table between Insurance Policy table and other tables. But I think it doesn't solve the problem because for summing the received amount from different methods I have the problem again in querying them. Maybe I misunderstand!

    Hi Karim,

    Well, just summing the records of Insurance-account for the given Insurance gives you the amount still due for that Insurance. If you exclude the total amount for the Insurance, you have all the payments the customer did, toghether with all the refunds the company did.

    For tracing purposes only the balance of the Insurance-account is of interest, in my opinion. That is why I have that (redundant) field in the Insurance record and which is automatically updated.

    The relation between Insurance (Insurance Policy?) and Insurance-account is 1:m, you can have many payments regarding one Insurance (as your example showed).

    Imb.

    Friday, July 22, 2016 10:15 AM
  • Dear Sebastian,

    I think you mean that I create several tables by SQL code on the fly and query them by UNION ALL statement.

    Yes, it is very good , but I think you write your code based on SQL not Access SQL. Unfortunately I am not master in this type of query. Could you explain me how can I add my data to this tables in Access. The code is a little vague to me.


    Karim Vaziri Regards,

    Friday, July 22, 2016 3:46 PM
  • The original problem is that your query contains multiple unrelated 1-to-many relationships. That's why you see inaccurate results. You could redesign it with your existing InsurancePolicies table and a single child table InsurancePolicyPayments. Each payment would be a single row in InsurancePolicyPayments. That table could have attributes like paymentAmount and paymentType, which could be a foreign key from a PaymentTypes table. Then the single join from InsurancePolicies to InsurancePolicyPayments would always be valid.

    If you wanted to have different details for the different payment types, you could either include all of those attributes in the InsurancePolicyPayments table, or you could use a category-subcategory approach with separate tables for each payment type. You would still have the InsurancePolicyPayments table, and it would have a 1:1 relationship to each of the specific payment type tables.


    Paul

    Friday, July 22, 2016 7:53 PM
  • Greetings Kvaziri,

    No, I used #-tables to create a set that we can both see and interact with.
    However I did not pick up all the fields you have in your design.

    You have multiple un-related 1-to-many tables, so you cannot join them as per your query.
    As this will mean you multiplications when someone used a payment-type more then once.

    So you can union the amounts from the base table; which I demonstrated in the query and do the sum over those values.

    Though you can create a UNION of more columns like, incase you want to know which type of payment is the source, similar to:

    [...]
    select InsurrancePolicyId, CreditAmount, 0 as Cash, CreditAmount as TotalAmount from  #tblCreditAmount
    UNION ALL
    select InsurrancePolicyId, 0, CreditAmount, CreditAmount as TotalAmount from #tblCashReceipt
    [...]

    If we look at the example, this would enable you to get a result similar to:

    InsurrenacePolicyId   Credit Cash Internet Check Total
    1 2 2 1 1 6

    but again, if you have the luxery to re-design and input-processing isn't an issue I would go with Paul23's suggestion:

    Create a payment-table which contains the bare information, similar to:
    ID, InsurrancePolicyId, CreditAmount, PaymentType, ProcessedDateTime, PaymentID, DetailID

    then join the extended information in  when needed from a different table based on PaymentType and DetailID.
    Where you add PaymentType as failsafe to incase ID's start to overlap.

    with kind regards,

    Sebastian




    Friday, July 22, 2016 9:22 PM
  • Hi Karim,

    Yesterday I was a little bit in a hurry, because I had to leave for a day.

    Returning back, I would use a schema with the table Insurance_Policy, that contains all details about the Insurancws. This table relates (1:n) to a table Insurance_Account, that contains all payments regarding these Insurances. It also has a FK to the Payment_Type. From this table you can make a reference to, for instance, Internet_Transactions, for storing further details on the Internet_payments, or a Check table that stores the Checknumber, etc.

    Imb.

    Saturday, July 23, 2016 5:26 AM