Previous value

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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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

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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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

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?k...E9LS 0E&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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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

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" wrote in message
...
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?k...E9LS 0E&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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de
news: ...
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" wrote in message
...
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

Go back to school!


"Jeff Boyce" a écrit dans le message de news:
...
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" wrote in message
...
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?k...E9LS 0E&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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de news:
...
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" wrote in message
...
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" a écrit dans le message de
news: ...
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" wrote in message
...
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