Running Sum with Multiple Criteria (1 Viewer)

[Sanjay_S]

I have a transactions table which has transactions of various types done by different investors in various folios and products.

I am interested in the transactions with have the [Mod Ttype] field set to "DR".

I would like a date-wise listing of DR transactions for each [folio_no] and [prodcode], showing [purprice],[Mod Units] and [amount], along with a running sum of [Mod Units].

For each row, the query has to, for a given [folio_no] and [prodcode], sum up all [Mod Units] upto and including the [traddate] of that row.

The table and the query (using DSum) I wrote to extract this information from the table are in the attached database.

When I run this query I get gaps. The running sum is skipped in certain fields, and re-started with some mysterious additions.

Could anyone please look into this and tell me how to rectify this.

Thanks

 

[arnelgp]

try this my friend, use your old query.

 

[Sanjay_S]

arnelgp, thanks for the solution. However, have a couple of problems still:

1. The data table displays immediately on executing the query, but it takes a long time for the number of records to display. This is a truncated table of 2001 records; my main table has 60,000 plus records
2. The initial count of records on executing the query says 1837. We however have 1839 records with the [Mod Ttype]="DR" in the main table. When I filter the query results using folio number, the display changes to 1839

I remember having the first problem with another part of my dataset before, and one of the members had suggested an alternate method of indexing that solved it. I did try playing around with the indexes in the main table, but am not able to get the record count to display quickly.

Any further solutions/ advice? And any reason as to why the DSum did not work? That query ran almost immediately, though unfortunately it left gaps in the running sum!

 

[arnelgp]

both have their disadvantage.
using subquery (Query_Expt) is slow in retrieving the resultset.
once all record is retrieved you can use the down/up arrow with no delay.

using DSum (Copy of Query_Expt), it retrieved all the resultset instantly.
using down/up arrow is slow (since everytime it re-evaluates DSum).

its, up to you which one to adopt.

 

[Sanjay_S]

Hi Arnelgp, sorry for the delayed response. Your Dsum with the date being set to a format works like a charm, and may fit the bill perfectly.

As of now the running total problem has been solved. Will begin building the other parts of the query now.

Many thanks for the response.