Running Total in Subform

[Scorpio11]

Evening



I have an issue that is making me go insane.

I am trying to get a running total working in a subform which is filtered for the information on the main form.

I have gotten it working in a query, mostly. which i have based the subform on. But in the subform, there are details i want to use as a filter and have the running total only calculate the running total for the information based on the filter.

I have attached my Access file for reference purposes.

The forms i am havings issued with is:
- Sec Total - The main form
- Sec Total (subform)

The query
- Qry: SecTrx-Trade

In the query i am trying to filter by the trades, but the running total seems to include values excluded from the filter - this is not much of an issue as i can create a second query to do the filter and another to do the subtotal - but if it is possible to do both in 1 query, it'll be appreciated.

I used this link to assist me with the running total: http://support.microsoft.com/kb/290136

But they filter by date and this does not help me as mine is text.

The form - The variable i want to use a the filter is the SecID. What I am seeing is the subform is pulling the calculated value from my query and not filtering the subtotal based on my SecID filter.

i have scoured i don't know how many forums trying to find a similar problem, yet the solutions i am seeing don't help me or use a method i don't understand.

Another method i tried, yet it is not in this database. Something tells me that this is the way to go, but my skills are clearly not up to it.

i tried to create a text box in the subform and use the dsum in a similar way to the query forumula, instead of the using the formula in the query - what let me down there is i get a #name error. i suspect a syntax error trying to associate the formula with the parent form, but i have no idea of why or how to fix it.

I want to use several running total formula throughout my database i am building.

Any help will be appreciated.

Thx.

[jzwp11]

If I understand what you are after, you have to restrict the DSum by SECID and the sectrxtype. So it should look like this

DSum("Amount","Trx_Listing-Detail","[TrxDetailID]<=" & [DetailID] & " AND SECID=" & [SECID] & " and sectrxtype='trade'")) AS [Running Total]

I've attached the revised DB

[Scorpio11]

I apologise if i was a little excessive in what i am looking for.

This seems to be what I was after.

Thank you.

[jzwp11]

You're welcome. Post back if you have any questions

[Scorpio11]

I do have another question:

I have added a few more columns and they are working fine.

One of the columns is an average cost column, being the average cost of securities purchased.

When a security is purchased, the average cost will change taking into account the cost and number of each security. When there is a sale, the average cost must remain the same so as to reflect the cost of the securities sold.

What i am looking for is: How do i tell the query to look at the Average cost result in the previous line, not the current one.

If this were excel, Line A would have the purchase and the average cost would be calculated.
Line B would have the sale and the the cell would look at the average cost in line A to calculate the cost of the sale.

I hope i am being clear. I know what I am wanting, i just am not sure i am describing it correctly.

[Scorpio11]

This is taking place in the same query as was fixed earlier.

[jzwp11]

So you want the average cost based on all securities sold that took place before the current transaction? You will have to use either a DAvg() function (much like the DSum()) or use a subquery. I'm not sure what you are averaging in terms of fields, so you'll have to provide more info.

[Scorpio11]

Hi

Essentially yes.

What I have done is taken a sample out of one of the spreadsheets i use. I am trying to create an access version of all the spreadsheets i use, excel is becoming too cumbersome to update so many sheets and get the same result.

I have attached the excel file in the zip doc - I am trying to replicate that in a query. The excel spreadsheet is suppose to represent what the form is to show me, but the formulae in the excel i think give an indication of what i am after. you can then see what my end result should look like.

I used your formula in the query and tried to modify it slightly to Dsum an expression i had built - Called - Cost. But the sum does not want to take it into account property. I hope the excel is able to give you an idea of what you are after. Am wondering if DSum does not like expressions.

I do appreciate all the help.

[jzwp11]

DSum() only sums a field in either the query or table referenced in the DSum(). Now that field can be an expression, so you do the expression first (in a query) and then use the DSum(). You can accomplish the same thing with subqueries (i.e. nested queried)

I was hesitant to use the detailID in the criteria section of the DSum() because the key field generally has no significance to the user. I think the date field would have been a better field to use.

I looked at the spreadsheet you provided, but I cannot correlate all of the fields of your tables with columns in your spreadsheet. Can you provide some insight as to some of the other fields that are in the database? Are you just recording shares and share price and later calculating the transaction value (in your spreadsheet)? In Access, you generally do not store a calculated value, only those basic values that are used in the calculation. You would calculate on the fly when you needed them.

What is the significance of the R in front of the values in your spreadsheet?

[Scorpio11]

I have uploaded the excel again with the query references in bold red. I have referenced the the spreadsheet to the query as far as i have, there are other query headers that have no bearing on the excel as they are for access only.

essentially yes i am recording purchases and sales of shares in this query. My thinking is this with the forumla i am trying - to calculate the cost of the shares, and then use that amount to process the next line in my transaction listing account.

i am building a kind of double entry system - i have worked out some of the kinks, but i find more along the way, most i can fix, but then i go to the forum to work out the rest.

I know i cannot build an automated entry to puts in the other side for me, so to facilitate the double entry i am essentially putting in the balancing entry myself - this is the reason why i am trying to calculate the cost of the disposal in the [Cost] expression, so i can then go to the next transaction and process the cost into the database. the [Cost] form is only for a calculation only , it is not to affect the rest of what i am building, which is why i was hoping to be able to have the calculation be automated. I am trying to input data quickly and cut down on some of the other time i spend on my excel.

A nested query - okay, that makes sense, i will look into that and experiment - thanks.

The "R" is a currency symbol, there is no other significance- i am not in the US, so i don't use the $ sign. but that is something i can format myself later at a different date.

[jzwp11]

Since the cost is dependent on the type (denoted with a P or S) in your spreadsheet, where is that in your table? SecTrxType? If so, how do the values P and S correspond to Trade, Null, Income - Dividend?

Nevermind, I see your description field

[jzwp11]

From your spreadsheet, if there is a sale, the cost is determined by the following equation: (avg cost of the previous sale) * (the # of shares sold). What happens if the # of shares being sold exceed the number of shares purchased in the previous sale? Do you have to use the avg cost from the purchase prior to the previous purchase? For example if the 2/29/2008 sale sold 119 shares rather than 25, would the cost be determined as follows:

(105*98.98) + (14*103.81)?

If so, you will probably need a custom function to determine the cost.

[Scorpio11]

What would happen if 119 were sold, the cost would be at the 98.98 still.

That is because the 98.98 is an average of cost of the previous 3 transactions all merged. The 103.81 is the average for only 2 transactions.

In accounting terms i am treating this on something called the weighted average method - The average cost per share, for all shares, is updated after each purchase. So If i sold all the shares at 28 Feb, the cost for all of them would be 98.98

The Average cost is based on the cumulative cost over the cumulative shares. - not an individual purchase, but all of them.

[jzwp11]

In order to do the cummulative cost and hence the average cost, you will need another query that is based on the query you already had.

Your first query: Qry: SecTrx-Trade

SELECT [Trx_Listing-Detail].TrxDetailID AS DetailID, [Trx_Listing-Detail].SecID, [Trx_Listing-Detail].trxDate, [Trx_Listing-Detail].Reference, [Trx_Listing-Detail].Description, [Trx_Listing-Detail].SecTrx, CCur(DSum("SecTrx","Trx_Listing-Detail","[TrxDate]<=#" & [trxDate] & "# AND SECID=" & [SECID] & " and sectrxtype='trade'")) AS [Cum SecTrx], [Trx_Listing-Detail].Amount, CCur(DSum("amount","Trx_Listing-Detail","[TrxDate]<=#" & [trxDate] & "# AND SECID=" & [SECID] & " and sectrxtype='trade'")) AS CumAmount, IIf([Trx_Listing-Detail].description="SALE",dsum("amount","[trx_Listing-Detail]","SECID=" & SECID & " and sectrxtype='trade' and trxDate<#" & trxDate & "#")/dsum("sectrx","[trx_listing-detail]","SECID=" & SECID & " and sectrxtype='trade' and trxDate<#" & trxDate & "#")*[Trx_listing-detail].sectrx,amount) AS Cost
FROM [Trx_Listing-Detail]
WHERE ((([Trx_Listing-Detail].SecTrxType)="Trade"))
ORDER BY [Trx_Listing-Detail].secID, [Trx_Listing-Detail].trxdate;


The new query that does includes the cummulative cost and avg cost (qrySecTrxTradeFinal in the attached DB):

SELECT [Qry: SecTrx-Trade].DetailID, [Qry: SecTrx-Trade].SecID, [Qry: SecTrx-Trade].trxDate, [Qry: SecTrx-Trade].Reference, [Qry: SecTrx-Trade].Description, [Qry: SecTrx-Trade].SecTrx, [Qry: SecTrx-Trade].[Cum SecTrx], [Qry: SecTrx-Trade].Amount, [Qry: SecTrx-Trade].CumAmount, [Qry: SecTrx-Trade].Cost, ccur(Dsum("cost","[qry: SecTrx-Trade]","secID=" & secid & " and trxdate<=#" & trxdate & "#")) AS CumCost, ccur(cumCost/[cum SecTrx]) AS avgcost
FROM [Qry: SecTrx-Trade];

In the attached database, I also included equivalent queries (qryBase and qryFinal) that illustrate the use of nested queries to accomplish the same thing.

[Scorpio11]

Wow, that is alot to look through to try to understand. Thank you.

I will look at it and let you know if i have any questions.

I do have a quick question though - in the formulae in the database - there a reference to Q1 and Q2, yet i cannot see a reference in the forum post - can you explain that one to me - i am not following it.