Report groups by City, Date, has a running total oddity - help

[KHerm]

I'm trying to get the right running total in an Access Report. The report runs off a select query which includes City, Date, Account_Entry.

In my report I have Sorting and Grouping this way:

City
Group Header Yes
Group Footer Yes
Group On Each value

Date
Group Header Yes
Group Footer Yes
Group On Year

Date
Group Header Yes
Group Footer Yes
Group On Month

The grouping by City is working fine.

I also get my Date grouped by Year and then by Month.

I'm having trouble getting a running total by city only that carries across each year.

I'm using Account_Entry twice because I want a "change" column, and I want a "running total" column.

In the Date Header (by month) area I have:
{=Format$([Date],"mm/yyyy&quot},{sum([Account_Entry])},{sum([Account_Entry]}

I'm using {} to show the text boxes here.

For the first Account_Entry sum I have selected: Running Sum... Over Group
For the second Account_Entry sum I have selected: Running Sum... No

In the Date Footer (by year) are I have:
{"Year Total"},{sum([Account_Entry])},{sum([Account_Entry]}

For the first Account_Entry sum I have selected: Running Sum... Over Group
For the second Account_Entry sum I have selected: Running Sum... No

Right now I have dates in the years 2000-2001 and my output for one City looks like this:

CITY 1
Month Running Total Change
2000
11/2000 $6,000 $6,000
12/2000 $151,000 $145,100
Year Total $151,000 $151,100

2001 01/2001 $12,400 $12,400
02/2001 $216,900 $204,500
03/2001 $531,600 $314,700
Year Total $682,700 $531,600


The year total for each City comes out right as a running total of both years (and the Change column is right, too)!
But as you can see, the monthly running total starts over when the the year 2001 starts.
I've tried using Running Sum... Over All, but that makes the Running Total continue on to each city and I don't want that.

If you've made it through all this I thank you! And if you have any thoughts, I thank you in advance!

Ken

 

[Guest_imported]

Ken, you probably already know the problem lies in the Grouping you use for the Report. You can try one of two things. First, get rid of some of the grouping, and use the "Hide Duplicates" property in the relevent field of the Report so that you don't repeat some of the Month or Year values over and over again. That should help with the Year transition. The second way is something I'll have to look at first. It would involve doing the running sums in the query which works for some data needs but I'm not sure the results will do what you need. I'll work on that if you want to try the first suggestion. I haven't posted to this board before so I'm kicking the tires here as to how this place works. I'll check back.
Paul

 

[KHerm]

Thanks, Paul, for the reply. I keep coming back to this forum for help, and always get it. Hopefully someday I'll be able to offer help in return!

If you'd work on your second method for the problem, I'd appreciate it. I'll check back too.

I'll also try the "Hide Duplicates" in the morning....but I'm not sure it's a duplicate issue, but we'll see.

Thanks again,

Ken

 

[Guest_imported]

Sorry, it isn't a case of duplicates, it's a case of removing one level of grouping so the running sums don't have to cross group levels. If you remove the Year Group Level and put your Year textbox in your Month Group Level, then the Year will repeat itself for each record. By selecting the Hide Duplicates, the Year will only show once at the first record and not again until it changes to a new year. I don't know if it will help or not. Your totals for the Year may depend on that grouping. Your getting the break in your Running Sum because you have it set for Over Group so when the Group starts over, so do the Running Sums(which I'm sure doesn't come as a surprise to you). By getting rid of one of the Group Levels, you get rid of that break. The Hide Duplicates is just a tool for keeping the appearance of the Report uncluttered. I'll look at things when I get to work and try and get back to you ASAP.
Paul

 

[KHerm]

I'm between a rock and a hard place. I need the month grouping to get my montly totals, and I need my year grouping to get the yearly totals.

I can get the year to appear correctly by hiding duplicates and not putting that text box in the year group header, and I was about ready to remove my year group footer, but I realized I was going to lose those totals. Can dummy fields be created in Access Reports to hold variables which can be updated and then appear when I need them?

Ken

 

[KHerm]

Looking at this problem some more, I see that my running total for the Running Total column really needs to be grouped by city. But I can't lose my monthly groupings or yearly groupings for totals. I'm going to try something with city groupings, but if anyone out there has a brilliant idea I'd love to hear it! I get email notification of responses, so I'll be keeping an eye out.

Ken

 

[Guest_imported]

Ken, I had no luck with the queries. They actually do just the opposite(it's been 3 years since I used them). They take a Running Sum value, such as a Clock on a Compressor, and breaks that down into individual amounts. So if the compressor clock is checked weekly, the value is entered and the amount of run time(the differece between weekly values) is calculated. I thought I might be able to reverse the process somehow but no luck. Here's a quick easy idea. Have the Year Grouping with the Year Textbox in the Group Header, but set the visible property to No for the Header, and then put the Year Textbox in the Month Group Header as well and used the Hide Duplicates method. Then set the Year Footer property to Visible and do your calculations. Worth a try.
Paul

 

[Guest_imported]

Ken, I've worked out a solution if you still need it. Basically it involves 2 tables, 2 queries 1 module and your report. Let me know. I don't want to post it if you've already found something else.
Paul

 

[KHerm]

Haven't solved it at my end yet. I do want to see what you came up with!

Thanks in advance!

Ken

 

[Guest_imported]

OK, I'll make this as generic as possible. I'll assume that your first table(I'll call Table1) has 3 fields. City, Date, and Account_Entry.
Create a second table, Table2 with fields City, Date and RunningSum(feel free to name things anyway you want after we're done).
Create Query1 using Table1. The fields will be City, Date, and Account_Entry. Sort Ascending on fields City and Date.
Create Query2 using Table1. Add City, turn on the Totals and Group By for City. This will return one record for each City in Table1. Sort Ascending
Create Query3(sorry I miscounted the number of queries). Add Query1 and Table2. Join on City and Date. Add all the fields from Query1 and RunningSum from Table2 and Sort City and Date Ascending.
One more. Create Query4. Add Table2 and add all the fields by draging the * down to a column. Turn it into a Delete Query(select Delete from the query menu) and select From on the Delete line for the column.
Create a new module and copy this code into it.

Sub runsums()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim totRS As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset("Query1", dbOpenDynaset)
Set rst2 = db.OpenRecordset("Query2", dbOpenDynaset)
Set rst3 = db.OpenRecordset("Table2", dbOpenDynaset)
rst.MoveFirst
rst2.MoveFirst
Do Until rst.EOF
If rst!City = rst2!City Then
totRS = totRS + rst!Account_Entry
rst3.AddNew
rst3!City = rst!City
rst3!Date = rst!Date
rst3!RunningSum = totRS
rst3.Update
rst.MoveNext
Else
rst2.MoveNext
totRS = 0
End If
Loop
End Sub

Your Report should have a Record Source from Query3. Add the grouping you want and just put the Running Sum value in the Detail Section.
In the Open event for the Report put this code.

Private Sub Report_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDelRec"
Call runsums
DoCmd.SetWarnings True
End Sub

This will delete the values in Table2, and recalculate them each time the report is run so the running sum is always current. The important thing is that the information is sorted the same in all the queries. That's why I use them and not the tables for everything. Give it a try(ON SAMPLE DATA) and let me know.
Paul

 

[Guest_imported]

Couple of changes.
The code in the Open Event for the Reprot should say
DoCmd.OpenQuery "Query4"
Also, Query2 should use Query1 as the Source and not Table1. That way if you want to add a parameter like Not Denver, Query2 will reflect that parameter.

 

[KHerm]

Paul, you definately deserve a star for your helpfulness (but I can't give you one as long as you're a visitor).

I like the look of your code very much, I'll be working on it Monday or Tuesday of next week again!

Big Thanks!!

Ken

 

[Guest_imported]

My data sample was small and there were no null values or glitches to deal with. I'm sure you may have small issues to deal with, but I think I got most of it worked out. Hope it all goes well. Let me know. Have a good weekend.
Paul