Data variance calculation report

[O2BSmart]

Hi, please can you help me do the following:



Table1 contains the following fields:
CreateDate : DataType = Date/Time
Value1: DataType = Number

I need to create a Report that lists the following information fields from Table1

CreateDate, Value1(of the current record), Value1(of the previous date record), Difference between the two previous Value1 fields.

What is the easiest and most efficient way to do this report?

[NTC]

one must have a query at the record source that results in the correct 2 records. that is your starting point.

Note that in your post Value 1 is shown twice, side by side - - that will not happen unless you want to go into cross tab format which I would avoid.

There will be 2 records.

then Reports has math ability - which you can set up as part of the wizard set up; or do manually with an unbound text box.

[O2BSmart]

Thanks, I agree with you on the duplicte field naming (Value1) in my post. The problem I have is in creating the query that needs to be applied to all the records in the table. i.e. (determine the variance of Value1 between all the consecutive date records)

Basically I would like to create a monthly report on the daily change of Value1.

I have tried using the mathematical functions of the reporting module, but they do not allow me to access the previous record Value1 Value....

How do I do this?

[NTC]

my first post assumed your report would contain just the 2 records you need. the report ability to sum would work fine in this case.

If instead you need a greater/large set of records, and math that only sums each set of 2 consecutive records - - then you are into an advanced sql statement for which there is no vanilla solution. Even though I do this all the time, it is an sql statement that I can't rattle off the cuff, and would need some time to noodle thru. I don't have that time right now but may be able to post something later.

[O2BSmart]

Thanks for your assistance. I seem to have found a simillar request in the "Simillar Threads" section of this page. Their proposed solution is also based on a advanced SQL statement as per your suggestion. Thanks a million for your assistance.