Somthing wrong with 3 Tables SQL Statment

databases Ukraine
  • 11 years ago
    hello everyone
    So cold now in Russia, about    -20C with strong icing wind. The best now to stay at home and do something useful. …so it is what am struggling with -

    Advise someone, please to solve the problem with SQL.
    Below the simplified tables from database, this part of base is to store information on bargains

    Three tables:
    1.    (The main table labeling bargains)


    2.    (The table registering payments on the bargain)


    3.    (The table registering dispatches on the bargain)


    And SQL statement to have for each bargain TotalDispatches, TotalPayments and Balance in between.

    Ado.RecordSource =
    "SELECT TblBargain.CompanyName, TblBargain.IDBargain, " & _
    “TblBargain. BargainInformation, " & _
    “SUM(TblDispatches.DispatchSum) as TotalDispatchSum, " & _
    "SUM(TblPayments.PaymentSum) as TotalPaymentSum, (TotalDispatchSum - TotalPaymentSum) as Balance " & _
                       "FROM (TblBargain LEFT JOIN TblDispatches ON TblBargain.IDBargain =                  TblDispatches. IDBargain) " & _
                       "LEFT JOIN TblPayments ON TblBargain.IDBargain = TblPayments. IDBargain " & _
                       "WHERE [TblBargain.CompanyName] LIKE '" & strCompanyName & "'" & _
     "GROUP BY TblBargain.CompanyName, TblBargain.IDBargain, TblBargain. BargainInformation "

    The PROBLE-E-EM! I-I-IS that this statement works correct in the cases when tables Dispatch- and Payment- have only one rec. in each table or only one of the tables above have records (could be more then one). Otherwise it sums the records in first indicated table (TblDispatches) and duplicates the sum with each record in the second table (TblPayments). Thus instead of one resulting record for one chosen bargin in the interface table I have several ones.
  • 11 years ago
    Try to use this query it will give u results as expected, may be some syntax error will be in it as i have not tested just made it....

    SELECT B.CompanyName, B.IDBargain, B. BargainInformation,
    D.TD as TotalDispatchSum,
    P.TP as TotalPaymentSum,
    (D.TD - P.TP) as Balance

    FROM TblBargain B

       LEFT JOIN (SELECT IDBargain , ISNULL(SUM(PaymentSum),0) AS TP FROM TblPayments
       GROUP BY IDBargain) AS P
       ON B.IDBargain = P.IDBargain

       LEFT JOIN (SELECT IDBargain , ISNULL(SUM(DispatchSum),0) AS TD FROM TblDispatches
       GROUP BY IDBargain) AS D
       ON B.IDBargain = D.IDBargain

  • 11 years ago

    Hello MJaved,

    thank u for assistance indeed. I will try it up.


    Holmes, Moscow.

  • 11 years ago

    Hello MJaved and everyone,

    The blocks "LEFT JOIN ..." separately works perfect but up to now i have not managed to find some missing operand to make these two blocks work together. Someone help?...

    Thanks in advance.

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“A computer lets you make more mistakes faster than any other invention in human history, with the possible exceptions of handguns and tequila” - Mitch Ratcliffe