Hello!

I have a report which has two running sum fields - and what I am trying to do is to divide one of them by the other. Here is a sample of how the report should look eventually:

Day CostToday CostToDate SalesToday SalesToDate RatioToday RatioToDate
1 100 100 1000 1000 10,00% 10,00%
2 200 300 3400 4400 5,88% 6,82%
3 150 450 2100 6500 7,14% 6,92%

CostToDate is the running sum of CostToday.
SalesToDate is the running sum of SalesToday.
RatioToDate should be CostToDate divided by SalesToDate for each line - note it is not a running sum of RatioToday.

Can anyone help? Thank you!

Odeh

Will the day numbers always be sequential? You can either write code to make these claculations in the report, or you can store them in your table. I would opt for storing the values in your original table. Then when the report was run, it could make your calculations, and then your arithmetic is easy.

If you are running the report on a date range, then you can store all the values in a temporary table, and base the report on that table.

Do you have a preference and other requirements?

mmcdonal

Look it up at: http://wrox.books24x7.com

Thank you for your reply!

My tables are set up like this:

tblTransactionDate:
txtDay
txtMonth
txtYear

tblTransactions:
txtTransactionType (i.e. purchase, sale, internal transfer, etc.)
txtProductType (i.e. food, beverage, etc.)
txtFromSection
txtToSection
Amount (i.e. in Euros)

The report is a monthly report - but for example, if I only have the movements of the first three days of the month registered, the monthly report (lets say for July 2008) will only show three lines. These two table are related - one-to-many using transactionID.

For me either option is fine, as long as I can get the value to appear in the field - but I am not sure how to do any of the options that you offered - can you explain, for example, I would transfer the running sums to a temporary table?

Thank you! It isnt possible to send screenshots through this forum, is it?

Regards! Odeh

You can send information directly to me using my contact information (click on my name).

Can you send me the database empty? It would be easier to see the structure and work on the solution with that.


mmcdonal

Look it up at: http://wrox.books24x7.com

Hello Thank you for your reply.

I clicked on your name, and then on 'send email' but I didnt find any way to attach the file or screenshots. Also I didnt see any personal details.

I think you helped me out with a problem a long time ago and provided it to me - I believe I still have the address somewhere - if I find it, may I send it to you on that?

Do you prefer I attach it as a zip file? Thank you!

You should be able to create a new text field on your report and make its Control Source a ratio based on the names of the running sum fields displayed on your report.

      -Phil-

Why are you answering threads that are MONTHS OLD???

The person who asked the question either got the answer or gave up. Almost surely he/she isn't going to come back months later trying to still get the answer.

Did it ever occur to you that people might be browsing old messages to get some info they can use? Why do you think these messages remain?

If this so offends your sensibilities then why are you insisting on even responding to an "out of date" message?

      -Phil-

Because we subscribed to them when they were current, and we are getting constantly peppered with emails with your random thoughts.



mmcdonal

Look it up at: http://wrox.books24x7.com

I post to a newsgroup because I actually feel I have something to contribute (not like someone just saying "yes, I agree" or "me too!"). When I browse these newsgroups I want to see answers to questions and I don't care about the dates on the messages. Many times I've seen questions that were posted months ago that never received an answer.

If you have such a problem with my "random thoughts" perhaps you should do as I do by not subscribing and periodically checking for non-random thoughts.

      -Phil-