Running Total of Purchases by Date with multiple purchases on the same date (1 Viewer)

Sanjay_S

Registered User.
Local time
Today, 19:24
Joined
Nov 24, 2015
Messages
32
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
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)
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
 

Attachments

  • Cumulative Illustration.accdb
    412 KB · Views: 408

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:54
Joined
May 7, 2009
Messages
19,169
use sub-query instead:

SELECT *, (SELECT SUM(T1.[Live Units]) FROM [FIFO_LIVE_TEMP] AS T1 WHERE T1.[MAIN TTYPE]="P" AND TI.[FOLIO NO]=[FIFO_LIVE_TEMP].[FOLIO_NO] AND T1.[PRODUCT CODE]=[FIFO_LIVE_TEMP].[PRODUCT CODE] AND T1.TDATE<=[FIFO_LIVE_TEMP].TDATE) AS RunningTotal from [FIFO_Live_Temp]
 

Sanjay_S

Registered User.
Local time
Today, 19:24
Joined
Nov 24, 2015
Messages
32
Dear arnelgp,

use sub-query instead:

SELECT *, (SELECT SUM(T1.[Live Units]) FROM [FIFO_LIVE_TEMP] AS T1 WHERE T1.[MAIN TTYPE]="P" AND TI.[FOLIO NO]=[FIFO_LIVE_TEMP].[FOLIO_NO] AND T1.[PRODUCT CODE]=[FIFO_LIVE_TEMP].[PRODUCT CODE] AND T1.TDATE<=[FIFO_LIVE_TEMP].TDATE) AS RunningTotal from [FIFO_Live_Temp]

With this the overall query should be
Code:
SELECT FIFO_Live_Temp.[Folio No], FIFO_Live_Temp.[Product Code], FIFO_Live_Temp.Ttype, FIFO_Live_Temp.TDate, Avg(FIFO_Live_Temp.NAV) AS NAV, Sum(FIFO_Live_Temp.[Live Units]) AS Units, Sum(FIFO_Live_Temp.[Live Amount]) AS Amount, FIFO_Live_Temp.[Mod PAN], 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) AS Cum_PUnits, FIFO_Live_Temp.[Main Ttype], (SELECT Sum(T1.[Live Units]) FROM [FIFO_LIVE_TEMP] AS T1 WHERE T1.[MAIN TTYPE]="P" AND TI.[FOLIO NO]=[FIFO_LIVE_TEMP].[FOLIO_NO] AND T1.[PRODUCT CODE]=[FIFO_LIVE_TEMP].[PRODUCT CODE] AND T1.TDATE<=[FIFO_LIVE_TEMP].TDATE) AS RunningTotal
FROM FIFO_Live_Temp
GROUP BY FIFO_Live_Temp.[Folio No], FIFO_Live_Temp.[Product Code], FIFO_Live_Temp.Ttype, FIFO_Live_Temp.TDate, FIFO_Live_Temp.[Mod PAN], FIFO_Live_Temp.[Main Ttype], (SELECT Sum(T1.[Live Units]) FROM [FIFO_LIVE_TEMP] AS T1 WHERE T1.[MAIN TTYPE]="P" AND TI.[FOLIO NO]=[FIFO_LIVE_TEMP].[FOLIO_NO] AND T1.[PRODUCT CODE]=[FIFO_LIVE_TEMP].[PRODUCT CODE] AND T1.TDATE<=[FIFO_LIVE_TEMP].TDATE)
HAVING (((FIFO_Live_Temp.Ttype)="P" Or (FIFO_Live_Temp.Ttype)="SI") AND ((FIFO_Live_Temp.TDate)<=IIf(Weekday(DateSerial(Year(Date()),Month(Date()),0))=1,DateSerial(Year(Date()),Month(Date()),0)-2,IIf(Weekday(DateSerial(Year(Date()),Month(Date()),0))=7,DateSerial(Year(Date()),Month(Date()),0)-1,DateSerial(Year(Date()),Month(Date()),0)))) AND ((Sum(FIFO_Live_Temp.[Live Units]))>0));

1. This gives me a "Syntax Error" message
2. Would this give different totals for rows 1 and 2? From the Query, I thought it would do the same thing as my Dsum did

Thanks for your patience.

Sanjay
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:54
Joined
May 7, 2009
Messages
19,169
Somewhere in the subquery you have Ti., eeplaced it with T1.
 

Sanjay_S

Registered User.
Local time
Today, 19:24
Joined
Nov 24, 2015
Messages
32
Somewhere in the subquery you have Ti., eeplaced it with T1.

Dear Arnelgp,

It is working now, but the results are exactly the same as the Dsum. The running total is the same for the first two rows- 6283.337, whereas it should be 4732.608 for the first row and 6283.337 for the second.

Sanjay
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:54
Joined
May 7, 2009
Messages
19,169
export your table in excel and test it there if you will have different result.
 

Sanjay_S

Registered User.
Local time
Today, 19:24
Joined
Nov 24, 2015
Messages
32
export your table in excel and test it there if you will have different result.

In Excel, the formulae are different, and as I had said in my first post, it is easy to achieve the desired outcome there.

I have found a work-around for MS Access, but it is a two step process. I first need to introduce an autonumber field (I've called this SCount) in the FiFO_Live_Temp table, and then use the following formula for the running total:
Code:
Round(CDbl(DSum("[Live Units]","[FiFO_Live_Temp]","[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") & "# AND SCount<=" & [SCount] & "")),3)

It seems to take care of the discrepancies. Even if there are two transactions on the same date, the SCount for each would be different, and therefore they would be separately added to the running count, instead of jointly, as was happening with the previous formula.

However, this means an intermediate process of introducing an additional field in the table.

If there is any other way of solving this, I'd be grateful for some advice.

Thanks,

Sanjay
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:54
Joined
Feb 19, 2002
Messages
42,970
To make a running sum in a query, you MUST have a unique identifier and you must use that to control the summing. Using the date will only work when the date is unique.
 

Sanjay_S

Registered User.
Local time
Today, 19:24
Joined
Nov 24, 2015
Messages
32
To make a running sum in a query, you MUST have a unique identifier and you must use that to control the summing. Using the date will only work when the date is unique.

Thanks, Pat. The date is not unique in that you can have two transactions (a purchase and a switch) on the same date, but the combination of [Ttype] and [TDate] is unique.

The problem of course is in setting the criteria for the running total, as this is not a number or a date.

One experiment I tried was putting a modified transaction date
Code:
[ModTDate]=[TDate]-Iif([Ttype]="P",2,1)
. This would set the date back two days for a purchase, and one day for a switch and thus make the date unique.

I can then use the same DSum formula, substituting [ModTDate] for [TDate].

But this does look a little clumsy to me, and slows down the query tremendously.

So, any pointers on resolving this in an elegant manner would be welcome.

Thanks,

Sanjay
 

plog

Banishment Pending
Local time
Today, 08:54
Joined
May 11, 2011
Messages
11,611
..but the combination of [Ttype] and [TDate] is unique.

Then it is possible, but going to be a pain. A running sum is all about ordering data--you are putting the records in a specific order then for a specific record you are adding up all the records prior to it. Without a unique way to order your data, records share sums because they essentially share the same position in the order.

The specifics are up to you but here's how it works in general. You will need to reconfigure your DSum to work with 2 conditions:

DSUM(Field, Table, Condition1 OR Condition2)

Condition1 = "[DateField] <" & [DateField]
Condition2 = "[DateField] = " & [DateField] & " AND [Ttype]<=" & [TType]

Every date prior gets let in, but for the same date you must test the Ttype to see if it occurs or not. This means that Ttypes with lower values get added to the running sum before ones with higher Ttype values.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:54
Joined
Feb 19, 2002
Messages
42,970
If there is an autonumber and the data is inserted as it happens, the autonumber will work.

As you will see, it is far easier to do this with a single field than multiple fields. It might work if you concatenate the two fields but be careful. For a string date to work as you expect a date to work it MUST be in ymd order so use the Format() function

Format(YourDate, "yyyymmdd") & yourType
 

Mark_

Longboard on the internet
Local time
Today, 06:54
Joined
Sep 12, 2017
Messages
2,111
If you default TDate to NOW() instead of DATE(), you will get a time component added which should make the records unique.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:54
Joined
Feb 19, 2002
Messages
42,970
Including a time component solves this immediate problem but will cause others in queries and code unless the poster finds EVERY place where he wants to only check for date and make the necessary modifications to the query and code.

A better solution is to simply add an autonumber PK. Leave the existing PK as a unique index to implement the business rules needed to define unique data.
 

Sanjay_S

Registered User.
Local time
Today, 19:24
Joined
Nov 24, 2015
Messages
32
Thank you everyone for the inputs.

Pat, the autonumber didn't work for me- it did the job in providing an identifier, but for whatever reason, when I fed this query into another append query, I got an "Overflow" message.

The earlier workaround I had of creating a different date counter, taking away one day if it was a switch, and two days if it was a purchase again didn't work, as I then had a purchase done on day T+2 and a switch done on day T+1 both being identified by date "T"- and the problem began all over again.

So, I created a field RowID, and specified
Code:
RowID=[TDate]&Iif([Ttype]="P",1,2)

This ensured that the RowID sorted identical to [TDate], and more importantly, for a "P" and "SI" on the same [TDate], the RowIDs were different, and could be used to calculate a running total.

I finished processing a batch of data using this, and everything tallies.

Thanks once again.

Sanjay
 

Users who are viewing this thread

Top Bottom