ACCESS FEED: Re: [MS_AccessPros] Totals on Report Footer

John,

Our first query is "ClientsCargoBalanceQuery2"
Second query is "ClientsCargoBalance(SelectiveClient)Query", this is reports Record Source.

Its better i put down SQL of these both queries for you.

"ClientsCargoBalanceQuery2"
SELECT DISTINCTROW CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.ClientName, CollectionVoucher.ConsigneeName, [Consignment Number].ExportDocs, CollectionVoucher.Destination,   Count(CollectionVoucher.CartonNo) AS [Count Of CartonNo], Right(CollectionVoucher.ConsignmentNo,2) AS SortOrder, Max(CollectionVoucher.DeliveryVr) AS MaxOfDeliveryVr
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,   Right(CollectionVoucher.ConsignmentNo,2)
ORDER BY Right(CollectionVoucher.ConsignmentNo,2);
----------------------------------------------------------------------

In this Report we are taking Count Of CartonNo: CartonNo from table "CollectionVoucher" in the query "ClientsCargoBalanceQuery2" and displaying it on the reports SortOrder Header below Label "Packages Received", next to it under Label "Packages Delivered" i have put an unbound text box, next under Label "Packages Balance" is a text box "Text68" with Control Source: =[Count Of CartonNo] just to show Balance. (All Labels are on Page Header)

Now we come to Reports Detail Section:
We are taking  Field "NoOfCartons" from table "ProgressOfConsignment" in the query "ClientsCargoBalance(SelectiveClient)Query" 





  On Tuesday, December 17, 2013 11:24 PM, John Viescas <JohnV@msn.com> wrote:
  
 Khalid-
  
Note that for the previous solution, I used DSum within the query.  For this report, we're using DSum and DCount directly on the report.
  
How do you propose to calculate "Balance of Cartons"?  What fields and which tables?
  
    
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: Tuesday, December 17, 2013 4:18 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals on Report Footer
  



John,
  
Extremely wonderful, i'm enjoying learning all these teqniques and methods just because of your keen cooperation, patientce and guidence.
  
In report Sorting and Grouping i put "SortOrder" , Ascending, Group Header & Group Footer set to Yes and removed ConsignmentNo from Sorting and Grouping.
On the report SortOrder Header put Textbox "SortOrder" with its Visible property set to "No".
  
Also put textbox "MaxOfDeliveryVr". All is OK now up to here.
  
For "punched" i meant the data is been entered.
  
........................
  
John, to make this report even more meaningful and robust can we do something that we get "Balance of Cartons" through some code.
  
I remember that for one of my form "Petty Cash Book" i had the same issue and you gave me the sql for its query, which is working smoothly on that form.
The sql was:
  
SELECT [Petty Cash].TransactionID, [Petty Cash].TransactionDate, [Petty Cash].Details, [Petty Cash].AmountReceived, [Petty Cash].Expenses, DSum("AmountReceived","Petty Cash","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses","Petty Cash","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived","Petty Cash","TransactionID <=" &   [TransactionID])-DSum("Expenses","Petty Cash","TransactionID <=" & [TransactionID]) AS Balance
FROM [Petty Cash]
ORDER BY [Petty Cash].TransactionID;
  
Can we do some trick here also?
  
Regards,
Khalid
  
On Tuesday, December 17, 2013 1:12 PM, John Viescas <JohnV@msn.com> wrote:
  
Khalid-
 
If ConsignmentNo is the field that needs to be qualified by a table name, whatever possessed you to add the table name in front of the function name?
 
Right([CollectionVoucher].[ConsignmentNo], 2)
 
I don't understand what you mean by "punched".  If you add DeliveryVr as a simple field to the Totals query, you will get multiple rows.  If you want only one value, you could try using Max([DeliveryVr]) to get the highest value.
 
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: Tuesday, December 17,   2013 6:28 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals on Report Footer
 


John,
  
You explained it clearly and that is obvious. Yes i need to sort on the last two digits as they would increment for the next consignment, no matter it is "A" OR "B".
 
In "ClientsCargoBalanceQuery2" if i put this expression "SortOrder: Right([ConsignmentNo], 2)" and do not   select a table and run the query i get this message:
 
The specified field '[ConsignmentNo]' could refer to more than one table listed in FROM clause of you SQL statement.
 
And if i do select table "CollectionVoucher" and run the query i get this   message:
 
syntax error (comma) in  query expression 'CollectionVoucher.[Right([ConsignmentNo], 2)'
 
Coming to second part of my question:
table "CollectionVoucher" has the field DeliveryVr
For each Consignment, every Clients Products lot packed in the shape of Cartons is allotted a unique "DeliveryVr"
 
In the database DeliveryVr's have been started to be punched from ConsignmentNo: 2013-B-10 & 2013-A-11, that is   why in ConsingmentNo. 2013-B-08 & 2013-A-09 DeliveryVr field is blank or null.
 
Regards,
Khalid
 
On Monday, December 16, 2013 9:55 PM, John Viescas <JohnV@msn.com> wrote:
  
Khalid-
 
A "normal" sort will always show the -A consignments before the -B consignments.  It looks like you want to sort on the last two digits, so you would need to pull that into a calculated field in your query and sort on it.  Something like:
 
SortOrder: Right([ConsignmentNo], 2)
 
Which table contains DeliveryVr?  Because ClientsCargoBalanceQuery2 is a Totals query, including that field will cause the query to add it to the GROUP BY clause, and you'll get another row for each unique combination of ConsignmentNo, ClientCIN, ClientName, ConsigneeName, ExportDocs, Destination, and DeliveryVr.  If there is more than one DeliveryVr value for a combination of ConsignmentNo, and ClientCIN, you'll get extra rows.  If there are multiple values, which one do you want to display?
 
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: Monday, December 16, 2013 5:29 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals on Report Footer
 
  
John,
 
Yes...... that's it, great. I knew you will acomplish it, and i also know why it took little long because i was not clearly 
explaining my problem. Thanks a lot.
 
Now there are few more things in the subject report:
 
Reports Sorting and Grouping is:
 
Field ConsignmentNo, Desending, Group Header= Yes, Group Footer= Yes
Field ExportDocs, Ascending, Group Header=   No, Group Footer= No
Field DeliveryToClient, Ascending, Group Header= No, Group Footer= No
 
For different ClientCIN i see different sort order of ConsignmentNo, why is it so?
Requirement is in the following order:
 
2013-B-08
2013-A-09
2013-B-10
2013-A-11
--------------------------
 
You   can see in the picture of this report which i uploaded that there is a Label "Received vide Contract No."
aside of this label i want to put a textbox "DeliveryVr".
 
If i include this field "DeliveryVr,Text,5" in "ClientsCargoBalanceQuery2" output becomes wrong:
 
I give you an   example:
WITHOUT inserting "DeliveryVr" filtering ClientCIN 30, i get in database view:
 
ConsignmentNo 2013-A-09.......other fields... CountOfCarton 27
ConsignmentNo 2013-B-08.......other fields... CountOfCarton 45
 
WITH inserting "DeliveryVr" filtering ClientCIN 30, i get in database view:
 
ConsignmentNo 2013-A-09.......other fields... CountOfCarton 26
ConsignmentNo 2013-A-09.......other fields... CountOfCarton  1
ConsignmentNo 2013-B-08.......other fields... CountOfCarton 45
 
That is why i cannot get correct results in "ClientsCargoBalance(SelectiveClient)Query"
 
Regards,
Khalid
 
 
On Monday, December 16, 2013 5:10 PM, John Viescas <JohnV@msn.com> wrote:
  
Ah, well I assumed you needed the total for the Client AND the Consignment.  You need a grand total   for the client, so do:
 
Text69:
=NZ(DCount("CartonNo", "CollectionVoucher", "ClientCIN = " & [ClientCIN]), 0)
 
Text70:
=NZ(DSum("NoOfCartons", "ProgressOfConsignment", "ClientCIN = " & [ClientCIN]), 0)
 
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: Monday, December 16, 2013 12:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals on Report Footer
 
 
John,
 
Perhaps you didn't noticed Grand Totals: 27      27       0
 
They should be:                                              72     61       11
 
This is the main headache  i am suffering.
 
regards,
Khalid
 
On Monday, December 16, 2013 4:31 PM, John Viescas <JohnV@msn.com> wrote:
  
Khalid-
 
The picture you posted looks fine to me.  What is wrong with 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: Monday, December 16, 2013 10:01 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals on Report Footer
 
 
John,
 
For the code you wrote in immediate window its result was 0, which is correct as in reports output there is only one row in Detail Section and its   blank, also Text69 is giving correct figure 105, therefore Text71 is correct i.e 105
 
I checked it for ClientCIN 30, see below:
 
?NZ(DSum("NoOfCartons", "ProgressOfConsignment", "ConsignmentNo = '2013-B-08' AND ClientCIN = 30"), 0)
 34
 
?NZ(DSum("NoOfCartons", "ProgressOfConsignment", "ConsignmentNo = '2013-A-09' AND ClientCIN = 30"), 0)
 27 
 
I have uploaded preview of that report in "2_AssistanceNeeded" folder of group and you will see the location of figures 34 and 27
 
Khalid 
 
On Monday, December 16, 2013 12:56 PM, John Viescas <JohnV@msn.com> wrote:
  
Khalid-
 
You should now see 0 in Text70.
 
You could try going to the Immediate Window (CTRL+G) and typing:
 
?NZ(DSum("NoOfCartons", "ProgressOfConsignment", "ConsignmentNo = '2013-A-11' AND ClientCIN = 7"), 0)
 
… and press Enter.
 
What answer do you get?
 
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: Monday, December 16, 2013 6:13 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals on Report Footer
 
 
John,
 
Still in the same position, no change.
 
Khalid
 
On Monday, December 16, 2013 3:06 AM, John Viescas <JohnV@msn.com> wrote:
  
Khalid-
 
Then do this:
 
Text70:
=NZ(DSum("NoOfCartons", "ProgressOfConsignment", "ConsignmentNo = '" & [ConsignmentNo] & "' AND ClientCIN = " & [ClientCIN]), 0)
 
 
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: Sunday, December 15, 2013 8:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals on Report Footer
 
 
John,
 
At present i have four consignments from where i have started data entry:
 
2013-B-08
2013-A-09
2013-B-10
2013-A-11
 
its input mask is: 9999\-A\-99;0;-
We decide ourselves that next consignmentNo would be "A" OR "B"
 
Now different Clients are involved in different consignments, see an   example:
 
ClientCIN : 7
ConsignmentNo :2013-A-11
Count Of CartonNo: 105
NoOfCartons : Null (Detail Section is blank)
Text58 : 107
Text59 : Null (Blank)
Therefore Text60 having Control Source: =[Text58]-[Text59] is Null
 
Text69: 105
Text70: Null
Text71: Null
--------------
 
But when there are more than one or all ConsignmentNo involved for a specific ClientCIN
Text69 and Text70 are giving values of Text58 and Text59 for last ConsignmentNo, not giving Sum of Text58 and Text59 of all Consignments.
 
Hope you got what we are missing now with your final code given for Text69 and Text70
 
Regards,
Khalid
 
On Sunday, December 15, 2013 2:34 PM, John Viescas <JohnV@msn.com> wrote:
  
Khalid-
 
OK, then try this:
 
Text69:
=DCount("CartonNo", "CollectionVoucher", "ConsignmentNo = '" & [ConsignmentNo] & "' AND ClientCIN = " & [ClientCIN])
 
Text70:
=DSum("NoOfCartons", "ProgressOfConsignment", "ConsignmentNo = '" & [ConsignmentNo] & "' AND ClientCIN = " & [ClientCIN])
 
Must put quotes around a text literal   comparison.
 
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: Sunday, December 15, 2013 9:13 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals on Report Footer
 
 
John,
 
ConsignmentNo is Text and ClientCIN numeric, integer
 
Khalid
 
On Saturday, December 14, 2013 8:41 PM, John Viescas <JohnV@msn.com> wrote:
  
Khalid-
 
Is   ConsignmentNo a numeric data type or text?  And how about ClientCIN?
 
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: Saturday, December 14, 2013 3:57 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals   on Report Footer
 
 
John,
 
On both Text boxes i'm getting #Error message.
 
Khalid
 
On Saturday, December 14, 2013 6:27 PM, John Viescas <JohnV@msn.com> wrote:
  
Khalid-
 
Try this:
 
Text69:
=DCount("CartonNo", "CollectionVoucher", "ConsignmentNo = " & [ConsignmentNo] & " AND ClientCIN = " & [ClientCIN])
 
Text70:
=DSum("NoOfCartons", "ProgressOfConsignment", "ConsignmentNo = " & [ConsignmentNo] & " AND ClientCIN = " & [ClientCIN])
 
 
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: Saturday, December 14, 2013 12:42 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Totals on Report Footer
 
 
John,
 
Sorry to say that trying it in many different ways, i could not get anything instead of getting error messages. Could you please step forward one step ahead in help and guide further, what to do and where to do, more deeply.
 
I'm worried if i do modify my relationships here i might get in other   problems and by the way for this specific problem what relationships do you suggest?
 
Thanks & regards,
Khalid
 
On Saturday, December 14, 2013 2:46 PM, John Viescas <JohnV@msn.com> wrote:
  
Khalid-
 
We're back to your original problem.  What you want cannot be done with the old relationships.  Your FROM clause in the Record Source query will replicate the total produced in ClientsCargoBalanceQuery2 so that any client consignments that have multiple Progress records will appear multiple times.
 
You *might* be able to do what you want by using DSum in your footers instead of trying to sum the values produced by the Record Source.  You'll need to do a separate count of cartons from CollectionVoucher for the current consignment.
 
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: Saturday, December 14, 2013 7:22 AM
To: ms_access_professionals@yahoogroups.com
Subject: [MS_AccessPros] Totals on Report Footer
 
 
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
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  
 


  