 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2005Running totals
I have a report that generates totals for a given month for a given team. The problem is that the only way I am getting accurate totals is if I start the parameter at 1/1/2005. What if I want to run a report for just the month of March? I would need the total spent for the month of march only but the remaining total budget field should still take the total for all purchases and give me an accurate number. The Grand Total field says this =Sum([Sum Of Amount of Purchase]) and the remaining team budget field says this =[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help with this would be totally appreciated. I am a rookie !! You can retrieve the Year To Date value using: -a subreport -DSum() -a totals query join to your report's record source -code -combo box  SignatureDuane Hookom MS Access MVP -- >I have a report that generates totals for a given month for a given team. >The [quoted text clipped - 9 lines] > =[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help with > this would be totally appreciated. I am a rookie !! I am very new to Access like only 2 months lol. Can you let me know the easiest way :) If you could give me a start that would be great. I am also trying to save these pages as data access pages so users will be able to go to our intranet site and run their own monthly reports. I have been sucessful with this but still have the running totals problem. I hope you understood the initial question. The basic parameter for my reports says enter a start date, enter an enddate, enter a team name. The report comes up with a totals field based on the dates and also a year to dae field. The only way the year to date field comes up correct is if the user starts the parameter from 1/1/2005. I hope that helps you more. Ryan > You can retrieve the Year To Date value using: > -a subreport [quoted text clipped - 16 lines] > > =[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help with > > this would be totally appreciated. I am a rookie !! Consider using controls on forms for your users to enter criteria. This doesn't take too much effort and improves the user experience tremendously. Martin Green has some good tutorials on this at http://www.fontstuff.com/access/index.htm. Now, since your criteria is coming from a control on a form, create a query similar (converted to a totals query) to your report's record source only set the criteria to something like: Field: [YourDateField] Total: Where Criteria: Between DateAdd("d",-DatePart("y",Forms!frmYourForm!txtEndDate)+1,Forms!frmYourForm!txtEndDate) And Forms!frmYourForm!txtEndDate You can add this totals query to your report's record source and include your ytd column. SignatureDuane Hookom MS Access MVP -- >I am very new to Access like only 2 months lol. Can you let me know the > easiest way :) If you could give me a start that would be great. I am also [quoted text clipped - 39 lines] >> > with >> > this would be totally appreciated. I am a rookie !! Another option: Add a field to your query as follows -- iif(format([DateFld],"mmmm")=[Please enter desired month]),[Amount of Purchase],0) as PurchasesForMonth (where DateFld is the name of your purchase date field) Then in your report footer, you'll have fields with: =Sum([PurchasesForMonth]) and =Sum([Budget Total]) - Sum([PurchasesForMonth]) > You can retrieve the Year To Date value using: > -a subreport [quoted text clipped - 16 lines] > > =[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help with > > this would be totally appreciated. I am a rookie !! I know I am a pain in the butt... I am still having no luck with this, I was wondering if their was any way I could send my table structure to someone, or maybe a few of the queries I am having trouble with?? I am down to crunch time with this database and need assistance... Any help would be greatly appreciated!!! > Another option: > Add a field to your query as follows -- [quoted text clipped - 26 lines] > > > =[BudgetTotal]-[Sum Of Amount of Purchase Grand Total Sum]. Any help with > > > this would be totally appreciated. I am a rookie !! Did you try my recent suggestion? Can you provide the SQL view of your report's Record Source? SignatureDuane Hookom MS Access MVP -- >I know I am a pain in the butt... I am still having no luck with this, I >was [quoted text clipped - 41 lines] >> > > with >> > > this would be totally appreciated. I am a rookie !! I was able to create a query for budget totals that took BT(Budgettotals)-AP (amountofPurchase)=Remaining team budget. I also added a query using the iff statement that when you run the query is gives you the certain month which allowsfor ytd totals to come up correct. I added the sub-report to the bottom of the ytd report. Thank you guys for all your help in this, like I said I apologize for not knowing much :) Ryan P.S. Hope what I wrote makes sence. I need to learn a little about VB and SQL statements. I think it will make me a much more valuable asset :) > Did you try my recent suggestion? > Can you provide the SQL view of your report's Record Source? [quoted text clipped - 44 lines] > >> > > with > >> > > this would be totally appreciated. I am a rookie !! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.