Please help with this query.

[lucia]

I am using Sql server 2000.

I have a table called s. I need left join t with this s. and left join m with t.
Some fields in table t:Tran_type, Commodity, Qty ,Amount...
Some fields in table m:Code...

What I need is: When t.Tran_type ="p" or "D", if t.Commodity ="C" then sum the Qty and Amount. If m.Code ="BA"or "BO", then sum the Qty and Amount.

I have a query like this:

  strSQL = strSQL & "SELECT s.Route_Num , s.StopDate, s.START_DATETIME, s.END_DATETIME, "
  strSQL = strSQL & " t.Tran_Type, t.Commodity, m.Code, "
  strSQL = strSQL & "SUM(t.Qty)as TotQty, SUM(t.Amount) as TotAmount "
  strSQL = strSQL & "FROM  s "
  strSQL = strSQL & "LEFT JOIN t ON (s.Stop_Num = t.stop_num "
  strSQL = strSQL & "AND s.StopDate = t.TranDate "
  strSQL = strSQL & "AND s.Route_Num = t.Route_Num) "
  strSQL = strSQL & "LEFT JOIN m ON t.Commodity = m.CommId "
  strSQL = strSQL & "WHERE t.ItemId <>'NOPU' "
  strSQL = strSQL & "AND s.START_DATETIME >='" & DTPFrom.value & "' "
  strSQL = strSQL & "AND s.END_DATETIME <='" & DTPTo.value & "' "
  strSQL = strSQL & "AND (t.Tran_Type='D' or t.Tran_Type='P') "
  strSQL = strSQL & "AND (m.CODE = 'BO' or m.CODE = 'BA') "
  strSQL = strSQL & "AND t.Commodity ='C' "
  strSQL = strSQL & "GROUP BY  "
  strSQL = strSQL & "s.Route_Num , s.StopDate, s.START_DATETIME, s.END_DATETIME, "
  strSQL = strSQL & " t.Tran_Type, t.Commodity, m.Code "
  strSQL = strSQL & "ORDER BY  s.StopDate, s.Start_DateTime"

It returns 0 record. I know there is a problem around following part. But I don't know how to correct it according to the needs I mentioned above.

strSQL = strSQL & "AND (t.Tran_Type='D' or t.Tran_Type='P') "
strSQL = strSQL & "AND (m.CODE = 'BO' or m.CODE = 'BA') "
strSQL = strSQL & "AND t.Commodity ='C' "

Thanks a lot.

Since there is no one in the database forum, I have to post it here.