In the attached Access Database ("Cumulative Illustration"), I have a table FiFO_Temp_Live, which contains purchase transactions by folio (Field [Folio No]), product purchased (Field [Product Code]), Investor (Field [Mod PAN]) date (Field [TDate]), Purchase Amount (Field [Amount]), Price of Purchase (Field [NAV]) and Units of the product allotted (Field [Units].
There is a field [Ttype], which can take the following values: "P" (for outright purchase of a fund), "SI" (for a switch from another fund), "DR" (for dividend declared being reinvested into the fund, creating more units), or "DP" (for dividend paid, which has no impact on units).
There is another derived field [Main Ttype], which just clubs "P" and "SI" as one, and leaves "DP" and "DR" as unique fields.
I wanted to design a query to calculate the running total for the "P" and "SI" transactions alone, by each row.
This query is named Cum_P in the attached database.
It works fine, except that I have a problem in the first two rows. The first row is a purchase ([Ttype]="P") of 4732.608 units on 12th OCt 2012, and the second row is a switch ([Ttype]="SI") of 1550.729 units on the same day.
Given my running total formula of
the cumulative sum in both the first two rows is the same- 6283.337.
I would like the query to compute the total for the first row as 4732.608 and the second one as 6283.337.
This is a very easy problem to solve in Excel, but I've run up against a brick wall in Access.
Some help would be greatly appreciated.
Thank you for your time.
Sanjay
There is a field [Ttype], which can take the following values: "P" (for outright purchase of a fund), "SI" (for a switch from another fund), "DR" (for dividend declared being reinvested into the fund, creating more units), or "DP" (for dividend paid, which has no impact on units).
There is another derived field [Main Ttype], which just clubs "P" and "SI" as one, and leaves "DP" and "DR" as unique fields.
I wanted to design a query to calculate the running total for the "P" and "SI" transactions alone, by each row.
This query is named Cum_P in the attached database.
It works fine, except that I have a problem in the first two rows. The first row is a purchase ([Ttype]="P") of 4732.608 units on 12th OCt 2012, and the second row is a switch ([Ttype]="SI") of 1550.729 units on the same day.
Given my running total formula of
Code:
Round(CDbl(DSum("[Live Units]","[FiFO_Live_Temp]","[Main TType]='P' And [Folio no] = '" & [FiFO_Live_Temp].[Folio no] & "' And [Product Code] = '" & [FiFO_Live_Temp].[Product Code] & "' And TDATE <=#" & Format([FiFO_Live_Temp].[TDATE],"m-d-yyyy") & "#")),3)
I would like the query to compute the total for the first row as 4732.608 and the second one as 6283.337.
This is a very easy problem to solve in Excel, but I've run up against a brick wall in Access.
Some help would be greatly appreciated.
Thank you for your time.
Sanjay