Need Help on report calculation

[Cwittmaack]

Hi All
I have a net change per customer for each day which is pulled by date from the Query on my current report, the owner now wants me to add a running total for the month to the same report. These are my fields

new _accts
dlr_access
disconnect

My current calculation located in the Detail portion, in a text box Control Source of the Report for net change is =[new_accts]+[dlr_access]-[disconnect]
Which gives me the current date net change for each customer

So now I need to have a Running Total, IE. 09/01/2010 to 09/15/2010 being the current date is the 15th, giving me the current total net change to date. Without changing the current days net change information.
I thought about using =Count() but have no idea how or were to put it. I have tryed deferant variations

 

[MarkK]

Check out the RunningSum property of a textbox on a report.
Cheers,

 

[Cwittmaack]

Thanks Lagbolt

I have selected both (over group) and (overall). This is what the results I get.

Example Data:
09/01/2010 Net Change Total To Date
Customer 0001 3 New, 5 Disconnect, 4 Dlr Access 2 2
Customer 0003 2 New, 0 Disconnect, 1 Dlr Access 3 5
Customer 0005 6 New, 4 Disconnect, 0 Dlr Access 2 7
Customer 0010 0 New, 8 Disconnect, 1 Dlr Access -7 0

This is what I need
09/02/2010
Customer 0001 2 New, 0 Disconnect, 1 Dlr Access 1 3
Customer 0003 0 New, 2 Disconnect, 1 Dlr Access 1 4
Customer 0005 1 New, 3 Disconnect, 0 Dlr Access -2 0
Customer 0010 5 New, 12 Disconnect, 3 Dlr Access -4 -11
Customer 0019 2 New, 0 Disconnect, 0 Dlr Access 2 2

09/01/2010 and 09/02/2010 data is added together for a total to date

I need the net change from current date, data entered which I have, but as you can see from the top data example the total to date is not correct. This happens when I use both over group and overall is selected.
Sorry for the small font.
Would it be better to create a total table that stores the data and pull the information from the seperat table for Total to Date?

 

[MarkK]

I don't understand your data. Probably it makes a ton of sense to you. From your original post you have an expression ...

NetChange = [new_accts]+[dlr_access]-[disconnect]

What I would do is calculate that in the query that drives the report and if you need to do other math too, consider doing more in the query. Then it should be a much simpler matter to do a running sum of THAT field on the report.
Does that help?

 

[Cwittmaack]

Lagbolt, your correct on the code for the net change, i have that in a text box in the control source. I have never performed any math in a query before, will have to read some on that, ill get back with you if i cant figure it out. thanks

 

[MarkK]

Let's say you have a query ...

SELECT new_accts, dlr_access, disconnect
FROM tTable;

You can calculate net change in that query using ...

SELECT new_accts, dlr_access, disconnect, [new_accts]+[dlr_access]-[disconnect] As NetChange
FROM tTable;

Now you can set the record source of the report to the new query and the field 'NetChange' is not only available, it is completely indistinguishable from any other field!!!

 

[Cwittmaack]

will give that a try, will have to creat a new query, will let you know thanks Lagbolt

 

[Cwittmaack]

Lagbolt im going to try and upload a database tomarrow for you to look at. thank you for your help so far.

 

[Cwittmaack]

Lagbolt here is a database like im using i put together yesterday, if you run the report with the date 09/06/2010 and 09/08/2010.. i have programed the net change like im using, i hope this will give a better understanding of what im trying to do. please let me know what you think..thanks

 

[MarkK]

Check out the query. See how it does the math? Check out the report. See how it simply displays a field called NetChange provided by the query?
Cheers,

 

[scalextric59]

Is this what you need?

 

[Cwittmaack]

LagBolt

I understand the Query SQl programming the As NetChange now, thank you, I tried the Running Sum for the Total To Date column, it does not seem to work, Will I have to create a separate Query and pull that information out without using a date, (cross Query)?

 

[Cwittmaack]

Hi Scalextric59
I have never used sub reports in a report, trying to understand what you did and how u did it, it does produce a total to date, but havent been able to get that to print to paper.. it showes up in preview but just does not print.. it will be aftewr the weekend untill i can get back with you both. thanks

 

[MarkK]

I think using a subreport to provide the value of a single field is an overweight solution.
See Net Change running sum implemented here with a field in a query and a bound control.

 

[Cwittmaack]

Lagbolt will take a look, thanks

 

[scalextric59]

Okay, I am reposting yor DB since I made a mistake on the subreport and that was the reason it was not printing. The subreport calculates the total to date (any date before or on the date entered) and it is linked to the main report by "dlrnum". The subreport has two fields that are not visible, one is the dealer number and the other is the date from the main report that I use to limit the query

 

[Cwittmaack]

LagBolt
I looked at what you had done, and it does a running total of the current data entered. Not what I need. If Customer 00001 puts information on 5 times in the month and puts more on the last day of the month the running (total to date) would add all of customer 00001 and the current date to the (total to date) for that customer along with the current day’s net change.

 

[Cwittmaack]

Scalerxtric59
Your sub-report does pull the information that I need, am going through the Access 2003 Bible trying to find out how you performed the operation, I looked at the Sub-report details and SQl Query. When I do it in my test database it asks for the date, then dealer number which is wrong, my access knowledge is not that in-depth, what we needed has grown way bigger than I had expected as far as reports. Wish there was an easer way..lol

 

[Cwittmaack]

Doramide7
have no idea what you want to say sorry

 

[boblarson]

Doramide7
have no idea what you want to say sorry

It was just a spammer trying to have their signature have links there. They've been removed.