Calculations using previous record calculation (1 Viewer)

fibayne

Registered User.
Local time
Today, 19:08
Joined
Feb 6, 2005
Messages
236
Been looking through various posts and feel sure this can be done, I have partially done it using PrevRecVal module I found on the web.
I created what I need to do in Access in Excel first, the problem doing this in Access (for me) is I need to refer to the results in the previous record, PrevRecVal helped but I need help to finish if possible.

The main report holds client policy data of which InvestAmount is used to start the calculation of the sub report, the data entered to run the calculations on the subreport would be
QtrDate
CurrentUnitPrice

the Excel spreadsheet is attached which may help explain what I'm trying to do.
Any help with this would be greatly appreciated
thanks
Fi
 

Attachments

  • Pre Funding Workings.xls
    52 KB · Views: 90

CJ_London

Super Moderator
Staff member
Local time
Today, 18:08
Joined
Feb 19, 2013
Messages
16,553
The easiest way is to use a subquery - assuming your table is per your main table in the spreadsheet and it is the units that needs to be calculated, the query would look something like this:

Code:
SELECT QTRDATE, 
(SELECT Top 1 Units FROM MyTable AS Tmp WHERE ClientID=myTable.ClientID AND QTRDate=DateAdd("m",-3,myTable.QTRDate))+(Fixed/UnitPrice) AS NewUnits 
FROM myTable
 

fibayne

Registered User.
Local time
Today, 19:08
Joined
Feb 6, 2005
Messages
236
Hi CJ,,,many thanks for your reply, working on this now...Fi
 

fibayne

Registered User.
Local time
Today, 19:08
Joined
Feb 6, 2005
Messages
236
Hi CJ
I have the main form which contains Policy data and the subform which contains the asset valuation data for the policy, the current unit calculation is previous unit calculation less units deducted for charges, as in the excel spreadsheet, below is the query behind the subform at the moment, and your suggestion, below that is my attempt at adding the subquery, it runs but the NewUnits field is blank, can you tell what i am doing wrong,,,,,thanks again Fi

OriginalQuery -
SELECT tblNSAssetValuation.ValuationID, tblNSAssetValuation.PolicyAssetsIDFK, tblNSAssetValuation.ValuationDate, tblNSAssetValuation.LatestAssetValueDate, tblNSAssetValuation.UnitsNS, tblNSAssetValuation.PriceNS
FROM tblNSAssetValuation
ORDER BY tblNSAssetValuation.ValuationDate DESC;

CJQuery -
SELECT QTRDATE,
(SELECT Top 1 Units FROM MyTable AS Tmp WHERE ClientID=myTable.ClientID AND QTRDate=DateAdd("m",-3,myTable.QTRDate))+(Fixed/UnitPrice) AS NewUnits
FROM myTable

NewQuery -
SELECT tblNSAssetValuation.ValuationID, tblNSAssetValuation.PolicyAssetsIDFK, tblNSAssetValuation.ValuationDate, tblNSAssetValuation.PriceNS, tblNSAssetValuation.LatestAssetValueDate, tblNSAssetValuation.UnitsNS, (SELECT Top 1 UnitsNS FROM tblNSAssetValuation AS Tmp WHERE ValuationID=tblNSAssetValuation.ValuationID AND ValuationDate=DateAdd("m",-3,tblNSAssetValuation.ValuationDate))+([UnitsNS]/[PriceNS]) AS NewUnits
FROM tblNSAssetValuation
ORDER BY tblNSAssetValuation.ValuationDate DESC;
 

fibayne

Registered User.
Local time
Today, 19:08
Joined
Feb 6, 2005
Messages
236
I was referencing the incorrect ID field, there is now data in the NewUnits field (not the correct data...) but I'm working on that, thanks again for your help much appreciated ...Fi
 

ypma

Registered User.
Local time
Today, 18:08
Joined
Apr 13, 2012
Messages
643
I have had ago at reproducing as per your xl example into access . Basically i created a table then a form and used the after update of QrtDate to populate the other fields. It is not best practice to save calculated fields but, i cannot think of another way, as we need previous data.
Hope its of some use to you
Regards
I am using access 2010
 

Attachments

  • PreviousFieldLookup.accdb
    472 KB · Views: 89

fibayne

Registered User.
Local time
Today, 19:08
Joined
Feb 6, 2005
Messages
236
Hi ypma,,,thank you for taking the time to do this, although not clear on the spreadsheet the unit price is manually updated each quarter end also, could your update code accommodate this also? thanks Fi
 

ypma

Registered User.
Local time
Today, 18:08
Joined
Apr 13, 2012
Messages
643
I coded the unit price to increase by .01 each quarter as that is what I thought happened ,Its the first line of code in the after update event of the QrtDate, which can be deleted . Note you will have to enter the unit price before you enter the QrtDate. Let me know if this is clear ?
Regards
 

fibayne

Registered User.
Local time
Today, 19:08
Joined
Feb 6, 2005
Messages
236
Hi...I took the first line of code out, then added a new line and the fields updated as expected but when I deleted all but the first row in the table then added a new unit price on the next line on the form, then added the qtr date, the only field that calculated was Unit Move, is there a particular sequence that all the data needs to be added to start the calculations from scratch? thanks again for your help....Fi
 

ypma

Registered User.
Local time
Today, 18:08
Joined
Apr 13, 2012
Messages
643
The id is an auto number and when you deleted the row the auto number still produces the next number in sequence prior to your deletion . and that is why it does not work if the new number is 8 and 7 is missing you will get blanks . Suggest you re-load my example and delete only the contents .Or create another duplicate table and delete your old one and rename the new table the same the old one.. ,ID 1 in the new table should have your value and units that you wish to start with . Auto number are not recommended as they can sometimes play up. when you develop you final database you should consider using and alternative numbering system . Please keep me in the loop of your progress
Regards
 

fibayne

Registered User.
Local time
Today, 19:08
Joined
Feb 6, 2005
Messages
236
thanks for this ypma,,,,will keep you posted on my progress and thanks again for your help I have been trying to work out a way to do this do this for a month now! :) Fi
 

Users who are viewing this thread

Top Bottom