Previous value

[warrio]

Hi there,

In a detail section, is there a way to get the previous field value in the
previous record?
I'm asking this because each current is calculated with the previous value.
The first a simple calculation based on current values.

looks like recursive code, but how to do without vba?

Thanks in advance for any good suggestion

 

[Jeff Boyce]

I can't tell from your description how you (and therefore, how Access)
determines "previous". You'll need to be able to define that in a way that
you can explain to Access.

If you are trying to build a "running sum" (again, not enough info in your
description), Access offers a Running Sum property for a textbox control in
a report definition.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[warrio]

Hi Jeff,
Thank you for having tried to understand my question. and sorry for not
making clear enough!
What I'm trying to calculate is chained year to date performance that you
calculate like:

First month: JanuaryPerf = X/(Y+Z)
Next month FebruaryPerf = (1 + JanuaryPerf) * (1 + (X/(Y+Z)))-1
Next month MarchPerf = (1 + FebruaryPerf) * (1 + (X/(Y+Z)))-1

It would have been easy to do it with a running sum if it was an addition,
but my desperate case, it's a product.
and I can't think of aggregate functions neither, because only the addition
is used whether in the sum or the avg function..

So what I'm doing now, because I haven't find an easier solution to it in a
query or a report is to calculate it in vba, store the result and display
them with the rest of info. unless you have a better advice

Thanks again

 

[Jeff Boyce]

I see nothing in your formula or description about "year". Are you
attempting to apply this across multiple years, or only for one year's data?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[warrio]

What I mean by a year to day performance is a performance calculated since
the beginning of the year until today

Generally from January to the current date (12 months maximum).



But I customize the formula by letting the user choose his own interval of
time. it can go from Date1 to Date2


Best regards

 

[Jeff Boyce]

I still am not clear how you are limiting the records on which you are
trying to do this to the current year. What if someone entered the
following two values for Dates: 1/1/2008 to 12/31/2009

That date range would have two Januaries, and two Februaries, ...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[warrio]

Hi Jeff,
Yes, I will have two records for each month if you specify from 1/1/2008 to
12/31/2009. But it will be distinguished by the year next to the month.
To be more accurate, what I display is the last day of the month in each
record

1/31/2008
2/29/2008
3/31/2008
4/30/2008
5/31/2008
....
1/31/2009
2/28/2009
....
12/31/2009

Here is an Excel file http://jump.fm/ADLWK where the calculation is made
easily but in a static way and in excel. The blue cells in column M are what
I want to calculate in Ms Access without having to use VBA.

Have good day

 

[Jeff Boyce]

Sorry, but I make it a practice not to open files from folks I don't know.
Can you provide a bit more description, say, the SQL statement you are using
in your Access query, and the formula you are using in Excel?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[warrio]

The sql query is made only to select data and make simple calculations.
It makes about 3000 characters. but it look like :
"SELECT TotalAccount, AllocationDate, TotalAccount-InOutCash AS
myCalculatedValue FROM tbl_Allocation WHERE AllocationDate>=" & Date1 & "
AND AllocationDate<=" & Date2

I respect your practice to not open files for security reasons, as long as
it doesn't go against productivity.
So what about an online spreadsheet? there is no code behind. and it's the
only way to satisfy your understanding.
Here it is:
http://spreadsheets.google.com/ccc?key=0Aj_8Rcmch9gZdHNodTg5aXNMcGdqUVFZbW96dE9LS0E&hl=en
All what I want to do is to get a running calculated value from a previous
record. I'm calculating values (such as the field myCalculatedValue in my
example query) and want to get in the same row, the value of this same field
from the previous row. Is my question still vague?

Nice week-end
Best regards and thanks again for spending time.

 

[Jeff Boyce]

It sounds like you have this information already available in Excel (or
another spreadsheet). Is there a reason you can't do this math on the
spreadsheet instead of in Access? Recall, unless you tell Access how to
sort/order records, the concept of "previous" is meaningless...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[warrio]

Go back to school!

It sounds like you have this information already available in Excel (or
another spreadsheet). Is there a reason you can't do this math on the
spreadsheet instead of in Access? Recall, unless you tell Access how to
sort/order records, the concept of "previous" is meaningless...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.