Hi John,
I have started new thread as our previous thread reached to a new query.
I am still floating on my previous threads original question and my boss is anxiously waiting for me to produce correct results on the same report.
John, you are a genius in this field. I hope and expect you would find a solution for it may be in some different way and logic. I am with my all old relationships and set up at present i cannot make modifications in that.
Current problem is the same i need Sum of "------------------" at Report Footer.
--------------------------------------------------------------------------------------------------------
I have a report with the following record source:
ClientsCargoBalance(SelectiveClient)Query
Its sql is:
SELECT ClientsCargoBalanceQuery2.ConsignmentNo, ClientsCargoBalanceQuery2.ClientCIN, ClientsCargoBalanceQuery2.ClientName, ClientsCargoBalanceQuery2.[Count Of CartonNo], ClientsCargoBalanceQuery2.ConsigneeName, ClientsCargoBalanceQuery2.ExportDocs, ProgressOfConsignment.DeliveryToClient, ProgressOfConsignment.DeliveredBy, ProgressOfConsignment.Receivedby, ProgressOfConsignment.NoOfCartons
FROM ClientsCargoBalanceQuery2 LEFT JOIN ProgressOfConsignment ON (ClientsCargoBalanceQuery2.ClientCIN=ProgressOfConsignment.ClientCIN) AND (ClientsCargoBalanceQuery2.ConsignmentNo=ProgressOfConsignment.ConsignmentNo)
WHERE (((ClientsCargoBalanceQuery2.ClientCIN) Like [Enter CIN]));
Sql for "ClientsCargoBalanceQuery2" is:
SELECT DISTINCTROW CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.ClientName, CollectionVoucher.ConsigneeName, [Consignment Number].ExportDocs, CollectionVoucher.Destination, Count(CollectionVoucher.CartonNo) AS [Count Of CartonNo]
FROM [Consignment Number] INNER JOIN CollectionVoucher ON [Consignment Number].ConsignmentNo = CollectionVoucher.ConsignmentNo
GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.ClientName, CollectionVoucher.ConsigneeName, [Consignment Number].ExportDocs, CollectionVoucher.Destination;
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 "Count Of CartonNo"
On the detail section there are four text boxes
DeliveryToClient, Receivedby, DeliveredBy, NoOfCartons
On ConsignmentNo Footer section, there are three text boxes:
Text58, Control source: =[Count Of CartonNo]
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([Count Of CartonNo])
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 "Count Of CartonNo" are multiplied by number of rows for the specific "ConsignmentNo" group
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