Somthing wrong with 3 Tables SQL Statment

• • Reply

  15 years ago

  by Holmes.vk

  Viktor

  Kiev, Ukraine, Ukraine

  Joined 15 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)
  
  TblBargain
     IDBargain
     BargainInformation
     CompanyName
  
  2.    (The table registering payments on the bargain)
  
  TblPayments
     IDPayment
     PaymentSum
     IDBargain
  
  3.    (The table registering dispatches on the bargain)
  
  TblDispatches
     IDDispatch
     DispatchSum
     IDBargain
  
  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.
  
  Report abuse
• • Reply

  15 years ago

  by MJaved

  Muhammad Javed

  , Pakistan

  Joined 15 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
  
  Regards
  Report abuse
• • Reply

  15 years ago

  by Holmes.vk

  Viktor

  Kiev, Ukraine, Ukraine

  Joined 15 years ago

  Hello MJaved,
  

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

  
  Regards
  

  
  Holmes, Moscow.

  Report abuse
• • Reply

  15 years ago

  by Holmes.vk

  Viktor

  Kiev, Ukraine, Ukraine

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

  Report abuse