cannot guess where is the problem, either in sql statement or crystal report

Hi Thomas
Thanks for your reply, yes i can try ur query in database. Another thing is i will surely add the ID field for both in the table. hope it will solve my problem. Else i will reply you back tomorrow...hope it will work.
Thanks for your reply again.

Have you tried running the SQL query not in the report but in your database? If you are getting extra rows returning that could explain the extra totals. In the report you suppress the duplicates but they are still added (I suspect that is the case).

To fix that rather than pull both tables and limit it in the where clause it would be better to write the query using inner joins. (Not sure what database program you are using so the SQL syntax below may vary slightly).


I don't know how much control you have over the tables and table structure but it sounds like you could have a table listing vendors, then an Orders table and finally a Payments table. In the Vendor table you could have a Vendor ID that is unique to each vendor. Then use that ID in the Orders table and in the Payments table.

Then your query could be written like this:
SELECT order.orderBillNo, order.siteName, order.orderDt, Vendor.vendorName, order.totalAmt, payments.InvoiceNo, payments.Date, payments.Paymode, payments.amt, payments.Chqddno, payments.bankname`
FROM  vendor
inner join order
on vendor.vendorid = order.vendorid
inner join payments
on vendor.vendorid = payments.vendorid
and Order.siteName = payments.siteName
where vendor.Name='A'
AND order.sitename='B'
AND (order.orderDt between "2012/02/01" and "2012/02/25")
AND (payments.date`between "2012/02/01" and "2012/02/25")

If you can not adjust your tables at all then you can write your query like this

 SELECT `order`.`orderBillNo`, `order`.`siteName`, `order`.`orderDt`, `order`.`vendorName`, `order`.`totalAmt`, `vendorpayments`.`InvoiceNo`, `vendorpayments`.`Date`, `vendorpayments`.`Paymode`, `vendorpayments`.`amt`, `vendorpayments`.`Chqddno`, `vendorpayments`.`bankname`
 FROM  `order`
inner join  `vendorpayments`
on `order`.`vendorName`= `vendorpayments`.`vendor`
AND `order`.`sitename` = `vendorpayments`.`sitename`
where `order`.`vendorName`='A'
AND `order`.`sitename`='B'
AND (`order`.`orderDt`>="2012/02/01" and `order`.`orderDt`<="2012/02/25")
AND (`vendorpayments`.`date`>="2012/02/01" and `vendorpayments`.`date`<="2012/02/25")


that SHOULD give you a single row of data for each order and payment. Run the SQL in the database to make sure that is the case. If it is not see if there is another field you did not include here that is different between each of the duplicate records. then you can adjust your where clause or Join to include that field.