ACCESS FEED: RE: [MS_AccessPros] Total on Report Footer giving wrong total

Rabu, 04 Desember 2013

RE: [MS_AccessPros] Total on Report Footer giving wrong total

Khalid-

Your problem is your FROM clause:

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

If there are multiple rows returned be either ProgressOfConsignment for the same ClientCIN or multiple rows returned by Consignment Number for the same ConsignmentNo, the total calculated by your ClientsCargoBalance will appear multiple times for each ClientCIN or ConsignmentNo. You should be able to see that by looking at the Datasheet view of the Record Source.

I don't know enough about the relationships between your tables to suggest how to fix it.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Thursday, December 05, 2013 8:47 AM
To: ms_access_professionals@yahoogroups.com
Subject: [MS_AccessPros] Total on Report Footer giving wrong total

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

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 (2)

Recent Activity: