Hi All,
I have a report with the following record source:
ClientsCargoBalance(SelectiveClient)Query
Its sql is:
SELECT ClientsCargoBalanceQuery1.ConsignmentNo, ClientsCargoBalanceQuery1.ClientCIN, ClientsCargoBalanceQuery1.ClientName, ClientsCargoBalanceQuery1.ConsigneeName, [Consignment Number].ExportDocs, ClientsCargoBalanceQuery1.DeliveryVr, ProgressOfConsignment.DeliveryToClient, ProgressOfConsignment.DeliveredBy, ProgressOfConsignment.Receivedby, ClientsCargoBalanceQuery1.PackagesCredited, ProgressOfConsignment.NoOfCartons
FROM (ClientsCargoBalanceQuery1 LEFT JOIN ProgressOfConsignment ON (ClientsCargoBalanceQuery1.ClientCIN = ProgressOfConsignment.ClientCIN) AND (ClientsCargoBalanceQuery1.ConsignmentNo = ProgressOfConsignment.ConsignmentNo)) INNER JOIN [Consignment Number] ON ClientsCargoBalanceQuery1.ConsignmentNo = [Consignment Number].ConsignmentNo
WHERE (((ClientsCargoBalanceQuery1.ClientCIN) Like [Enter CIN]))
ORDER BY ProgressOfConsignment.DeliveryToClient;
Sql for "ClientsCargoBalanceQuery1" is:
SELECT DISTINCT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.ClientName, CollectionVoucher.ConsigneeName, CollectionVoucher.DeliveryVr, Count(CollectionVoucher.CartonNo) AS PackagesCredited
FROM CollectionVoucher
GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.ClientName, CollectionVoucher.ConsigneeName, CollectionVoucher.DeliveryVr;
Reports Sorting and Grouping is as follows:
ExportDocs - Ascending, Group Header=No, Group Footer=No
ConsignmentNo - Descending, Group Header=Yes, Group Footer=Yes
DeliveryToClient - Ascending, Group Header=No, Group Footer=No
On reports ConsignmentNo Header section, i have Text box "ConsignmentNo" Control source ConsignmentNo, "PackagesCredited" with control Source "PackagesCredited"
On the detail section there are four text boxes
On ConsignmentNo Footer section, there are three text boxes:
Text58, Control source: =[PackagesCredited]
Text59, Control source: =Sum([NoOfCartons])
Text60, Control source: =[Text58]-[Text59]
Upto here things are OK
On the report Footer section, there are three text boxes:
Text69, Control source: =Sum([PackagesCredited])
Text70, Control source: =Sum([NoOfCartons])
Text71 Control source: =[Text69]-[Text70]
My problem is occurring on the report footer section on Text69 output, reason is that if on detail section there is only one row then Text69 output is OK, but if rows on detail section are more than one then "PackagesCredited" are multiplied by number of rows for the specific "ConsignmentNo" group
I don't know if i have explained my problem in a meaningful and descriptive manner. Need help please.
Khalid
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar