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

John,

Thanks God, i stepped on the first step of the stairs of Relationship.

You wrote: and you need to make these changes in the backend database where the data resides.

Me: Got it. And should i remove all relationships previously made in Front end ? and start one by one on Back end.

You wrote: Why is there a ClientName in Clients and in CollectionVoucher?

Me: This means that in CollectionVoucher i should only have ClientCIN field and remove ClientName, make Relationship on ClientCIN from Clients to CollectionVoucher.

And in the following sql:

SELECT DISTINCTROW CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.ClientName, CollectionVoucher.ConsigneeName, [Consignment Number].ExportDocs, CollectionVoucher.Destination, Count(CollectionVoucher.CartonNo) AS [Count Of CartonNo], Sum(CollectionVoucher.WeightOfCarton) AS [Sum Of WeightOfCarton], Sum(CollectionVoucher.Amount) AS [Sum Of Amount]

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

HAVING (((CollectionVoucher.ConsignmentNo) Like [Enter Consignment No]) AND ((CollectionVoucher.ClientCIN) Is Not Null) AND ((CollectionVoucher.ClientName) Is Not Null) AND ((CollectionVoucher.ConsigneeName) Is Not Null) AND (([Consignment Number].ExportDocs) Is Not Null) AND ((CollectionVoucher.Destination) Is Not Null) AND ((Count(CollectionVoucher.CartonNo)) Is Not Null) AND ((Sum(CollectionVoucher.WeightOfCarton)) Is Not Null) AND ((Sum(CollectionVoucher.Amount)) Is Not Null));

I should include table Clients, and under ClientName change its table to Clients.

Also in the same pattern/technique deal with other tables?

Does making the above changes would make any loss of data ? OR it would automatically update ? By the way i have made a backup up to today before sending this mail.

Once again lot of thanks.

Regards,

Khalid

On Friday, December 6, 2013 2:01 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

Yes, those changes might help, and you need to make these changes in the backend database where the data resides. But simply assigning Primary and Foreign keys isn't going to solve your problem entirely. You have duplicate fields all over your design. Why is there a ClientName in Clients and in CollectionVoucher? Why is there a ConsigneeName in Consignee and CollectionVoucher? A field that isn't a linking key should appear in one and only one table. If you need to change a field value, you should have to do it in one and only one table. When you need the name associated with a CollectionVoucher, you get it by linking CollectionVoucher in a query via a Foreign Key to the table that contains that value.

When you have corrected the problems with Primary and Foreign keys, you can find out whether the links are good by changing the Relationship line to include Referential Integrity. Attempting to do that will fail if, for example, there are ConsigneeID values in CollectionVoucher that have to match in Consignee. You may have a lot of cleaning up of your data to do.

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: Friday, December 06, 2013 8:37 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Total on Report Footer giving wrong total

John,

Thanks for identifying my wrong approach for making PKeys and giving an idea for doing it in a meaningful manner. I did studied your reply and read about PKeys and Foreign Keys on net too, and my first preference is to resolve this matter then i would be able to get correct reports and build queries correctly.

Now what i assumed and understood (if i'm right) that PKey in "Consignment Number" table should be "ConsignmentNo" and Foreign key in "CollectionVoucher" should be "ConsignmentNo" & its not PKey.

In Relationships i can see Field "ConsignmentNo" bold in Consignment Number table & "ConsignmentNo" field NOT BOLD in CollectionVoucher table.

If up to here i am on the right track, please give me a go ahead signal to adjust other tables. I have deleted the fields yesterday i created as PKeys and sent you the Relationships view.

Also there is a simple silly question where should i make these relationships:

On PCTL ?

OR PCTL_be

Certainly not on MDE database where i have unchecked "Show Database Window"

Waiting anxiously for your response, so that this matter be resolved sooner and then i come to my report and other issues to resolve as my boss is waiting for me to correct that report from where this thread started.

Regards,

Khalid

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

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