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

Khalid-

You did exactly what I told you NOT to do. Adding a meaningless ID field to create a Primary Key is worthless. For a Primary Key to have value, it must not only uniquely identify each row but it must also be the link to Foreign Keys in related tables. For example, ConsignmentNo perhaps could be the PKey in the Consignment Number table (if each row has a unique value). It would then make sense for ConsignmentNo to be a Foreign Key in the Collection Voucher, Invoice Values Aventis, and Invoice Values Pak Ca… tables. This would also mean that for every ConsignmentNo, there can be multiple related CollectionVoucher, Invoice Values Aventis, and Invoice Values Pak Ca… records.

You need to lay out on paper all the Subjects in your database. Products is a subject. Consignments is a subject. Clients is a subject. Perhaps Vouchers and Invoices are subjects. Then decide how each subject is related to other subjects. Can a Client have multiple Consignments? Can a Consignment have multiple Products?

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 12:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Total on Report Footer giving wrong total

John,

Yes you've said this to me before also and i did not took attention to this important and vital fact. Now i have tried to define Primary Keys for my tables.

I have uploaded "PCTL-Relationships New" file to 2_AssistanceNeeded in the group.

John I did'nt changed the relations manually. I would request you to once guide me the correct methodology for it. And get the solution for my report.

Also if Design of tables needs to be modified kindly suggest.

Thanks in advance for your favour and help.

Regards, Khalid

On Thursday, December 5, 2013 3:19 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

I'm sorry, but that's a mess. I see no Primary Key defined for CollectionVoucher, Clients, Invoice Values Aventis, or Invoice Values Pak Ca… It's impossible to see how these tables are actually related to each other. Simply adding something like an AutoNumber Primary Key won't help. What combination of fields in CollectionVoucher makes each row unique? Ditto for the other tables missing keys. You need to fix your design first - I think I've said this to you before.

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 10:35 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Total on Report Footer giving wrong total

John,

I have uploaded file "PCTL-Relaitonships" in the Assistance needed folder.

In the report i am putting:

Report Header: CIN, ClientName, ConsigneeName

Page Headers: Labels

ConsignmentNo Header: ConsignmentNo, ExportDocs (Date field), DeliveryVr, PackagesCredited

Detail section: DeliveryToClient (Date field), DeliveredBy, ReceivedBy, NoOfCartons

ConsignmentNo Footer:

Text58 Control source: =[PackagesCredited]

Text59 Control source: =Sum([NoOfCartons])

Text60 Control source: =[Text58]-[Text59]

Report Footer:

Text69 Control source: =Sum([PackagesCredited])

Text70 Control source: =Sum([NoOfCartons])

Text71 Control source: =[Text69]-[Text70]

That's it.

Khalid

On Thursday, December 5, 2013 2:02 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

The total is not "wrong" - it's doing exactly what you asked it to do. I suppose seeing the relationships might help me help you. I would also need to understand more about what you're trying to put in the report.

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 9:05 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Total on Report Footer giving wrong total

John,

Should i send you snapshot of relationships and report where i see wrong total ?

Regards,

Khalid

On Thursday, December 5, 2013 12:56 PM, John Viescas <JohnV@msn.com> wrote:

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