Calculation Not Working

[access2010]

Could we receive an idea why the form, "Investments_Purchases_SalesF_SubForm=Error" does not work and causes all other calculations on the form to show an error?

While the form, "Investments_Purchases_SalesF_SubForm=Works" has no errors.

Any suggestions, will be appreciated.

Thank you,
Nicole

 

[June7]

As noted in your other thread, this could be corruption that will never be explained. Suggest you keep the good one and get rid of the bad and move on. I had to do that a few times during the early stages of developing an app.

 

[access2010]

June7
Thank you for your note and this is what we have done, BUT, I was hopping that someone might find the way to fix our problem.

Nicole

 

[June7]

You may have already accomplished the most reasonable 'fix' possible - created a new form.

You can try a decompile procedure to see if that corrects the issue, however, will not explain what caused it, and sometimes even that will not fix. http://www.fmsinc.com/MicrosoftAccess/Performance/Decompile.asp

 

[Dreamweaver]

This happened to me a few times years back when Programs I worked on errored and I started debuging making changes to the code then going back to the form without saving after the save warning popup, would currupt that form I backup all my projects on a daily basis and if I've spend a lot of time on a project I'll back it up. Haven't had it happen since I started using office 365 but still backup.

 

[access2010]

MickJav
Thank you for your suggestion.
If we plan to make any changes to our database, we copy the original to a folder with to-days date and compile and make a backup to the same folder as the day's changes continue.

Paul

 

[MajP]

You cannot calculate a calculated control. You have to go to the source
Should read
=Sum(Nz([TransactionQuantity])*Nz([TransactionPrice])+Nz([TransactionComm]))

 

[access2010]

June7
Thank you for your link to use the Decompile function.
The data base with the problem went from 27,696 KB to 23,044 KB

We have tried recreating the form MANY times but an error always occurs when we try to add the contents of the field "TransactionTotal".

Could we save the data to a NEW field (TransactionTotal) in the Table "Investments_Purchases_SalesT" and than be able to Add this NEW Column to find the TransactionTotal (DR-CR).

If we have the TransactionTotal we could than find the Average Cost of each Share?

If yes, how could we place the calculation into the new field (TransactionTotal) in the table?

Thank you,
Paul

 

[MajP]

See post 7
If you replace
=sum([TransactionTotal])
with
=Sum(Nz([TransactionQuantity])*Nz([TransactionPrice])+Nz([TransactionComm]))

it works all values are shown.

 

[MajP]

See detailed explanation. My solution uses method 1.
http://www.jegsworks.com/lessons/databases/formsreports/step-totals-calculated.htm

 

[access2010]

MajP
I can smell S.U.C.E.S.S

Could you please advise me of how to fix our average calculation and than our form will be complete?

Thank you,
Paul

 

[MajP]

=Sum([TransactionPrice])/Sum([TransactionQuantity])

 

[June7]

Now I do feel like idiot. I did make sure TransactionQuantity was a normal field in table but apparently overlooked TransactionTotal. However, I do find it bizarre that error in TransactionTotal causes the valid expressions to error as well.

Might want to correct textbox name Average_Tranaaction_Price to Average_Transaction_Price

Should not divide TransactionPrice by TransactionQuantity. TransactionPrice is already cost per unit.

=Sum([TransactionPrice])/Sum([TransactionQuantity]) = 0.43

=Avg([TransactionPrice]) = 15.31

What needs to be considered in calculating average? Should this include the commission?

 

[MajP]

Should not divide TransactionPrice by TransactionQuantity. TransactionPrice is already cost per unit

I feel like an idiot too, did not look at the data just the formula.

However this is probably also wrong unless you really want the mean average price paid.

=Avg([TransactionPrice]) = 15.31

That is an average of an average.

I believe the correct is

=Sum([TransactionPrice]*[TransactionQuantity])/([SharesOwned])

which is 18.99 per share.

Example
You buy 100,000 shares with a transaction price of 1 per share.
You buy 1 share with a transaction price of 100 dollars.
The average of the transaction prices paid is 101/2 or 50.5

But not the average cost of all your shares.
100,100/100,001 which is basically a little more tha 1

 

[June7]

Maybe you mean:

=Sum([TransactionPrice]*[TransactionQuantity])/Sum([TransactionQuantity]) = 18.99

or

=Sum([TransactionPrice]*Abs([TransactionQuantity]))/Sum(Abs([TransactionQuantity])) = 9.61

 

[MajP]

Another good catch. Was not even paying attention to the negatives. The latter looks correct.

 

[June7]

Right, OP needs to decide if they want the average share price regardless if bought or sold OR the average price of net quantity.

 

[access2010]

I wish to thank you all for the suggestions received, BUT we still have not received the answer our Accountant wants.
===
We would like to find the Average cost of each share purchased by dividing the "TransactionsBalance" by "SharesOwned" which would than give us the "AverageShareCost"
I think that we have tried all the suggested formulas without any success.

The "TransactionsBalance" is $5,383.81 divided by "SharesOwned" #250 = "AverageShareCost" $21.54

===
Your suggestions, will be appreciated on how to find the Average Share price.
Thank you.
Nicole

 

[June7]

Well, that is an explicitly stated algorithm and all we needed.

Simply reference those two textboxes:

=[Text107]/[Text108]

 

[access2010]

June7
Thank you for your suggestion and we have tried the following code without receiving the answer that we require.

=[TransactionPrice]/[SharesOwned]
=([TransactionPrice])/([SharesOwned])

Nicole