Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 1st, 2007, 12:09 PM
Registered User
 
Join Date: Apr 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Report Total

[u]Background:</u>
Developing a DB to track purchase orders. Each order can contain one or more items.

[u]Table Design/Relationship:</u>
I have two tables 1) Order and 2) OrderDetails in a one to many relationship. The Order Table contains general order info and has a field "GrandTotal" which is the sum of all the item prices in the order. The OrderDetails Table has a record for each item in each order.

[u]Objective/Problem:</u>
I have a report that joins the two tables and displays the current months orders and the contents of each order. The report is grouped by order number and displayed in the group footer is the GrandTotal field. The order contents (the items making up the order) is in the details section. In the report header i want to display the the total of all the orders. In a text box i placed =Sum([GrandTotal]) but the grandtotal of each item gets multiplied by the number of items in the order. For example, if there is one order with a GrandTotal of $2 and in that order there are two items, Sum([GrantTotal]) returns $4 instead of $2.

Does anyone know how I can display the sum of all the order's GrandTotals in the report header?
 
Old June 2nd, 2007, 08:22 AM
Authorized User
 
Join Date: May 2006
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

Did u tried Running Sum option in the report?

Try this way:

You put another unbound text box (let us say text0) near by the GrandTotal and inside that you put =GrandTotal and in the property of this unbound text box, set running sum true. Then place another text box on the report in which you need the real grand total. Refer the Text0 in this new text box.

Hope it helps.

Ashfaque

 
Old June 4th, 2007, 10:45 AM
Registered User
 
Join Date: Apr 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ashfaque thank you for the reply,

I had tried your suggestion and it didn't work for me. I added a text box in the group footer and set the RunningSum property to "Over All". It keeps track of the correct sum but when i set a reference to it in the report header it only displays the GrandTotal for the first group instead of all the groups. If i place a text box in the report footer with a reference to the text box in the group footer then it works fine. So i tried setting the text box in the report header to the text box in the report footer but it still only shows the sum for the first group. Still not sure what went wrong here...

But thanks again for the suggestion.

On another forum someone suggested the DSum function and that did the trick

DSum function:

="Report Total: " & Format(DSum("GrandTotal","Orders",[Report].[Filter]),"$#,###.00")




 
Old June 4th, 2007, 12:16 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

My suggestion is to go to Help > Sample Databases and then open the NorthWind sample database. In it you will find the database design you want to use, and you will find this report / subform feature you want.

I think for the report, you can create a text box in the details section, and make that the extended price (qty * price), and you can hide this if you want. Then in the group footer, sum this textbox.

Did that work?


mmcdonal
 
Old June 6th, 2007, 08:55 AM
Authorized User
 
Join Date: May 2006
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

If still face problem, send your db to me in zip mode containing only source table (least records), query and report. I will look into it.(Write in detail)

Ashfaque

[email protected]
 
Old June 10th, 2007, 03:01 AM
Authorized User
 
Join Date: May 2006
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

Chris,

Sorry for the delay in reply...

If you already did using Dsum then no need of anything. Bcz I was planning to go ahead with same. But finding you are still interested how it would work in another way, I just sent updated db back to you. Confirm if you received and its working as you desired. What I did is with the help my friend Paul, removed the control of your textbox102 and placed a code line in Report Footer's OnFormat event including 2 additional textboxes called Text133 & Text143.

You may keep them visible false as I did.

Ashfaque

 
Old June 11th, 2007, 10:56 AM
Registered User
 
Join Date: Apr 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Ashfaque,

It worked perfectly, knowing a little more about how report events work will definitely come in handy in the future.

Solution from Ashfaque:
Place text box in group footer with RunningSum property set to "Over All". Place another text box in report footer with reference to the text box in the group footer. Then in VBA use the format event to execute the following code that will display the report total in a text box in the report header:

Reports![your report name]!Text102 = "Report Total: " & Format(Reports![your report name]!Text143, "$#,###.00")





Similar Threads
Thread Thread Starter Forum Replies Last Post
Total Count of Specific Records in a Report Odeh Naber Access 5 August 1st, 2007 03:50 AM
Sub Total Rjh Reporting Services 0 June 21st, 2006 04:11 PM
Export Access Report to Excel - Total column moves rklass BOOK: Access 2003 VBA Programmer's Reference 1 June 9th, 2006 08:50 AM
sub report total sanjay_jadam Crystal Reports 1 June 24th, 2005 12:55 PM
Group total in a report tsimsha Classic ASP Databases 1 February 28th, 2005 05:40 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.