Calculation of data.

[DB-UK]

Hi, i want to do this calculation in my data base is it possible?

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[The_Doc_Man]

First, hello and welcome to the forum.

Second, if you were to see the name "Calculated field" you might be tempted to try that. But the answer is NO because for Access, that kind of calculation is not possible at the table level. You are thinking in terms of Excel (for which that IS possible). The way this MIGHT be doable involves looking up something called a sub-query where you can look up the previous record. In this case it would be possible since you have a year field that you can use to define a "previous" record. However, be aware that Access does not define a "previous record" in the same as is done for Excel.

However, third, if much of what you want to do includes Excel-like actions, you should use Excel. Therefore, since you asked the question as you did, I have to clarify your intent. Were you in fact looking at an Excel "database" or were you really planning to use Access? It makes a HUGE difference. If you have reasons to use Access, then we can proceed in that direction. If you were not sure what you wanted to do, tell us in simple language what you want to do with your data.

 

[arnelgp]

if you are using Access, you can achieve the same using a Query.
btw you don't need field 9. you can create a calculated column for it:

SELECT
    yourTableName.Company,
    yourTableName.[Analysis Date],
    yourTableName.Period,
    yourTableName.C3,
    (SELECT TOP 1 T.C3 FROM yourTableName AS T WHERE T.Period = yourTableName.Period-1)-[C3] AS 9
FROM yourTableName;

If in Excel, create the following formula and copy down:

=IFERROR(D1-D2,"")

 

[DB-UK]

First, hello and welcome to the forum.

Second, if you were to see the name "Calculated field" you might be tempted to try that. But the answer is NO because for Access, that kind of calculation is not possible at the table level. You are thinking in terms of Excel (for which that IS possible). The way this MIGHT be doable involves looking up something called a sub-query where you can look up the previous record. In this case it would be possible since you have a year field that you can use to define a "previous" record. However, be aware that Access does not define a "previous record" in the same as is done for Excel.

However, third, if much of what you want to do includes Excel-like actions, you should use Excel. Therefore, since you asked the question as you did, I have to clarify your intent. Were you in fact looking at an Excel "database" or were you really planning to use Access? It makes a HUGE difference. If you have reasons to use Access, then we can proceed in that direction. If you were not sure what you wanted to do, tell us in simple language what you want to do with your data.

Hi, The Doc Man,
Thanks for your reply its much appreciated.
Indeed the calculation is in an Excel spread sheet, what i am trying to do is convert my Excel spreed sheets into Access data bases purely for presentation and ease of use.

But as you point out this is not the best solution for some of the more complicated scenario's, i will try a sub-query and see how that works.

 

[DB-UK]

if you are using Access, you can achieve the same using a Query.
btw you don't need field 9. you can create a calculated column for it:

SELECT
    yourTableName.Company,
    yourTableName.[Analysis Date],
    yourTableName.Period,
    yourTableName.C3,
    (SELECT TOP 1 T.C3 FROM yourTableName AS T WHERE T.Period = yourTableName.Period-1)-[C3] AS 9
FROM yourTableName;

If in Excel, create the following formula and copy down:

=IFERROR(D1-D2,"")

Hi, arnelgp,

Thanks for your reply its much appreciated.

I will try your solution and keep you posted.

Cheers.

 

[DB-UK]

Hi arnelgp,

I have followed your advice and code string to solve my problem and can say that it was 100% successful, attached screen shot.

Thank you for your expertise and time solving my problem, it was very much appreciated.